Are you just starting your job search or would you like to improve your skills? The ability to work with data using a spreadsheet software such as Excel is one of the most fundamental skillsets across industries and functions. Although the level of knowledge and expertise will vary depending on the role, most office positions will require that you have at least some basic understanding of spreadsheets. After all, business decisions are commonly made based on data, and your ability to analyze and interpret that data will enhance the quality of your decisions. Here, I will outline the basic skills that you’ll need and provide you a few resources to get started.
When I first started my consulting job not too long after college, I thought I knew Excel. After all, I could write formulas, create charts, and use filters. I used Excel in various classes and during various internships. Once I started working, I realized how little I knew. I must have spent 100s of hours learning new features. People were doing things with Excel that I didn't even realize was possible. Equally important was how quickly some people were able to fly around and manipulate spreadsheets.
Below, I've outlined both the basic and advanced skills you'll need to know if you want to become proficient with Excel.
Data Entry - The ability to enter data into cells
Basic Formatting - The ability to manipulate the appearance of the cells
Inserting/Deleting Rows/Columns/Cells - The ability to add or remove rows, columns, and cells.
Working with Multiple Worksheets - Understand how multiple worksheets make up a workbook and how they can link together
Basic Formulas - The ability to write basic mathematical formulas
Referencing Cells - The ability to reference cells in your formulas and an understanding of absolute vs relative references
Filtering - The ability to use the filter on a large dataset
Sorting - The ability to sort data alphabetically or numerically
Perhaps the easiest way to learn these basic functions is to purchase an introductory-level Excel course from Udemy. These courses contain close to 10 hours of video content for less than $25. If you are completely new to Excel, the videos can clearly show you how to perform the basic tasks.
Lookups - The ability to use VLOOKUPs and HLOOKUPs across and within worksheets
Pivot Tables - The ability to run a pivot table to analyze a dataset and slice and dice data in any way imaginable
Advanced Formulas - The ability to apply SUMIFS, COUNTIFS, AVERAGEIFS, COUNT, COUNTA and other advanced mathematical functions
Text Functions - The ability to manipulate and extract text
Charts - The ability to create a variety of charts depending on the purpose
Conditional Formatting - The ability to apply formatting based on a set of criteria
Arrays - The ability to write array formulas when necessary
If-Statements - The ability to make decisions in your formulas
Comparison Operators - An understanding of how to use comparison operators when making decisions
Shortcuts - A strong knowledge of shortcuts to speed up your tasks
Modeling - The ability to combine all the above tools to produce a functional, multi-sheet workbook that extracts value from the available data and any user input.
Professional Presentation - The ability to make your charts, graphs, and tables look clean, attractive, and organized.
I cover most of the intermediate and advanced functions in my book. I’ve also included a set of practice exercises for most of the chapters, categorized into levels of difficulty. I believe the best way to learn the advanced functions is to actually practice them on data. Not a lot of resources out there will provide you this type of practice. These are all skills that I have learned after hundreds of hours on the job analyzing and presenting data.
Professional Development and Personal Finance Blog