Charlie Nichols, Author at Office PowerUps

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…

25Mar 2013 by Charlie Nichols No Comments

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…

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…

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…

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.…

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…

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)…

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.