Basic Excel Cheat Sheet: Essential Formulas, Shortcuts, and Workflows
A basic Excel cheat sheet is a compact reference for spreadsheet tasks that lists common formulas, keyboard shortcuts, navigation tips, and simple workflows. It focuses on concrete spreadsheet concepts such as cells, ranges, workbooks, formulas, pivot tables, charts, and file export options. The following material highlights essential formulas and syntax, frequent navigation and formatting shortcuts, data entry and validation tips, chart creation steps, a primer on pivot tables and filters, and practical file management notes for entry-level work.
Core formulas and syntax
Formulas compute values across cells and ranges using functions and operators. Use the equals sign to start any formula, reference cells by letter and number (for example A1), and combine functions with arithmetic operators. The table below condenses the most commonly used formulas, showing purpose, syntax, and a short example that fits typical administrative tasks.
| Purpose | Formula / Syntax | Example |
|---|---|---|
| Sum values | =SUM(range) | =SUM(B2:B12) calculates total sales |
| Average of a range | =AVERAGE(range) | =AVERAGE(C2:C12) computes mean unit cost |
| Conditional count | =COUNTIF(range, criteria) | =COUNTIF(D2:D100, “Complete”) |
| Conditional sum | =SUMIFS(sum_range, criteria_range1, criteria1, …) | =SUMIFS(E2:E100, A2:A100, “East”, B2:B100, “>100”) |
| Simple conditional | =IF(condition, value_if_true, value_if_false) | =IF(F2>0, “Yes”, “No”) |
| Lookup by key (classic) | =VLOOKUP(key, table, col_index, [range_lookup]) | =VLOOKUP(G2, H2:J100, 3, FALSE) for exact match |
| Flexible lookup (preferred where available) | =INDEX(range, MATCH(key, lookup_range, 0)) | =INDEX(J2:J100, MATCH(G2, H2:H100, 0)) |
| Combine text | =CONCATENATE(text1, text2) or =text1 & ” ” & text2 | =A2 & ” ” & B2 creates full name |
Navigation and formatting shortcuts
Keyboard shortcuts speed repetitive tasks and reduce mouse switching. Learn a small set that covers most activity: move between worksheets with Ctrl+Page Up / Page Down, jump to the last used cell in a region with Ctrl+Arrow, and select a contiguous range with Shift+Arrow. For formatting, use Ctrl+B for bold, Ctrl+1 to open format cells, and Ctrl+Shift+L to toggle filters. On macOS substitute Command for Ctrl. These shortcuts are compact tools for entry-level efficiency and are commonly supported in desktop editions.
Data entry, validation, and cleanup
Structured data entry reduces errors and speeds downstream analysis. Apply data validation to limit acceptable values (Data > Data Validation), use drop-down lists for repeated choices, and enable required formats with custom rules. For quick cleanup, Flash Fill can extract or combine pieces of text when Excel detects a pattern, Text to Columns splits a column by delimiter, and Remove Duplicates finds repeated rows. Consistent column headers and a basic naming convention for workbooks help when importing or merging data later.
Simple chart creation and styling
Charts convert rows and columns into visual summaries suitable for reports and presentations. To create a chart, select the data range including labels, choose Insert > Chart and pick an appropriate type (column for comparisons, line for trends, pie for proportions). After inserting, check axis labels, add a clear title, and use a legend only if multiple series are present. Keep color and label choices accessible by ensuring sufficient contrast and avoiding excessive chart elements that obscure the message.
Pivot table basics and filters
Pivot tables summarize large tables without complex formulas. Place the cursor inside a structured table or range, choose Insert > PivotTable, and drag fields into Rows, Columns, Values, and Filters to reshape the data. Values typically default to Sum or Count; change aggregation to Average, Max, or custom calculations as needed. Use slicers or the Filter area to let users interactively narrow results. Pivot tables refresh when the source data changes; remember to refresh manually in some versions.
File management and export essentials
Workbook organization affects collaboration and downstream use. Save workbooks with descriptive filenames, keep a consistent folder structure, and use version-aware storage when possible. Exporting commonly involves Save As > CSV for plain-text table exchange or Export/Save as PDF for fixed-layout reports. Be aware that CSV strips formatting and multiple sheets, and PDF preserves layout but not interactivity. When sharing, consider access controls and whether recipients need the raw data or a static snapshot.
Considerations when features vary and accessibility
Feature availability and behavior vary across desktop, web, and mobile editions. Some functions and keyboard shortcuts differ or are absent in browser-based versions, and advanced functions like dynamic arrays or newer lookup functions may not be present in older releases. Printable one-page references should focus on universally available features to avoid confusion. Accessibility considerations include choosing high-contrast palettes for charts, using clear cell labels for screen readers, and avoiding merged cells where possible because they can interfere with navigation and data export. These trade-offs influence which shortcuts and functions to prioritize when building training materials or onboarding references.
Which Excel shortcuts improve productivity?
How to use Excel templates for onboarding?
What Excel training resources cover pivots?
A printable one-page reference is often the best starting point: a compact set of formulas, a handful of navigation shortcuts, and steps for common tasks like charting, pivoting, and exporting. For entry-level spreadsheet work, this balance supports fast problem solving and consistent handoffs. Next learning steps typically include practicing lookup patterns, exploring conditional aggregation with SUMIFS and COUNTIFS, and building simple pivot-based summaries to move from manual updates to reusable workflows.
