02Apr 2013 by Charlie Nichols 6 Comments
How to add a drop down list in Excel to your worksheet

How to add a drop down list in Excel to your worksheet

100 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] Two ways to add a drop down list in Excel Sometimes you have a sheet that you need other people to fill in. And, you have a column or range of cells that need to have values from a specific set of values. You don't want to have to deal with multiple variations of "yes", for example. You might wind up with "Yes", "1", "True", "Y", "T", and who knows what else. You need people to stick to the menu of choices to make your analytic life much easier. The solution: add a drop down list in Excel. You've seen drop down pick lists in other people's spreadsheets and want to do the same in yours. The process is pretty straightforward. I'll describe a couple of common ways below. The…
Read More
29Mar 2013 by Charlie Nichols 2 Comments

How to refer to a cell in Excel and have that reference remain unchanged

100 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] Do you remember playing Battleship (the board game). You called out the coordinates for your shot using letters for the row labels, and numbers for the column labels. Excel is laid out the same way (just reversed). When you reference a cell you use its column and row coordinates. For example, A1 represents the cell in the first column ("A") and the first row ("1"). When you reference that cell such as below you are using a relative reference. =A1 This is called relative because if you copy that formula to another cell, the reference will adjust to be relative to the original reference. For example, if you copy the formula above to the cell immediately below the formula will be changed to the one below automatically. You…
Read More
25Mar 2013 by Charlie Nichols No Comments
Keep your row and column headings visible as you scroll in Excel

Keep your row and column headings visible as you scroll in Excel

100 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] How to freeze row and column headings in Excel It's pretty easy to get a large number of rows or columns in one of your spreadsheets. And often you wind up having to scroll up and down or side to side in order to keep track of which column or which row you are looking at. If you ever need to keep the row headings or the column headings "pinned" on the screen so that you can track your place easier you need to use the Split and Freeze Panes features in Excel. This will allow you to freeze row and column headings in Excel. PINNING THE COLUMN HEADINGS If you want to pin or lock in place your column headings Excel lets you do that with the…
Read More
20Mar 2013 by Charlie Nichols No Comments

Top 5 ways for you to get your extra tabs in Excel noticed

100 level, Excel
Have you ever shared an Excel workbook with information in additional tabs? And, later found out that over half the people didn't even notice there was any information in those tabs? I think that having an empty "Sheet2" and "Sheet3" tab by default has essentially trained us to ignore those tabs. It also doesn't help our cause by having those tabs at the bottom of the window, and they seem to nicely blend in amongst the noise down there, much like this toad. Here are five simple things you can try to make it more likely people will actually see and use the information in those extra tabs. After all, if you take the time to put information in them it would be good if people actually saw the information.…
Read More
19Jan 2013 by Charlie Nichols No Comments

How to enter multiple lines of text inside a single cell in Excel

100 level, Excel
Enter Multiple Lines of Text in One Cell [caption id="attachment_84" align="alignnone" width="300"] Entering multiple lines of text in one cell[/caption] Have you ever needed to enter multiple lines of text within a single cell in Excel? But when you press ENTER you wind up in the next cell - which is not what you wanted. In order to get the multiple lines of text in one cell, you need to press the ALT key along with the ENTER key. This ALT+ENTER key combination lets you put multiple lines within a single cell. If you need multiple lines, just repeat the key combination. It can make your data contained within a cell much easier to read. There you go.
Read More