Microsoft Excel Analytics

Excel treats every cell as one of a handful of data types, and almost every strange calculation error traces back to a cell holding the wrong type.

Data Types & Visual Alignment

• Numbers align right by default; text aligns left. That single visual cue tells you instantly if a numeric string is secretly stored as text.
• The Number Format dropdown on the Home tab (General, Number, Currency, Date, Percentage) controls how a value displays without altering its underlying value.
• Ctrl + Arrow Key jumps directly to the edge of a data region — the fastest way to navigate large worksheets.
• Ctrl + Shift + Arrow Key extends your selection to the edge of the data block in that direction.

Every formula begins with an equals sign (=). Mastering these fundamental functions handles the vast majority of day-to-day spreadsheet tasks.

Essential Functions

• =SUM(A2:A10) — Calculates the total of a numeric range.
• =AVERAGE(A2:A10) — Returns the arithmetic mean of a range.
• =COUNT(A2:A10) — Counts how many cells in a range contain numbers.
• =IF(A2>100, "Over budget", "OK") — Evaluates a condition and returns specific values for TRUE or FALSE outcomes.

Relative vs. Absolute Cell References

Copying a formula like =A2*B2 down a column shifts both references relatively. To lock a reference so it remains fixed, add dollar signs: $B$1 locks both column and row. Use this when referencing a single static input, such as a tax rate cell.

Converting a simple range of data with headers into an official Excel Table activates automatic database-like functionality.

Creating & Managing Tables

1. Select any cell within your data range and press Ctrl + T.
2. Ensure "My table has headers" is checked, then confirm.
3. Excel automatically applies filter controls, zebra striping, and visual formatting.

Key Advantages

• New rows typed adjacent to the table are automatically integrated, expanding formulas and formatting.
• Header dropdown arrows allow instant sorting (A–Z / Z–A) and granular filtering.
• Formulas use structured references (e.g., [@Price]*[@Qty]), which remain clear and automatically adapt as data grows.

Lookup functions search for a specific value in a dataset and return corresponding information from another column in the same row.

Lookup Syntax Breakdown

• =XLOOKUP(lookup_value, lookup_array, return_array) — The modern standard. Searches in any direction, defaults to exact matches, and handles missing values cleanly.
• =VLOOKUP(lookup_value, table_array, col_index_num, FALSE) — The legacy method. Only searches left-to-right. Always set the final parameter to FALSE for exact matching.
• =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) — Flexible dynamic lookup combination that works across older Excel versions without breaking when columns shift.

Pivot Tables allow you to summarize thousands of rows of data—calculating totals, averages, and counts—without writing complex formulas.

Building a Pivot Table

1. Select your source table and navigate to Insert → PivotTable.
2. Drag a categorical field (e.g., Region) into the Rows area, and a numeric field (e.g., Sales) into the Values area.
3. Customize calculations via Value Field Settings to toggle between Sum, Count, or Average.

Slicers & Visual Dashboards

Use Insert → Slicer to add visual, clickable buttons for instant data filtering. Combine with PivotCharts to build dynamic, interactive analytics dashboards.