Top 5 Excel Skills for Entry-Level Staff

By Brigid D’Souza, CPA, Saint Peter’s University – July 2, 2021
Top 5 Excel Skills for Entry-Level Staff

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 entry-level 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 yourlookup 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

Brigid D’Souza, CPA, MBA, is an 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.

This article appeared in the Summer 2021 issue of New Jersey CPA magazine. Read the full issue.

 

 

Related events

October 25, 2021Live Webcast
October 29, 2021Live Webcast
November 3, 2021Live Webcast
November 3, 2021Live Webcast
November 4, 2021Live Webcast
November 5, 2021Live Webcast
November 5, 2021Live Webcast
November 8, 2021Webcast Replay
November 9, 2021Live Webcast
November 9, 2021Live Webcast
November 9, 2021Live Webcast
November 10, 2021Live Webcast
November 11, 2021Live Webcast
November 11, 2021Live Webcast
November 12, 2021Live Webcast
November 12, 2021Live Webcast
November 15, 2021Live Webcast
November 15, 2021Live Webcast
November 17, 2021Live Webcast
November 17, 2021Live Webcast
November 19, 2021Live Webcast
November 19, 2021Live Webcast
November 29, 2021Live Webcast
November 30, 2021Live Webcast
December 1, 2021Webcast Replay
December 2, 2021Live Webcast
December 6, 2021Live Webcast
December 9, 2021Webcast Replay
December 10, 2021Live Webcast
December 13-14, 2021Live Webcast
December 15, 2021Webcast Replay
December 16, 2021Webcast Replay
December 17, 2021Live Webcast
December 21, 2021Live Webcast
December 21, 2021Live Webcast
December 22, 2021Live Webcast
January 6, 2022Live Webcast
January 13, 2022Live Webcast
January 20, 2022Live Webcast
January 21, 2022Live Webcast
January 26, 2022Webcast Replay
February 9, 2022Live Webcast
February 18, 2022Live Webcast
February 23, 2022Webcast Replay
March 16, 2022Live Webcast
April 20, 2022Live Webcast
April 29, 2022Live Webcast
May 4, 2022Webcast Replay
May 20, 2022Live Webcast
May 25, 2022Webcast Replay
June 22, 2022Live Webcast