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:
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.
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.
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:
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.
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!