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:

> Wrapping data within cells to ensure data is not cut off

> Shading and centering header cells

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

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:

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

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

> The SUM formula can report back totals for columnar data which you can then compare to control numbers in the enterprise-wide (original) reports.

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

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

5. ANALYZE AND VISUALIZE YOUR DATA

Once you have a clean dataset and you’ve run diagnostics to ensure your changes have not resulted in inaccuracies or omissions, you’re ready to analyze, visualize and report back to your team. Entry-level staff should learn the VLOOKUP and PIVOT table concepts. While these are advanced skills in Excel, every entry-level employee can learn them.

> The VLOOKUP formula is a search feature — like the Amazon or Netflix search box — that you can run on your dataset. Let’s say you have a list of unique customers and you want to easily pull up profile data on that customer based on the customer name. VLOOKUP allows you to use the customer name as a search term, the full customer dataset can be your lookup range and VLOOKUP will report back the profile fields associated with that customer.

> PIVOT TABLES are customizable reports within Excel. Pivot tables require clean data, which is why the preceding skills are so important. After that, it’s a matter of using Excel’s menu options to create the table.

If you’re already in an entry-level staff position or about to begin one, take the time to learn these skills as they will make you more efficient over time.

Brigid D’Souza, CPA, MBA, is assistant professor at the Frank J. Guarini School of Business in the Department of Accountancy & Business Law at Saint Peter’s University. She is a member of the NJCPA and can be reached at bdsouza@saintpeters.edu.