Why Excel still matters
Every few years someone announces that Excel is dead — replaced by Python, by Power BI, by some newer tool. And every time, Excel proves them wrong. It remains the most widely used analytical tool in the world, present in every industry and every function, from finance to operations to marketing to data analysis. Understanding Excel well is not optional for anyone who works with data.
This guide covers Excel from the ground up — not as a reference manual, but as a practical walkthrough of the things that make the biggest difference in day-to-day work. Every section is drawn from real training materials used across professional teams. Start at the beginning or jump to the section you need.
The Excel interface in plain English
An Excel file is a workbook. A workbook contains one or more worksheets — the grid of rows and columns you work in. Each intersection of a row and a column is a cell, identified by its column letter and row number: A1 is the first cell, B3 is column B row 3.
The ribbon at the top organises commands into tabs: Home (formatting), Insert (charts, tables, pivot tables), Data (sorting, filtering, validation), Formulas (functions), and View (freeze panes, zoom). The formula bar above the grid shows the content of the selected cell — either the value you have typed or the formula that produces the displayed value.
Formatting cells — telling Excel what your data means
Excel stores everything as either a number, text, or a formula. Formatting tells Excel how to display that value — as a date, a currency, a percentage, a number with decimal places, or plain text. Getting the format right prevents a significant category of errors.
To format cells, select them and press Ctrl+1 to open the Format Cells dialog. The Number tab controls how values display. Date formatting is particularly important: if you type 01/01/2024 and the cell is formatted as General, Excel may interpret it as a date (which it stores as the number 45292 internally) or as text, depending on your regional settings. Explicitly formatting date columns as Date prevents ambiguity.
Key formatting shortcuts: Ctrl+B (bold), Ctrl+I (italic), Ctrl+U (underline), Ctrl+Shift+1 (number with 2 decimal places), Ctrl+Shift+4 (currency), Ctrl+Shift+5 (percentage).
Conditional formatting — making data visible
Conditional formatting automatically changes the appearance of cells that meet a condition — highlighting negatives in red, marking duplicates, showing data bars that visualise values proportionally, or applying colour scales that shift from green to red as values change.
To apply: select the cells, go to Home → Conditional Formatting → choose your rule type. The two most useful types are cell value rules (format cells where the value is greater than X, less than Y, between two values, or equal to a specific value) and formula-based rules (for more complex conditions involving other cells).
Conditional formatting updates automatically when data changes — which is what makes it more useful than manual formatting. A column of sales figures where the top performers are automatically green and underperformers automatically red communicates performance without anyone having to reformat the sheet each time data updates.
Basic calculations and formulas
Every formula in Excel starts with an equals sign. Without it, Excel treats the entry as text. =2+2 produces 4. =A1+B1 adds the values in cells A1 and B1. =A1*B1 multiplies them. =A1/B1 divides. =A1^2 squares A1.
Cell references are the foundation of useful formulas. A relative reference like A1 shifts when you copy the formula to another cell — if you copy =A1+B1 from row 1 to row 2, it becomes =A2+B2. An absolute reference like $A$1 stays fixed regardless of where the formula is copied. Press F4 while editing a cell reference to cycle through absolute and relative combinations.
Essential functions every user needs
SUM(range) — adds all values in a range. The most used function in Excel. Shortcut: Alt+= to AutoSum the range above or to the left.
AVERAGE(range) — calculates the arithmetic mean. COUNT(range) counts cells containing numbers. COUNTA(range) counts non-empty cells. MAX(range) and MIN(range) return the highest and lowest values.
DATE functions: TODAY() returns the current date (updates daily). NOW() returns the current date and time. YEAR(date), MONTH(date), DAY(date) extract the relevant component. DATEDIF(start, end, "D") calculates the difference in days between two dates.
Text functions: CONCATENATE(text1, text2) or the & operator joins text strings. LEFT(text, n) extracts the leftmost n characters. RIGHT(text, n) extracts from the right. MID(text, start, n) extracts from the middle. TRIM(text) removes extra spaces. UPPER, LOWER, PROPER change case.
VLOOKUP — the formula that changes everything
VLOOKUP searches for a value in the leftmost column of a table and returns a corresponding value from a specified column in the same row. The syntax is =VLOOKUP(what to look for, where to look, which column to return, exact or approximate match).
-- Find "John" in column B of the table,
-- return the value from the 3rd column (Net Sales),
-- FALSE = exact match required
Always use FALSE as the fourth argument unless your lookup table is sorted and you specifically need approximate matching. The most common VLOOKUP error — #N/A — means the lookup value was not found in the first column of the table. Check for trailing spaces, different capitalisation, or mismatched data types (text vs number).
Pivot tables — summarise anything in seconds
A pivot table takes a flat data table and lets you summarise it interactively — without writing a single formula. You drag fields into rows, columns, values, and filters, and Excel aggregates the data accordingly.
To create one: click any cell in your data, go to Insert → PivotTable → select New Worksheet → OK. The PivotTable Fields panel appears on the right. Drag the field you want to group by (e.g. Salesperson) into Rows. Drag the metric you want to summarise (e.g. Net Sales) into Values. Excel creates a summary table instantly.
Pivot table tips: right-click any value cell and choose Summarise Values By to switch from Sum to Count, Average, Max, or Min. Use the Filters area to restrict the entire table to one value. Refresh the pivot table after the source data changes by right-clicking and selecting Refresh.
Data validation — build bulletproof spreadsheets
Data validation restricts what can be entered into a cell — numbers only, dates within a range, values from a dropdown list, or text of a specific length. It prevents the data quality errors that corrupt formulas and produce incorrect analysis.
To apply: select the cells, go to Data → Data Validation. In the Allow dropdown, choose your restriction type. For dropdown lists, choose List and either type the options separated by commas or reference a range of cells containing the valid values. Use the Input Message tab to add a helpful prompt when the cell is selected, and the Error Alert tab to show a custom message when invalid data is entered.
Charts and graphs — when numbers become pictures
A chart in Excel is created from a selected data range. Select the data you want to chart, go to Insert, and choose the chart type. Excel's chart recommendation (Insert → Recommended Charts) shows which types suit your data based on its structure.
After inserting a chart, use the Chart Design ribbon to change the chart type, switch row and column orientation, select a different data range, or apply a chart style. The Format ribbon controls individual element formatting — the title, axis labels, gridlines, and data series colours.
The most important chart selection rule: bar and column charts for comparisons between categories; line charts for trends over time; pie and donut charts for composition (proportion of a whole); scatter charts for relationships between two variables.
IF and logical functions
The IF function tests a condition and returns one value if true and another if false. =IF(A1>100,"Over budget","Within budget") displays "Over budget" when A1 exceeds 100, and "Within budget" otherwise.
Nested IFs handle multiple conditions: =IF(A1>=90,"Excellent",IF(A1>=70,"Good",IF(A1>=50,"Pass","Fail"))). This works but becomes unreadable beyond three levels. For complex multi-condition logic, use IFS (available in Excel 2016+) or a combination of IF with AND and OR functions.
AND(condition1, condition2) returns TRUE only if all conditions are true. OR(condition1, condition2) returns TRUE if any condition is true. =IF(AND(A1>50,B1="Active"),"Qualified","Not qualified") requires both conditions to be met.
Data entry tips that save hours
Ctrl+D copies the cell above into the selected cell or range. Ctrl+R copies the cell to the left. Ctrl+Enter fills all selected cells with the content of the active cell simultaneously. Alt+Down Arrow in a column shows all unique values previously entered in that column — useful for maintaining consistency in categorical data.
The Data Entry Form (add it to the Quick Access Toolbar from Excel Options) provides a pop-up form interface for adding records to a table — particularly useful when your table has many columns and scrolling right and left while entering data is disruptive.
The shortcuts that matter most
VBA macros — automate the repetitive work
VBA (Visual Basic for Applications) is Excel's built-in programming language. A macro is a recorded or written sequence of actions that can be replayed with a button click. Even a basic understanding of VBA unlocks significant productivity gains for repetitive tasks.
To record a macro: Developer tab → Record Macro → perform the actions → Stop Recording. Excel writes the VBA code for you. To view or edit it: Developer tab → Visual Basic → find your macro in the module list.
Four practical macros from real work — all explained in the dedicated VBA article in this series: merging multiple Excel files into one, splitting a sheet by column value into separate tabs, saving the active sheet as a PDF to your desktop, and toggling help annotations on and off with a button.