2.8 Excel reference

Look up an Excel function, feature, or keyboard shortcut while you work. For tutorials and examples, use the websites and YouTube channels.

2.8.1 Import/export data

• Insert data from picture (Figure 1)
• Import or export text (.txt or .csv) files
• Split (delimited) text into different columns
• Save charts as .SVG files
• Save tables as .PDF files (export selection)
• Print a worksheet or workbook

Figure 1: Inserting data from a picture.

2.8.2 Manipulate data

• Create and format tables
• Replace a formula with its result (paste values only)
• Use calculation operators (arithmetic, comparison, text concatenation, and reference)
• Switch between relative, absolute, and mixed cell references (freeze cells, columns, and rows)
• Use formulas and constants
• Use AutoFill and Flash Fill
• Change the column width or row height
• Use nested functions in a formula
• Create a histogram
• Use IF with AND, OR, and NOT functions (conditional formulas)
• Generate random numbers with RANDBETWEEN and RAND functions
• Look up values with XLOOKUP, XMATCH, and INDEX functions
• Use string functions such as TRIM, LEFT, RIGHT, FIND, SEARCH, CONCAT, REPT, TEXT, Ampersand symbol (&)
• Sum numbers using SUM, SUMIF, SUMIFS, and SUMPRODUCT
• Count numbers using COUNT, COUNTIF, and COUNTIFS
• Calculate absolute values (ABS) and square roots (SQRT)
• Retrieve the row (ROW) and column (COLUMN) numbers and offset (OFFSET) values
• Round a number (ROUND, ROUNDUP, ROUNDDOWN)
• Display the relationships between formulas and cells (Trace Precedents/Dependents)
• Evaluate a nested formula one step at a time
• Calculate multiple results by using a data table (What-If Analysis)
• Use Goal Seek to find the result you want by adjusting an input value

2.8.3 Analyze data

• Create a chart
• Create a PivotTable to analyze worksheet data
• Use the Field List to arrange fields in a PivotTable
• Highlight patterns and trends with conditional formatting
• Sort data in an Excel table using multiple levels
• Freeze panes to lock rows and columns
• Analyze data automatically

2.8.4 Visualize data

Relevant skills when you are using generated figures and tables on reports:

• Export pretty, professional tables (see guide (latex), guide (APA), examples (latex), and tutorial).
• Convey graphical results using the right chart for your data.
• Provide captions with enough context to understand tables and figures without going back to the main text (see guide).

2.8.5 Keyboard shortcuts

Experienced Excel users look up functions and features all the time, in the Microsoft help pages or with a web search. Learn the shortcuts for the actions you repeat in every workbook, such as copying, pasting, and filling down. Look up everything else when you need it.

TipSearch effectively

Because millions of people use Excel, you can usually find a solution to almost any Excel question through a web search. When searching:

• Include Excel 365 or Excel 2024 for modern solutions
• Include the function name if you're troubleshooting a formula
• Add without VBA if you want formula-only solutions

The tables below list Windows shortcuts for navigation and selection (Table 1), editing (Table 2), formatting (Table 3), formulas (Table 4), and files and help (Table 5). Microsoft's page Keyboard shortcuts in Excel lists many more, including the Mac versions.

Navigation & selection

Table 1: Shortcuts for navigation and selection.

Shortcut	Action
Ctrl+Home	Jump to cell A1 (or first unfrozen cell)
Ctrl+End	Jump to last used cell
Ctrl+Arrow key	Jump to edge of data region
Ctrl+Shift+Arrow key	Select to edge of data region
Ctrl+A	Select current region (press again for the entire worksheet)
Ctrl+Shift+L	Turn filters on or off
Ctrl+Page down or Ctrl+Page up	Switch between worksheets
F5 or Ctrl+G	Go To dialog

Editing

Table 2: Shortcuts for editing.

Shortcut	Action
F2	Edit active cell
Ctrl+C / Ctrl+V / Ctrl+X	Copy / Paste / Cut
Ctrl+Alt+V	Paste Special dialog
Ctrl+Z / Ctrl+Y	Undo / Redo
Ctrl+D	Fill down
Ctrl+R	Fill right
Ctrl+E	Flash Fill
Ctrl+T	Create an Excel Table
Ctrl+;	Insert current date
Ctrl+Shift+;	Insert current time

Formatting

Table 3: Shortcuts for formatting.

Shortcut	Action
Ctrl+1	Format Cells dialog
Ctrl+B / Ctrl+I / Ctrl+U	Bold / Italic / Underline
Ctrl+Shift+~	General format
Ctrl+Shift+$	Currency format
Ctrl+Shift+%	Percentage format

Formulas

Table 4: Shortcuts for formulas.

Shortcut	Action
F4	Toggle absolute/relative references ($)
F3	Paste Name (list of defined names)
F9	Recalculate all open workbooks (for example, new RAND values)
Alt+=	AutoSum
Ctrl+`	Show/hide formulas
Ctrl+Shift+Enter	Legacy array formula entry

Files & help

Table 5: Shortcuts for files and help.

Shortcut	Action
F1	Help
F12	Save As
Ctrl+N	New workbook
Ctrl+O	Open workbook
Ctrl+S	Save
Ctrl+P	Print
Ctrl+F / Ctrl+H	Find / Find and Replace

2.8.6 Websites and YouTube channels

Microsoft resources

• Microsoft Excel Help & Learning: Excel documentation
• Microsoft 365 YouTube Channel: tutorials for Excel and other Microsoft 365 apps
• Excel Templates: workbook templates

YouTube channels

• Leila Gharani: Excel techniques, including dynamic arrays
• ExcelIsFun: Excel tutorials and full courses
• Chandoo: Excel for data analysis
• MyOnlineTrainingHub: Power Query and modern Excel features

Practice exercises

• Excel Exercises: interactive practice problems

Reference websites

• ExcelJet: function reference and formula examples
• Contextures: PivotTables and data validation