Five Excel Skills That Matter

Two Kinds of Excel Users

Some readers are Excel professionals who get genuinely excited building elaborate models to impress bosses and clients. For them, the 500-plus formulas, hundred-plus functions and 200-plus shortcuts are part of the fun. Those nested-formula experts can skip this piece. Everyone else finds Excel a bit intimidating. They know the basics, a simple sum across a range, yet they mostly export results from SAP, Oracle, McKesson, Epic, Cerner or Salesforce and hesitate to touch the reports for fear of breaking something. This piece is for them, because the path from intimidated to conversant is shorter than it looks.

The Big Five

Five core skills make you conversant in the language of Excel, not fluent, but enough to get around: pivot tables, VLOOKUP, filter, sort and the dollar sign for absolute references. Interviewers who ask candidates to self-rate their Excel proficiency apply a simple standard. Never claim anything above three out of ten without knowing these five. The encouraging news is that most Excel work is straightforward, organizing rows of data and doing simple math. Across a typical month of analysis, these five skills cover roughly 80 percent of everything done. Resources such as ExcelJet teach each one well.1 The demonstrations below use the Johns Hopkins dataset of confirmed Covid-19 cases from March 2020, which remains publicly downloadable for practice.2

Pivot Tables

Pivot tables are the easiest way to look skilled. The tool groups your data and turns rows into columns and back again, which sounds like jargon until you watch it work. Consider a simple question: how many confirmed cases existed as of March 7, 2020? One route applies the SUM formula to the relevant column, arriving at 105,000. A more professional route selects the top data point and presses Control, Shift and the down arrow together, which reveals count, sum and average at the bottom of the screen. Then comes the pivot moment. Ask how many Australian cases came from each source and simple sums no longer suffice. Pivot tables bucket the data instantly. If they are new to you, invest two or three hours in practice. That single skill plausibly makes you 10,000 dollars per year more valuable to your employer.

VLOOKUP

VLOOKUP performs a different kind of magic. You hold two sheets of data, and the function looks up a value from one sheet and delivers it to the other. Suppose sheet one lists cities with populations, and sheet two needs those populations matched to its own city list. The formula tells the computer to find each city on sheet one and bring back the value sitting a set number of columns away. For a single city, skip the formula and use the find command. For 35,000 records, VLOOKUP saves the day and several hours besides. Video tutorials demonstrate the syntax in minutes, and the pattern generalizes to invoice matching, employee rosters and every reconciliation task consulting teams face.

Filter and Sort

Filter does exactly what the name promises. A column of 67,000 items narrows instantly to numbers greater than, less than or equal to a value, or to rows containing typed letters. Hunting for cities in Florida, typing the state abbreviation shrinks the list dramatically. Sort arranges the same data high to low or alphabetically, and it hides the most common Excel disaster. When the data contains a break, sort rearranges only the section before the gap, leaving everything after untouched. Careers have been bruised this way. Confirm your data is continuous, the fancy way of saying no gaps, before sorting anything that feeds a client deliverable.

The Dollar Sign

The absolute reference separates people who have built models from people who have watched models get built. Excel's copy-down feature repeats a formula across thousands of rows in a second, and the references shift down with each row. Usually that is exactly right. Sometimes it ruins everything. Computing each country's share of total Covid-19 cases means dividing China's count by the grand total, then Italy's count by the same total. Copying the formula down moves the denominator unless you anchor it. Placing a dollar sign before the column letter and row number locks the reference, so every row divides by the same 105,840 total. The F4 key adds the dollar signs automatically, one of dozens of shortcuts that compound into real speed.3

Why These Five Compound

The five skills reinforce each other in daily work, which is why learning them together beats learning them separately. A typical analysis starts with an export from an enterprise system, and filter plus sort turn the raw dump into ordered rows. VLOOKUP then joins the export to reference data, such as mapping cost centers to departments. A pivot table summarizes the joined data into the view a manager actually wants, and absolute references power the ratio columns that turn counts into insight. That chain, from messy export to client-ready summary, describes most spreadsheet work in consulting, finance and operations. Speed on the chain matters commercially. An analyst who completes it in twenty minutes instead of three hours gets invited to the meetings where the numbers get discussed, which is where careers actually advance.

From Conversant Toward Fluent

The five skills form a foundation, not a ceiling. Once pivot tables summarize your data, VLOOKUP joins your sheets, filter and sort organize the rows and absolute references keep formulas honest, the intimidation fades. Practice on a real dataset rather than reading about features, because muscle memory beats theory in spreadsheet work. From there, the path to text functions, conditional logic and charting opens naturally. Ask any Excel ninja about their favorites and the list grows quickly, yet every one of them started with these five.

• 1ExcelJet
• 2Johns Hopkins Covid-19 data repository
• 3Microsoft Excel help and learning