How To: Use Excel’s Named Cell Range Feature

Developing formulas can be tricky at times when you are dealing with Excel’s built in cell references (e.g. tabname!$J$5:$J$356). You may know where in the spreadsheet this information is and what it describes, but you always need to spend time going back and forth between different tabs to pull the reference in or copying it from another cell. That can take a lot of valuable time.

Wouldn’t it be great if you could just call data by its own name? Such as “PayrollHours” or “AR” for accounts receivable. Well you can. Formulas don’t need to look like this:

The first step is accessing Excels named range feature. And as with everything there are a couple of ways to do this. The easiest is to highlight the cell or range you want to name. Then look to the top left of the spreadsheet and you should see a little box with a cell reference in it. It is just to the left of your formula bar and looks like this:

cellrange2a

Click in the box (shown as V8 above) and rename that to anything you want. For example, I renamed this reference “Apple”. Now in the formula bar you can type “Apple” anywhere you would normally put a cell reference and Excel will know exactly what data to pull in there.

Say you are a financial auditor and want to conduct a ratio analysis. You can go through the balance sheets and various financial documents and rename all the important cells to the terms you are used to. For example, call the cell that contains current assets “Assets” and current liabilities “Liabilities”. Now in Excel you can type in the current ratio by just entering “=assets/liabilities”.

The other way of naming references is to use the name manager on the Formula tab of the ribbon.

cellrange3a

Click on new to create a new name. Enter the name and whether it applies to the whole workbook or just that single tab. You can also add a comment to document what it is you are defining.

cellrange4

On the bottom, you can adjust what individual cell or ranges you are referencing. Click the little box with the arrow to select them manually.

Now you have the tools available to change your formulas to look like this:

cellrange5
The formula is now much easier to understand.  It is summing (adding) all payroll hours. The formula breaks out the totals by location, classification, and pay code. To learn more check out this website.

Ian Green, CGAP and OAD Senior Auditor

Ian Green, CGAP and OAD Senior Auditor

Enjoy reading our ‘how to’ posts? Subscribe to the Oregon Audits blog through the ‘Follow’ link in the right hand sidebar and keep tabs on the latest data wonk tips and tricks, audit releases, interviews, and more!

Data Wonk Featured How To

How To: use Excel shortcuts to save you time and be more productive

Excel is a great tool for auditors, but oftentimes we do not utilize the shortcuts and other tools available to increase our productivity. This post will focus on some shortcuts I use to be more efficient.

My favorite and most used shortcut is auto filling formulas. Make sure you read the post on relative and absolute referencing or else you might make a critical error when auto-filling formulas.

Data Entry Tips

CTRL_D_1

CTRL+D, =SUM formula

To auto-fill a formula down, you can use the command CRTL + D. First, enter the formula you want to apply in the topmost cell of the column you are working in. Highlight the cell with the formula and all the other cells you want filled in. Now hit CTRL + D and the formula will now be applied to all of those cells. If you are working left to right in a spreadsheet, the same approach can be used with CTRL + R.

A helpful trick when working with big datasets is to use CTRL + Arrowkey to jump to the end of a row or column. To highlight those cells use CTRL + SHIFT + Arrowkey. If you keep holding SHIFT, but drop CTRL, you can modify the selection using arrow keys. For example, you can deselect the row or column label.

Excel has a handy shortcut if you work with dates and times. To enter today’s date simply hit CTRL + semicolon. For the current time hit CTRL + Shift + colon.

For a long time I could never get Excel to work nicely with adding another row of text to a cell. I would turn on text wrapping and add in spaces until the text I wanted dropped down a line. Quite an inefficient process. Save yourself time and use ALT + ENTER to put a return space.

ALT_ENTER

Switch cell formats to currency by hitting CTRL + SHIFT + $. To change it to text/General use CTRL + SHIFT + ~. For Percentages use CTRL + SHIFT + %. For numbers, CTRL + SHIFT + !. For Dates, CTRL + SHIFT + #. Want to switch to accounting format? Send Microsoft an email requesting that a shortcut feature be added, because right now there is no shortcut and you’ll need to use the manual process. The quickest way is to pull up a cell format dialog box using CTRL + 1.

To auto-sum fields you have entered use ALT + =. Select either a cell below a column or row needing summation.

Inserting rows and columns can be done by highlighting either an entire row or column and then hitting CTRL + SHIFT + =.

Editing Cell Formulas

If you select a cell and hit F2, Excel switches you to editing the cell formula. This is a great shortcut rather than having to go to the ribbon each time. One of the most important changes you can make is switching between absolute and relative references using F4.

F3 is handy if you use named ranges in complex formulas. This command will pull up the named range dialog box.

Reviewing worksheets

One of the best ways to review worksheets is to inspect the formulas used to calculate various fields. You can access this option using the ribbon, but a quick CTRL + ` (the one above tab not the ‘ next to the Enter key). Just enter the command again to turn formulas off.

awesomesheet

Use CTRL+TAB instead

You can switch between worksheets by using CTRL + TAB to go to the next worksheet or CTRL + SHIFT + TAB to go back a worksheet.

 Other useful shortcuts

We probably all use CTRL + V to paste, but did you know CTRL + ALT + V allows you to quickly paste special? Paste special is great for removing formatting copied from other documents or transposing data.

Turn auto-filter on and off with CTRL + SHIFT + L.

Auto-updating charts

You can tie charts directly to data ranges. If you compile an annual report, creating charts that auto-update can save you a lot of time. Read more about this process in Stephanie Evergreen’s most recent blog post.

Conclusion

By using one or more of these tips above you will become more efficient in conducting your audit work and before no time you will be the next Excel super user in your office.

Ian Green, CGAP and OAD Senior Auditor

Ian Green, CGAP and OAD Senior Auditor

 

Enjoy reading our ‘how to’ posts? Subscribe to the Oregon Audits blog through the ‘Follow’ link in the right hand sidebar and keep tabs on the latest data wonk tips and tricks, audit releases, interviews, and more!

Data Wonk Featured How To