Accounting
Excel Tips & Tricks for Accountants: Plus 0
We’re excited to have Jim Cline, founder of ExcelCEO, share a series of blogs about how accountants can better use Excel. Jim will be hosting a two-part free CPE webinar on May 22 and 24. Click here to register.
Welcome to the third and final post in my three part series, “Excel Tips & Tricks for Accountants.” If you missed it, you can read part one here (all about the Format Painter) and part two here (how to use Data Fill).
If you’re interested in learning more top Excel skills accountants need to know to be efficient on a daily basis, be sure to register for my upcoming two part webinar with FloQast, entitled, “Become EXCEL-lent – Excel Tips & Tricks for Accountants.” Until then, I hope you enjoy this tip that will help you use a formula to correct formatting problems rather than changing the format itself.
To follow along and try for yourself, download this Excel workbook, then go to the Office Ribbon and click on the Home tab.
Excel Tip No. 3 – Plus 0:
Often, when data is copied or linked to from one spreadsheet to another, the format from the first spreadsheet may not match the format we need in the second spreadsheet. Our natural inclination is to change the format, but I encourage you to leave the source data alone and instead write formulas to correct the format. If you use formulas, the source data can change and the formulas will still work.
On the Sales tab of our Excel Tips Workbook file, you can see that the values in the StoreNo field are formatted as text instead of numbers. Usually you can tell when numbers are formatted as text because either 1) the number is left justified in the cell and/or 2) the numbers won’t sum in a calculation. In the following exercise, we’ll look up the budget number for each store (contained on the Budget tab) and insert it on each row of the Sales tab.
- Select Cell H2 in the Sales tab and insert the following formula: =VLOOKUP(C2,budget,2,false)
This formula looks up the budget for Store No. 1063 in the range called budget contained in the Budget sheet. But it doesn’t work — It returns a #N/A error. That’s because the formula is looking up the store number from the Sales sheet, which is formatted as text, and comparing it to the store number in the Budget sheet, which is formatted as a number.
When this happens, most people will either change all of the store numbers in the Sales sheet to be a number, or change all of the store numbers in the Budget sheet to be text. But the fastest way to make it work is to change the formula.
- In cell H2, edit the formula. Type “+0” immediately after the C2 reference (so the formula now reads =VLOOKUP(C2+0,budget,2,false).
- Copy the updated formula to all the cells below using the double-click Data Fill trick.
Using +0 in the formula changes the number formatted as text into a number, and now our formula in the Budget column works perfectly.
This is just a small taste of the amazing things that our favorite spreadsheet program can do — for more, join my two-part, free CPE webinar, “Become EXCEL-lent – Excel Tips & Tricks for Accountants,” which I’ll be presenting live with FloQast on May 22 and 24. I’ll demonstrate LIVE my top Excel tips and tricks via a screenshare — you can even follow along in your own copy of the workbook if you choose.
If you learn these tips and teach them to others, you’ll be on your way to becoming the “Chief Excel Officer” of your company!
About ExcelCEO: ExcelCEO was developed by Jim Cline to train people who want to become Excel experts. The ExcelCEO Excel course (available for Excel 2016, 2013, 2010 and 2007) is self-study and is worth 40 hours of CPE credit (via NASBA’s QAS program). For more information about CPE credits, please see the ExcelCEO website at www.ExcelCEO.com. Feel free to browse the website and explore more about the ExcelCEO program. For more information about ExcelCEO, please email [email protected].
Related Blog Articles
