Whether you're just starting out with Excel or looking to sharpen your spreadsheet skills, this cheat sheet brings together some of the most practical tips you need for everyday use. It covers everything from quickly closing all your workbooks, doing instant calculations, and even using advanced search with regular expressions. This guide is designed to help you save time, avoid common pitfalls, and discover handy Excel features you might not know exist. And after mastering these skills, level up your Excel game even more by working smarter with custom templates. Let's make your Excel experience smoother and more enjoyable!
Closing all workbooks at once
The Close button in the top-right corner of Excel for Windows makes it easy to shut down workbooks — and Excel itself — whether you meant to or not. Close your last open workbook, and Excel waves goodbye — you must then launch the software again if you want to work on another spreadsheet. Purposeful, but repetitive methods of clearing the decks one workbook at a time include pressing Ctrl+F4 or Ctrl+W or choosing File→Close.
On macOS, things work a little differently. The Close button sits in the top-left corner of each workbook’s window and doesn’t boot you out of Excel entirely. You can also press Ctrl+W or choose File→Close. To fully exit Excel, choose Excel→Quit Excel or press Cmd+Q. Either way, closing multiple workbooks one by one is a tedious chore. Skip the hassle by adding Close All to your Quick Access Toolbar:
From the Choose Commands From list, select Commands Not in the Ribbon.
Scroll down and select Close All.
Click Add >> (Windows) or > (macOS).
Click OK (Windows) or Save (macOS).
Now, Close All sits conveniently on your Quick Access Toolbar, letting you clear the decks in one click — without the aggravation of relaunching Excel when you're ready to move on.
Windows users can access Close All with a keyboard shortcut — just tap Alt and type the shortcut that appears in the screen tip.
Rest assured, Excel prompts you to save any workbooks with unsaved changes before closing.
Creating a "Don’t Save All" button
Using Close All or exiting Excel triggers a save prompt for each open workbook — a tedious process when you just want to bail out. If multiple workbooks are open, the prompt includes a Save All button, but what if you don’t want to save anything?
Time for the nuclear option:
Click Close All or exit Excel:
Windows: Press Alt+F4.
macOS: Choose Excel→Quit Excel or press Cmd+Q.
Hold down Shift while clicking Don’t Save.
Boom! You’ve just created a virtual "Don’t Save All" button — no repeated clicking required. Who knew?
Performing math with Paste Special
Paste Special isn’t just for values and formats — it also lets you apply math operations directly to a selected range. Instead of writing formulas, you can copy a value and use Paste Special to apply the following operations:
Add: Increases each value by the copied amount
Subtract: Decreases each value by the copied amount
Multiply: Scales each value by the copied amount
Divide: Divides each value by the copied amount
To illustrate, let’s bump up a range of numbers by 5%:
Enter the baseline values and extend the series:
Type 100 into A1 of a blank worksheet.
Type 200 into A2.
Select A1:A2, then drag the Fill Handle down to A10.
To represent a 5% increase, type 1.05 into cell C1.
Copy the multiplier:
Select C1.
Choose Home→Copy or press Ctrl+C (Windows) or Cmd+C (macOS).
Apply the multiplication:
Select cells A1:A10.
Choose Home→Paste drop-down→Paste Special or press Ctrl+Alt+V (Windows) or Cmd+Ctrl+V (macOS).
The Paste Special dialog box opens.
Select Multiply, then click OK.
Presto! The values in A1:A10 increase by 5% — no formulas required!
Other ways to perform mathematical operations with Paste Special include
Converting negative numbers to positive: Multiplying by -1 to flip signs
Dropping extra zeros: Dividing by 1,000 to convert large values to thousands
Applying bulk adjustments: Quickly adding or subtracting fixed amounts across an entire dataset
Instead of cluttering your sheet with extra formulas, Paste Special math operations let you modify values directly and instantly.
Using regular expressions with XLOOKUP
Regular expressions (often abbreviated as regex) are powerful search patterns used to match and manipulate text. Think of them as highly advanced search functions that can look for complex strings of characters based on patterns, rather than just exact matches. Regular expressions allow you to find text that fits a certain pattern, like all words that start with a vowel (for example, “^[AEIOUaeiou]”) or any number that has exactly five digits (for example, “^\d{5}$”). These patterns help make searches more flexible and efficient, particularly when dealing with large datasets.
Chapter 6 dives deep into the XLOOKUP function and describes its arguments, but regular expressions are so new they missed the print cutoff for this book. To use a regular expression with XLOOKUP, provide a regex pattern in the lookup_value argument and set the match_type argument to 3. For example, suppose you have an Excel table named Data and want to return the total sales amount for the first vendor whose name contains the word Fruit (with a capital F). This formula will do the trick: =XLOOKUP("\bFruit\b",Data[Vendor],Data[Total Sales],,3).
Regular expressions are like walking along lazily in the surf until that next step plunges you in over your head without warning. They offer powerful capabilities but can be tricky to navigate at first. If you're feeling a bit lost, two Microsoft resources that you may find helpful are the Regular Expressions Quick Reference and an article on .NET regular expressions, which provide more details and examples to get you on track. On the other hand, if you're excited about using regular expressions in Excel, be sure to check out the REGEXTEST, REGEXEXTRACT, and REGEXREPLACE functions for hands-on practice.
Mapping missing features in macOS (and key Windows differences)
While Excel for macOS is powerful — it’s certainly no Excel for the Web — it still lacks numerous features and shortcuts that Windows users take for granted. I usually say macOS has 95% parity with the Windows version — but wow, that missing 5% hits hard.
This list isn’t exhaustive, but it highlights where macOS users might hit roadblocks and where Windows users have an edge. I’ve included workarounds where possible for macOS users, along with key Windows shortcuts and tips that may come in handy.
If you need these Windows-only features in macOS, consider running Excel for Windows via Parallels or Boot Camp to bridge the gap.
Navigating shortcuts and hidden clicks
Revealing shortcut screen tips: In Windows, pressing Alt reveals shortcut screen tips for both Excel’s ribbon and Quick Access Toolbar. Mac users? No dice — time to embrace the mouse like it’s 1984.
Displaying keyboard shortcuts: Hovering over a ribbon command displays a screen tip with its name on both Windows and macOS. Windows users also get keyboard shortcuts, but Mac users? Not so much. That said, keyboard shortcuts do appear in the traditional drop-down menus at the top of the Excel screen.
Using dialog box launchers: In Windows, tiny shortcut buttons in the corners of some ribbon sections offer quick access to related dialog boxes. Mac users? Keep clicking.
Double-clicking to confirm dialog options: In Windows, double-clicking an option within a dialog box often selects it and clicks the default button in one go. For example, double-clicking Fixed Width in the Text to Columns Wizard skips the Next button. Mac users? It’s a roll of the dice — sometimes you save a step, but more often you're stuck on the hamster wheel of endless button clicks.
Inserting bullets with a keystroke: Windows users can quickly insert a bullet (•) by holding down Alt and pressing 7 on the number pad — try numbers 1 through 9 to see what other happy surprises await. Mac users? To avoid shooting blanks, choose Edit→Emojis and Symbols or tap the Fn key, type Bullet (but sorry, Steve McQueen — not Bullitt) in the search field, and then select your car chase — er, symbol.
Using the Selection Task Pane: Windows users can choose Home→Find & Select→Selection Pane to display a task pane that allows them to view and select objects (like shapes, charts, and text boxes) within a worksheet. Mac users? You’re out of luck — no such task pane exists in Excel for macOS, leaving you to manually hunt down those objects.
Customizing Excel's interface
Creating workbook-specific toolbars: Windows users can create workbook-specific Quick Access Toolbar layouts (see Chapter 1 of Microsoft 365 Excel For Dummies). Mac users? Stuck with a one-size-fits-all setup that doesn’t leave their computer.
Setting default PivotTable preferences: Windows users can set PivotTable defaults via File →Options→Data→Edit Default Layout. Mac users must painstakingly apply preferences one PivotTable at a time.
Adding Custom Views to the Quick Access Toolbar: Windows users can add Custom Views to the Quick Access Toolbar (see Chapter 11 of Microsoft 365 Excel For Dummies). Mac users must go to View→Custom Views, select a view, and click Show every single time.
Making Excel read aloud: Windows users can add Speak Cells and Stop Speaking Cells to the Quick Access Toolbar. Mac users? Even though macOS lets you add these commands to the toolbar, they remain stubbornly unclickable — so don’t get your hopes up.
Streamlining access to recent workbooks
Favoriting frequently used workbooks: Windows users can hover over a file in File→Home or File→Open, and then click Add to Favorites (represented by a star or pushpin) to keep it easily accessible. Mac users? No favorites. The Open Recent menu constantly rotates, causing lesser-used files to eventually vanish — leaving you to hunt them down manually.
Right-clicking workbooks for extra options: Windows users can right-click a workbook in File→Home or File→Open to
Open it
Open the containing folder
Share the workbook with others
Open a copy while keeping the original intact
Delete it (choose wisely)
Copy its file path
Pin it to Recent (mark as a favorite)
Remove it from Recent
Remove all unpinned workbooks from Recent
Adjusting the number of recent files: Windows users can customize how many recent files Excel tracks by going to File→Options→Advanced and scrolling down to the Display section. Mac users? You’re at the mercy of Excel’s whims — whatever it decides to show, that’s what you get.
Copying, moving, and managing sheets
Copying worksheets within or between workbooks: Windows users can simply Ctrl+Drag to copy worksheet tabs effortlessly. Mac users? No such shortcut, but perhaps Edit→Sheet→Move or Copy will strike your fancy instead of Home→Format→Move or Copy Sheet?
Grouping rows or columns with a shortcut (Chapter 3 of Microsoft 365 Excel For Dummies): Windows users can press Shift+Alt+Right to group selected rows or columns instantly or Shift+Alt+Left to ungroup. Mac users? Your mission, should you choose to accept it, is to navigate to Data→Group or Data→Ungroup.
Using the clipboard and add-ins
Pasting values with a shortcut: Windows users can press Ctrl+Shift+V to paste values directly. Mac users? Take the scenic route: Home→Paste drop-down→Values — because some shortcuts just didn’t make the cut.
Managing multiple Clipboard items: Windows users can click the Clipboard shortcut on the Home tab to store up to 24 copied items — not just from Excel. Mac users? Limited to the most recently copied item — hope it’s the right one!
Enabling and disabling COM add-ins: Windows users can manage third-party COM add-ins via File→Options→Add-Ins. Mac users? No COM add-ins — though that’s not always a bad thing, considering how some PDF software programs hijack your ribbon like an overeager telemarketer.
Here’s a potential ray of sunshine for Mac users — you can choose Tools→Excel Add-Ins to enable the Analysis ToolPak and the Solver Add-In, as well as any other .XLA add-ins (legacy Excel add-ins that extend functionality with custom macros and features).
Modern Office add-ins, which work on both platforms, can be accessed via Home→Add-Ins.
Working with data and formulas
Redoing multiple actions: The Redo command in Windows sports a drop-down menu for bulk redo of up to 100 actions. Mac users? No menu for you — just blindly redoing one step at a time, like trudging uphill both ways in a blizzard, hoping for the best.
Toggling between Enter and Edit modes in reference fields: Windows users can press F2 to switch between Enter and Edit modes. Mac users can press Cmd+U in the Formula Bar — but in dialog box fields, one stray arrow key press and — oops! — you’ve got an unexpected cell reference crashing the party.
Extracting data from PDF files: Windows users can choose Data→Get Data→From File→From PDF to pull data directly from PDFs. Mac users? You’ll have to rely on third-party tools or manually copy-paste, unless you're into living on the edge.
The bonus chapter “Automating Data Transformation with Power Query” (available at dummies.com/go/excelfd) gives Windows users a running start with extracting data from PDF files.
Identifying stale values: Windows users can choose Formulas→Calculate→Format Stale Values to apply strikethrough formatting to outdated formulas. Mac users? The only hint is when Calculate appears in the Status Bar — where those stale formulas are lurking is anyone’s guess.
The Format Stale Values command is disabled in automatic calculation mode. To use it, switch to manual calculation mode.
Getting recommendations for PivotTables and Charts: The Insert→Recommended PivotTables and Insert→Recommended Charts commands (both covered in Chapter 12 of Microsoft 365 Excel For Dummies) in macOS are mere shadows of the robust functionality offered in Windows. It’s like asking for a second free sample at the grocery store — unlikely and not nearly as satisfying.
Resizing tables: Windows users can use the Resize Table command in the Table Design ribbon tab (see Chapter 5 of Microsoft 365 Excel For Dummies). Mac users must rely on the resizing handle instead.
Replacing formatting across a range: Windows users can use the Format button in the Find and Replace dialog box to find or replace formatting across multiple cells. Mac users? Get ready to do it one cell at a time.
Prognosticating with Forecast Sheets: In Excel for Windows, you can create a Forecast Sheet with just a few clicks. This feature automatically generates a forecast of your data, complete with a trendline and confidence intervals, all by analyzing historical data patterns. It’s like having your very own crystal ball for business predictions. Mac users? You’re left staring into the void because this feature is not available — no crystal ball here, just spreadsheets filled with numbers.
Saving interim changes in Conditional Formatting: Windows users can click Apply in the Conditional Formatting Rules Manager to save changes without closing the window. Mac users? It’s more like rinse and repeat — click OK to close the window and then reopen it for further edits.
Creating incompatible macros: The Macro Recorder (see Chapter 16 of Microsoft 365 Excel For Dummies) offers a helpful starting point for automating repetitive tasks. However, be cautious — macros that involve steps like opening workbooks or using features not available in Excel for macOS will result in errors. When you try running these macros, you're greeted with frustration because Excel for macOS can’t execute those actions, leaving you seeing red.
Importing worksheet ranges into Power Query: Windows users can choose Data→From Table/Range to send a range of cells directly to the Power Query Editor. Mac users? Well, you have to jump through a few more hoops — think of it like taking the scenic route just to get to the same destination.
The bonus chapter “Automating Data Transformation with Power Query” (available at dummies.com/go/excelfd) covers actions that both Windows and macOS users can automate, though some features may be more accessible or work differently depending on your platform.
Analyzing with Power Pivot: In Excel for Windows, Power Pivot is a powerful add-in that allows you to work with large datasets and create complex data models. It lets you build relationships between tables, create calculated columns and measures using DAX (Data Analysis Expressions), and easily analyze and summarize data across multiple sources. Mac users? Not so lucky — you’re left without Power Pivot, which means no advanced data modeling or complex calculations with ease. But hey, you can still make do with basic pivot tables.
Recovering workbooks and fixing issues
Reviewing protected sheets: Windows users can check File→Info for a list of protected sheets (see Chapter 14 of Microsoft 365 Excel For Dummies). Mac users? Get ready for a scavenger hunt — each worksheet must be checked individually to see if the Review tab displays Protect Sheet (unlocked) or Unprotect Sheet (locked).
Repairing corrupted workbooks: Windows users can go to File→Open and then click Browse. Within the Open dialog box, click the Open drop-down, and select Open and Repair (see Chapter 18 of Microsoft 365 Excel For Dummies). Mac users? No built-in repair option — better hope you’ve got a backup.
Creating automatic backup copies: Windows users can enable Always Create Backup (see Chapter 18 of Microsoft 365 Excel For Dummies) for locally saved workbooks. Mac users? No such option — so unless you're saving to the cloud, get comfortable with manual backups.
Forcing workbook recalculations before saving: Excel for Windows automatically recalculates workbooks set to manual calculation mode before saving — unless a user specifies otherwise. Mac users? Not so lucky. If a workbook is in manual calculation mode, it saves as-is, potentially preserving outdated or incorrect values. To ensure everything is up to date, press Cmd+= before saving.
Managing shared workbooks: Managing shared workbooks: Mac users can share workbooks, but miss out on the Manage Access option that Windows users enjoy. They also lack the Review→Unshare Workbook command, leaving them without an easy way to remove sharing once it's been enabled.
Removing excessive formatting: Windows users can access Review→Check Performance to detect and remove unnecessary formatting (see Chapter 18 of Microsoft 365 Excel For Dummies) — macOS users must manually clean up formatting or take a detour through Excel for the Web to get the job done.
Recovering unsaved workbooks: Windows users can retrieve up to five AutoRecover versions or recover unsaved files (you guessed it, see Chapter 18 of Microsoft 365 Excel For Dummies) — macOS users can only recover workbooks that were closed unceremoniously.
Mac users, if you’ve read this far without rage-quitting, congratulations — you deserve a medal.
Exploring macOS advantages
This next section is about as thin as the paper it's printed on, but there are a few things that macOS users can lord over their Windows counterparts:
Accessing commands from the menu bar: The drop-down menus at the top of Excel for macOS let you access commands without switching ribbon tabs. For example, instead of navigating to Home→Format→Format Cells, you can simply use Format→Format Cells from the menu bar. Small victories!
Avoiding the Scroll Lock Key: The Scroll Lock key in Windows can be a frustrating roadblock, especially when it gets accidentally activated. When turned on, it causes the arrow keys to scroll the entire worksheet instead of moving through the cells, which can feel like a very strange malfunction until you realize what's happened. Fortunately, macOS users don’t have to worry about this key at all — it simply doesn’t exist on their keyboards.
Using the Previous Button: Mac users have the advantage of a Previous button in the Find dialog box, which allows them to easily jump to the previous occurrence of a searched term within a worksheet. This small but handy feature saves time, especially when navigating large datasets, as it eliminates the need to start the search over from the beginning.
For both Mac and Windows users, clicking Find All might also help in locating all instances at once.
Stepping Through Macros: Stepping through a macro allows you to run a macro one line at a time, making it easier to debug or understand the flow of the code. Mac users can kick off this process by clicking Step in the Macros dialog box (see Chapter 16 of Microsoft 365 Excel For Dummies), and then pressing Shift+Cmd+I on each line of code. Windows users, however, need to click Edit within the Macros dialog box, and then press F8 for each line of code. It’s six of one, half a dozen of the other, but the Step button on Mac does reveal a hidden gem that many Excel users may not even know exists.
David H. Ringstrom, CPA, is president of Accounting Advisors, Inc., a spreadsheet and database consulting firm. He is a sought-after speaker on the subject of Microsoft Excel, QuickBooks, and QuickBooks Online. He is also the co-author of the previous edition of QuickBooks Online For Dummies and two previous accounting reference books.
