Skip to content

Excel Reference & CheatSheet

⌨ MS Excel Core Shortcuts & Operations

Speed up your daily data routine by swapping manual menu clicks for these essential keyboard shortcuts.

Feature / Action Shortcut / Method What It Does (The Beginner-Friendly Breakdown)
Flash Fill Ctrl + E The Pattern Reader: Automatically senses your pattern and fills the entire column instantly (e.g., pulling out first names from email addresses).
Remove Duplicates Alt + A + M The Data Cleaner: Instantly scans your dataset and deletes identical, accidental rows.
Text to Columns Alt + A + E The Column Splitter: Splits a single messy column into multiple sections using markers like spaces, commas, or semicolons.
Data Validation Alt + A + V + Enter The Rule Maker: Locks a cell down so users can only type specific data types or values.
Listing Inside Data Validation criteria The Dropdown Creator: Generates clean, clickable in-cell dropdown lists using a custom data range.
Duplicating Sheet Right-click tab ➔ Copy & move The Sheet Cloner: Safely creates an exact twin of your worksheet in a brand new workbook.
Select All Data Ctrl + A The Grid Grabber: Instantly highlights your entire active table with a single keystroke.
Format Painter Home ➔ Clipboard ➔ Brush The Style Cloner: Copies the fonts, colors, and borders of one cell and paints them onto another.
Freeze Rows/Columns View ➔ Freeze Panes The Header Anchor: Keeps your top titles or side labels permanently locked on screen while you scroll downwards.
Merge Cells Home ➔ Merge & Center The Label Builder: Melds separate cells together into a single large title block.

Data Interpretation Tools

Transform massive, unorganized lists into scannable views.

  • Filtering (Ctrl + Shift + L): Drops small interactive filter arrows onto your top header row so you can sort or hide records instantly.
  • Sorting (Alt + S + H): Organizes rows alphabetically (A–Z) or numerically (High–Low).
  • Advanced Filtering (Alt + A + Q): Runs deep searches to find unique records or copy filtered data straight to an isolated sheet space based on complex rules.
  • Pivot Table (Alt + N + V): Summarizes millions of rows of raw facts into clean, readable summary tables in just three seconds. > 💡 Pro-Tip: Always press Ctrl + T to turn your data into a formal Excel Table before launching your Pivot Table. This ensures any new row you add later automatically updates in your charts without creating messy blank spaces!
  • Grouping: Built right into Pivot Tables to pack granular information (like individual days) into broad, clean buckets (like Months or Quarters).
  • Slicer: Turns messy drop-down filters into clear, beautiful, clickable button arrays on your spreadsheet.
  • Timeline: A special, interactive sliding visual timeline designed specifically to slice data by custom date ranges.

Data Visualization

  • Conditional Formatting (Alt + H + M or Alt + H + L): Acts as your spreadsheet's automatic highlighter. It shifts cell background colors automatically based on the values typed inside.
  • Visual Enhancements: Dropdown highlights to look out for:
    • Data Bars: Fills cells with gradient mini-progress bars based on size.
    • Color Scales: Applies smooth multi-color heatmaps (e.g., Red for low margins, Green for high).
    • Icon Sets: Places neat flags, traffic lights, or arrows right next to numbers to instantly show up or down trends.

Functions & Formulas

Basic Syntax & Navigation

  • Start Every Formula: Always type = first.
  • Insert Function Menu: Press Alt + M + ZL to quickly pull up the search box for any formula.
  • Absolute Cell Referencing (Locking cells): Press F4 (or Fn + F4). This locks a specific cell down with dollar signs (like $A$1) so it won't slip or change coordinates when you drag the formula down.
  • The No-Mouse Super-Fill Trick: Move one arrow left to a filled column ➔ Hold Ctrl + Down Arrow to drop to the bottom ➔ Move one arrow right ➔ Hold Ctrl + Shift + Up Arrow to select the empty lane ➔ Press Ctrl + D to instantly duplicate your calculation all the way down!

1. Essential Statistical & Math Formulas

The core toolset for basic calculations: * SUM(...): Merges and adds all target values together. * AVERAGE(...): Pinpoints the exact mathematical mean. * COUNT(...): Counts how many cells contain numbers only. * COUNTA(...): Counts every cell that isn't empty (great for counting text lists, names, or codes). * MAX(...) / MIN(...): Instantly pulls out the highest peak or lowest baseline value in a column. * PRODUCT(...): Multiplies a cluster of numbers together.

2. Logical & Conditional Families

Teach Excel to make automated decisions based on rules.

  • The Core: IF (checks a single condition), AND (requires every condition to be true), OR (requires at least one condition to be true).
  • The Single-Rule (IF) Family: Combines counting and adding with a single rule. You can use standard wildcards like * to look for partial text matches.
    • SUMIF: Adds values together if they pass a specific test.
    • AVERAGEIF: Computes the mean of values that meet a criteria.
    • COUNTIF: Counts items only if they match your input rule.
  • The Multi-Rule (IFS) Family: The modern upgrade. Use these when you have multiple layered rules running at the same time:
    • SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS
  • Operators: Use > for greater than and < for less than.

3. Text Extraction & Manipulation

Perfect for breaking apart and cleaning messy, exported databases.

  • LEFT(...) / RIGHT(...) / MID(...): Snippets out a specific number of characters from the beginning, end, or middle of a text cell.
  • UPPER(...) / LOWER(...) / PROPER(...): Standardizes mismatched capitalization styles (e.g., turning "text string" into "Text String").
  • CONCAT(...) / TEXTJOIN(...): Merges text from separate blocks together into one string. TEXTJOIN is the advanced version because it lets you inject spaces or commas automatically while ignoring blank slots.
  • SUBSTITUTE(...) / REPLACE(...): Swaps old pieces of words or codes with brand new text inputs.
  • SWITCH(...): Tests a single value against a direct list of options and returns a matched result—a cleaner, modern replacement for writing multiple confusing nested IF statements.

Extracting Clean Formatting via TEXT Formulas: Need to extract readable parts from dates or values? Use these text masks: * TEXT(D5, "#") ➔ Pulls out raw, clean whole numbers without decimals. * TEXT(F23, "mmmm") ➔ Extracts the full text name of the month (e.g., "July"). * TEXT(F38, "hh:mm am/pm") ➔ Converts timestamp blocks into a clean, readable time. * TEXT(F74, "dddd") ➔ Reveals the exact day of the week (e.g., "Monday"). * TEXT(F74, "dd") ➔ Pulls out just the standard double-digit calendar day.

4. Date & Time Calculations

Keep project timelines, deliveries, and tracking schedules perfectly automated.

  • Live Tracking: Use TODAY() for the current calendar date and NOW() for the real-time running clock.
  • Time Windows: Break down components using DAY(), MONTH(), YEAR(), HOUR(), MINUTE(), and SECONDS().
  • Day Counters:
    • DAYS(): Computes the basic total number of calendar days between two milestones.
    • NETWORKDAYS() / NETWORKDAYS.INTL(): Calculates true corporate working days by automatically skipping weekends and omitting custom holiday lists.

5. Lookups & References

How to connect separate data tables together effortlessly.

  • XLOOKUP(...): The modern king of lookups. It looks down a column for an item and pulls back a matching detail from a different column. It works left, right, and won't break if you insert or delete columns later.
    • Partial Matching: Write it as "*" & cell & "*" as your target lookup value to find partial matches tucked away inside cells.
  • INDEX(...) + MATCH(...): The ultimate classic power-combo used to search across matrix tables diagonally or run multi-variable lookups.
  • VLOOKUP(...): The legacy vertical search engine tool.

6. Advanced Formula Structures & Ordering

  • Nested Formulas: Layering math actions inside each other, such as =PRODUCT(SUM(), SUM()).
  • Nested IFS: Stacking several rules together in a row so Excel works through a series of backup options until it finds a match.
  • Advanced Data Ordering: Deep statistical functions to sort distributions:
    • LARGE() / SMALL(): Extracts the exact Nth highest or Nth lowest value (like finding your 2nd best sales month).
    • RANK(): Assigns a competitive placement number to a cell value relative to the rest of the list.
    • PERCENTILE() / FREQUENCY(): Analyzes benchmarks and counts how often specific distributions show up across predefined range brackets.

Rounding Formulas Reference Guide

Use this handy index matrix to pick the exact rounding strategy your financials or metrics require:

Target Formula Direct Syntax Example What It Outputs
Standard Rounding =ROUND(C4, 0) Follows standard math rules to round to the nearest whole number.
Decimal Control =ROUND(C20, 1) Locks down the number to exactly 1 decimal place.
Round Up (Base 10) =ROUNDUP(E5, -1) Forces the number up to the nearest multiple of 10.
Round Down (Base 10) =ROUNDDOWN(E20, -1) Forces the value straight down to the nearest multiple of 10.
Nearest Multiplier =MROUND(E27, 5) Rounds out smoothly to the absolute closest factor of 5.
Highest Multiple =CEILING.MATH(E5, 5) Drives numbers strictly upward to hit the next clean tier of 5.
Lowest Multiple =FLOOR.MATH(E20, 25) Drives numbers strictly downward to the lower tier of 25.

Charts & Data Storytelling

Choosing the right chart means your audience can read your data instantly without needing a long explanation.

Comparison Charts (Column & Bar Visuals)

  • What They Track: Differences between separate categories.
  • Key Components: A clear Chart Title, labeled X-axis, Y-axis, Axis Titles, and clear Data Series identifiers.
  • Clustered 2D Column: Best for tracking one single variable across different categories.

Trend Charts (Time-Series)

  • Stacked 2D Column: Best used when tracking changing patterns across time when you have more than 2 distinct data series. This lets you see individual segment sizes and the total summary trend all at once.

Part-to-Whole Relationships

  • Pie Chart / Pie of a Pie: Great for quick, high-level snapshots of fractional data. Rule of thumb: Don't use a pie chart if your data has more than 5 slices!
  • 100% Stacked 2D Column: The ideal choice when showing how the internal makeup of a category shifts over time, especially when focusing on 2 to 4 distinct core elements.

7-Step Dashboard Building Blueprint

Follow this structured workflow to turn messy data tables into clean, automated corporate layouts.

Step Phase Action / Method Beginner-Friendly Explanation (The "Why")
1 Environment Create a blank sheet canvas Start fresh. Dedicate a brand new worksheet tab reserved solely for your front-end presentation layout, away from your messy data.
2 Objectives Focus on answering key metrics Design with the end-user in mind. Every chart or summary card you build must directly answer a critical business question or track a primary metric.
3 Grid Layout Build and align charts cleanly The Grid Lock: Hold down the Alt key while resizing or moving your charts. This forces their borders to snap perfectly onto the spreadsheet cell lines for an aligned look.
4 Declutter Hide gridlines and header columns Go to the View menu ribbon and uncheck Gridlines, Headings, and the Formula Bar. This instantly hides the default grid lines, making Excel look like custom software.
5 Highlight Call out core KPIs inside blocks Build high-level summary cards at the very top of your dashboard using large, bold fonts to display your most important key numbers instantly.
6 Interact Add Slicers for cross-filtering Insert Slicers and connect them to your data tables. This drops interactive, clickable buttons onto your screen so stakeholders can filter the dashboard on the fly.
7 Theming Apply cohesive, professional colors Ditch bright primary or neon colors. Stick to a clean, unified corporate palette—like desaturated blues, slate greys, or muted greens—paired with thin borders for a professional finish.
  1. Create an Environment: Start with a completely fresh, blank worksheet tab reserved only for your presentation layout.
  2. Create Business Answers: Design with real business questions in mind. Every visual item must directly solve a problem or answer a core metric.
  3. Create & Align Charts: Build your charts and hold down the Alt key while resizing them. This snaps their borders perfectly to the cell gridlines for an aligned look.
  4. Remove Distractions: Head to the View menu ribbon and uncheck Gridlines, Headings, and the Formula Bar. This hides the grid and makes Excel look like a custom software application.
  5. Bring Insights Out: Build highlighted KPI summary cards at the top using bold numbers to call out the most important figures instantly.
  6. Integrate Slicers: Connect visual Slicers to your underlying tables so users can click buttons to sort and filter your dashboard on the fly.
  7. Color & Theme: Drop bright neon colors. Stick to a clean, cohesive corporate palette—like slate greys, calm blues, or deep greens—paired with thin, light borders for a professional finish.

Excel Using AI: Modern Business Use Cases

How data professionals use intelligent prompts and smart functions to automate operations:

  • Text Analysis: Set up custom character string calculators to count total words packed into specific text fields or feedback logs.
  • Project Management: Design automatic rule triggers to flash custom Project Deadline Alerts before tasks run overdue.
  • Financial Health: Connect revenue inputs to run automated, real-time Profit Margin Calculations.
  • HR Analytics: Set up automated trackers to flag employee attendance trends, missing shifts, or layout schedules.
  • Revenue Management: Build interactive Dynamic Pricing Models that update pricing outputs on the fly when input assumptions change.
  • Marketing Operations: Set up automated logic rules to classify lists into targeted, distinct Customer Segments.
  • Logistics & Retail: Write threshold safety indicators to run automated, real-time Inventory Management reorder alerts.
  • Internal Auditing: Use conditional parameters to deploy Expense Report Validation flags that spot non-compliant line items instantly.
  • Predictive Analytics: Use historical variables to generate clean, macro-driven Financial Forecasting models.