22Mar 2013 by Charlie Nichols No Comments

Are you tired of looking at Excel formula errors?

200 level, Excel
Now that you're getting into using formulas in Excel you've found they can be quite useful. And by now you've found that sometimes they return error values. Sometimes, these can detect errors you need to fix. Other times, they just indicate that you don't yet have all the data populated in your worksheet. You wind up with a cell showing values such as #VALUE!, #DIV/0!, #N/A, etc. It just looks messed up and as if the whole sheet might be suspect. You can add another function to your cell that will "wrap" your existing function with a small layer of error protection. Meet IFERROR The IFERROR function lets you simply show one value if your function runs normally, and alternate value (that you define) if your function returns one of…
Read More
21Mar 2013 by Charlie Nichols No Comments

Do you need your VLOOKUP to look to the left instead of the right?

300 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] I've used VLOOKUP for many many years. It's a straightforward enough concept: scan down a column for a match in a cell, then look over to the right in that row for the value we want to retrieve. And, all of this is wrapped into a single function call: VLOOKUP. But you may have found VLOOKUP has a particular limitation. It can only look to the right of the cell you've matched. If you've been able to plan ahead and lay out your data so that the column you're scanning is to the left of the range of information you're looking for, you're in luck I suppose. But if you're like me and your data is provided to you from another source or you don't always envision every…
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
20Feb 2013 by Charlie Nichols No Comments

How to set up a running total column in Excel

200 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] Have you needed to set up a running total column in Excel? Perhaps you needed to maintain an account balance in the far right column of a worksheet. And then did you find yourself using one formula for the first row in the column, and then another formula to start adding the current row to the previous row? Well, as you know that method works. It's just a bit quirky looking -- and if you want to use that method in a table you aren't able to take advantage of the autofilling capabilities of the table. Here is a simpler way. It uses a mix of absolute and relative references on a range within the sum function. So, if you originally had a series of formulas that looked…
Read More
19Feb 2013 by Charlie Nichols No Comments

How you can “pin” the current date or time in Excel

200 level, Excel
Have you ever used the TODAY() or the NOW() function in Excel? They make it super easy to insert the current date and time into your worksheet. So long as you want the value to always be current those functions are great. If you open the worksheet tomorrow, the date or time information will reflect that moment in time. But, what if you wanted to put this moment in time into your worksheet -- and have that date or time be persistent. Or, what if you wanted to insert more of a timestamp than a dynamic date or time value? Here's a hot-key combination that makes that super easy too. Press CTRL+; (that's control and the semicolon) to insert the current date. Press CTRL+SHIFT+; (that's control, shift, and the semicolon)…
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