Excel: Create an Option List in a cell

Here's how to create an option set for a cell in Excel 2016.

So I have a form I'm creating and I want to limit the optional values in one column.  Here's a simple description of what I'm putting together.

The Column Name is "Status".  I want to make a limit the options for this cell instead of having it be filled in with any number of values.  If I can get consist data entry in this field I can then use it to filter the table or to pivot the data for further analysis.

Here are the options I want to make available:

• Potential Buy
• New Purchase
• In Rehab
• Listed
• Pending
• Sold

So, here's the step by step.

1. Create the options list on a separate page from your working doc

2. Select the cells that you want to use the option list

3. Go to the Data menu and click Validation

4. In the Validation Window select "List" from the Allow menu
5. Click the Source box and select the cells that show the options you want to use

1. Create the options list on a separate page from your working doc

2. Select the cells that you want to use the option list

3. Go to the Data menu and click Validation

4. In the Validation Window select "List" from the Allow menu

5. Click the Source box and select the cells that show the options you want to use

6.  The Option List should now be available for your cells

Excel: Change a cell format to add text after a Number

Microsoft: Create a Custom Number Format

From Microsoft:
Display both text and numbers    To display both text and numbers in a cell, enclose the text characters in double quotation marks (" ") or precede a single character with a backslash (\). Include the characters in the appropriate section of the format codes. For example, type the format $0.00" Surplus";$-0.00" Shortage" to display a positive amount as "$125.74 Surplus" and a negative amount as "$-125.74 Shortage." Note that there is one space character before both "Surplus" and "Shortage" in each code section.

So I selected format Category "Custom", then in the Type field I typed " minutes" right after the 0.00.  That did the trick!

Excel - Mortgage Principal Paid over a Period

Interested in determining how much principal is paid on a mortgage between two time periods?


CUMPRINC(Rate, nper, pv, start_period, end_period, type)

Example: How much principal will a borrower pay between their 10 and 15 payment on their mortgage?  Principal balance at the starting period is $51000 and interest rate in 9%.

Rate - it's the interest rate for the period, so for a mortgage with 9% you need to divide by 12 to get the monthly interest rate, .09/12

nper - total number of payments to be included, so the number of months between the start period and end period  (15-10), 5

pv - the principal at the starting period

Start_Period - Payment date doesn't work, you have to figure out the payment number, i.e. the 10th payment out of the term. 10

End_Period - Again, not a date, it has to be the number of the last payment, 15

Type - set it to 0 if the payment is made on the first of the month, 1 of the payment is on the end of the month

=CumPrinc( .09/12, 5, 51000, 10, 15)

Cumulative Principal Paid Between Periods

Excel - Countifs Using Equal To or Greater Than with a Date Field

There's a wrinkle in the countifs() formula for using a date field rather than an explicitly declared date in the formula.

Here's an example:
=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">=1/10/2013")

I wanted to replace the "1/10/2013" with a date field in A3.

My first attempt was this:
=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">=$A$3")
Which doesn't work.  My next try was this:

=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">="$A$3)
Again, that didn't work.


Now here's the correct way to use a date field in Countifs():

=COUNTIFS($P$15:$P$2050,"91+",$D$15:$D$2050,$A$1,$Q$15:$Q$2050,">="&$A$3)

See that little ampersand between the greater than and equal section and the field?  That's the trick.


You're welcome.

Excel - Grouping Dates by Month in a Pivot Table

For this post I am referencing the work creating by the talented minds at Chandoo.org, a great resource for advancing your knowledge of Excel.  Here's the direct link to their pivot table post.


I ran into a problem when creating a pivot table yesterday.  Here's my raw data, a series of daily bank payments that I wanted to group to show what months the account had made payments in.

Creating the pivot is pretty easy, but the result is that for each account the payments show up by day instead of summed up by month.

Here's the trick that the guys at Chandoo.org showed me.  Right click on a date cell; the pull-down menu will show a category called "Group...". Click Group and you can select month, year, etc.

The result is a nice pivot table with your numbers summed up by month. 

One caveat, it seems when you group by month it only looks at the month part of a date field, so if you have dates spanning multiple years they will get summed up to the specific month.  So you have to select group by month and year, this will do the trick!

"Get Lucky" using Mario Paint version

Just cuz this is really cool... Great story about it in Wired.

PDF - Exporting data from the pdf to a spreadsheet

I've received a bunch of pdf files and I want to get the data out of the PDF form and into a spreadsheet.  

Fortunately the vendor that created the pdf files is using a pdf form, so the fields are not just scanned text, they're actual data elements.

The Adobe Acrobat XI website has an excellent description of how to import and export data from pdf files, here's the link that I got my information on:

Source for how to move data from PDF
Here's the excerpt I used, you have to have Acrobat Pro to do this.

Merge exported data files to a spreadsheet

If you want to compile data from forms that are not already in a data set, use the following process.

1. Choose Tools > Forms > More Form Options > Manage Form Data > Merge Data Files Into Spreadsheet.
2. In the Export Data From Multiple Forms dialog box, clickAdd Files.
3. In the Select file Containing Form Data dialog box, select a file format option in File Of Type option (Acrobat Form Data Files or All Files). Then locate the form files that you want to merge into the spreadsheet, select them, and click Open.
4. Repeat the previous step to add form data files that are in other locations, as needed.
5. Click Export. Then select a folder and filename for the spreadsheet, and click Save.
6. In the Export Progress dialog box, click either View File Now to open the spreadsheet file or Close Dialog to return to Acrobat.

How to get the data out of pdf to a spreadsheet