PROFESSIONAL DEVELOPMENT
Top 5 Excel Skills for Entry-Level Staff BY BRIGID D’SOUZA, CPA, SAINT PETER’S UNIVERSITY
Entry-level staff will be well served by building their Excel toolkit early. The list below is largely gleaned from my experience working for a Big 4 tax firm in a quantitative consulting practice, but also from weaving Excel into my advanced accounting classes at Saint Peter’s University. 1. FORMAT YOUR DATA Formatting your data to be as easily readable as possible will create efficiencies down the line as work prepared by entrylevel staff is analyzed or manipulated by more senior staff. Some key tasks to keep in mind include the following: y Wrapping data within cells to ensure data is not cut off y Shading and centering header cells y Adding lines and borders to differentiate subtotals and totals from details Excel’s formatting menus have a similar interface as Word, making it one of the easier tasks to latch onto if you’re just starting out with Excel.
18
SUMMER 2021 | NEW JERSEY CPA
2. MASTER KEYBOARD SHORTCUTS Large spreadsheets can be intimidating at first glance, but figuring out how to quickly and efficiently move around in a spreadsheet will help build both your efficiencies and your confidence level. The “command” key on a Mac or “control” key in Windows will, when combined with arrow keys, allow you to jump up and down vertical ranges or left to right across horizontal ranges. Layering in the “shift” key allows you to highlight the cells as you jump, which can be helpful if you want to apply a singular change (such as bolding) to the entire range. 3. CLEAN YOUR DATA Examples of cleaning data include replacing blank cells with zeros, ensuring all values in a single column are formatted consistently or getting rid of unnecessary leading spaces in a text value. Excel can help automate a lot of this. The following are two examples: y The TRANSPOSE formula allows you to copy a range of cells that is laid out horizontally or vertically and then paste the range vertically or horizontally, respectively.
y The TRIM formula will remove any leading or trailing blank spaces before or after a value in a cell. 4. VALIDATE YOUR CHANGES After you’ve cleaned your data, it is a good practice to validate that your changes have not altered the raw (or original) dataset. Excel is chock full of formulas to help with this, including the following: y The SUM formula can report back totals for columnar data which you can then compare to control numbers in the enterprise-wide (original) reports. y The COUNTA formula can count the number of data points within a range which can be a helpful check to make sure no rows or data points were erroneously deleted while you were data cleaning. y The SUMIF and COUNTIF formulas will allow you to sum or count (respectively) a range based on a conditional value. For instance, “sum all revenues from Country X” or “count all instances of Country Y.”
