I'll admit: sometimes I get bogged down in Excel's power-user features, from Power Query and the Data Model to Python.But when I stop and think about the things that actually help me every day, it's often the little discoveries I picked up accidentally along the way.These are six Excel tricks I wish I'd stumbled upon earlier—and the best part is, each one takes seconds to learn.
Copy a worksheet without opening a dialog Drag, drop, duplicate You might duplicate an Excel worksheet for many reasons, from creating a backup before testing to adding another month to a personal budget workbook.For years, I did this by right-clicking the tab, clicking "Move or Copy," checking "Create a copy," then figuring out where I wanted the new sheet to go.But that's tedious, especially if there are dozens of sheets in my workbook.
Fortunately, I recently discovered there's a much quicker way.Hold Ctrl, then drag the worksheet tab to where you want the copy to appear.When you release the mouse button, Excel creates a duplicate of the worksheet in that position.
There's one thing to watch out for: make sure you're on the worksheet you want to copy first.If you try to copy an inactive sheet using the Ctrl-drag method, Excel selects the sheet tabs instead.Clear every filter before you blame your data A tiny button I overlooked Close Excel filters are great until you forget that you've applied one.
The trouble is that the filter icon in a column header is tiny, and when you're working with a wide table, it's easy to miss that one of your columns is still filtering the data.I've spent plenty of time wondering where some rows had gone before realizing I'd left a filter switched on.Excel has a button that removes the filter criteria while leaving the filter buttons in place.
Once you know where it is, it's a much quicker way to reset your view.Open the Data tab, head to the Sort & filter group, and click Clear.Just make sure you click inside the relevant table or range first.
If you prefer keeping your hands on your keyboard, selecting a cell in the dataset and pressing Ctrl+Shift+L twice turns the filters off and then back on again, which effectively resets them.Add to a selection without holding Ctrl A shortcut I've hated not knowing I've been holding Ctrl to select non-adjacent cells for years.It works, of course, but I've never particularly enjoyed doing it.
If you're selecting several ranges with a mouse, you have to keep Ctrl held down while clicking and dragging.But there's always the chance that you accidentally release Ctrl, losing the selection you've already built.Or if you want to scroll down using your mouse wheel, you can end up zooming out.
Excel has an Add to Selection mode that removes the need to hold Ctrl entirely.Select the first cell or range, then press Shift+F8 to activate the mode.Then, select another cell or range, and Excel adds it to the existing selection.
Press Esc when you've selected everything you need.Keep an eye on the status bar.Excel displays Add to Selection or Add or Remove Selection while the mode is active, so you know you can select additional ranges without holding Ctrl.
This is particularly useful when you're building formulas.If you want to include several separate ranges in a SUM, for example, you can press Shift+F8 and select each range in turn.Excel adds each selection to the formula, so you can build a multi-range formula without keeping Ctrl held down throughout.
Scroll sideways without dragging the scrollbar Give your mouse wheel another job Here's one I use surprisingly often when I'm working with a wide worksheet: hold Ctrl+Shift and scroll the mouse wheel.Instead of moving vertically, Excel scrolls horizontally through the worksheet.It's much easier than reaching for the horizontal scrollbar at the bottom of the window.
If I'm keeping my hands on the keyboard, Alt+Page Up and Alt+Page Down move the worksheet horizontally too.There's very little to learn here, which is precisely why I like it.Once you know the shortcut, it becomes one of those navigation methods you stop thinking about.
Give important cells names Make Excel remember what they mean Close Cell references are useful until a formula starts filling up with things like $G$2 and $B$14.Excel's Name Box lets you give important cells meaningful names instead.Select a cell, click the Name Box to the left of the formula bar, type a name, and press Enter.
For example, if a cell contains my tax rate, I could name it Tax_Rate.Then, when I'm building a formula and select that cell, Excel automatically inserts Tax_Rate rather than its cell reference.So instead of: =D2*$G$2 I can end up with: =D2*Tax_Rate That's much easier to understand when I come back to the worksheet later.
You can also create several names at once using Formulas > Create from Selection.This is useful when you have a small block of values with labels beside them and want Excel to turn those labels into names automatically.Collect multiple things before you paste Copy and paste with a better memory This last trick isn't exclusive to Excel, but it's particularly handy when I'm building or reorganizing a spreadsheet.
The Microsoft 365 Clipboard lets you collect multiple things you've copied instead of just the most recent item.Open the Clipboard pane by clicking the launcher icon in the Clipboard group on the Home tab, then choose which item to paste.I find this useful when I'm compiling information from different parts of a workbook.
Instead of copying something, pasting it immediately, then finding the next thing, I can collect several items first and paste them where I need them.The Clipboard keeps those items available even after you've moved on to another task, and it can also hold content copied from other apps.There's also a Paste All option, which pastes the collected items into consecutive cells in the order they were copied.
You can then delete individual items from the pane or clear the whole collection when you're done.The little things add up It's often those little discoveries in Excel that make the biggest difference to my workflow.The same applies to functions.
Many tutorials cover the big names like XLOOKUP and FILTER, but there are plenty of useful functions that rarely get the same attention.Whether you're a power user looking to fill in a few gaps or an Excel newcomer searching for productivity tricks, you might find your next favorite Excel trick somewhere you never thought to look.
Read More
