Tricks in Excel – everyone should learn some. I’ve used Excel for over 10 years and some of the coolest tricks I know are the simplest to learn.
Little tricks can help you become a more powerful Excel user as they simplify and speed up your work. And there’s a delicious range of mouse tricks I’d like you to discover today.
You might say to yourself “Surely I know how to use the mouse in Excel, everyone knows. Why do I need any more tips and tricks?”
I understand the feeling – but actually there are so many ways to really use the mouse effectively in Excel that probably 80% of users are not using the mouse as effectively as they could.
This includes people who love keyboard shortcuts (like me). It pays handsomely to know a couple of mouse tricks that will help you get the job done better or faster. So read on to find out our Top 20 tricks.
Excel Tutorial Video – Mouse Tricks for Excel
First here is our 13 minute video with our Top 20 mouse tricks for Excel.
Scroll past the video highlights to the Top 20 Mouse Tricks for Excel for step-by-step instructions. These are shown with simple animated screencasts with no audio commentary.
Click on the video below to play it and listen to my audio commentary.
My personal favorites are double clicking to lock the Format Painter tool (4:00 minutes) and exchanging the position of columns or rows using the SHIFT key and dragging the selection border (11:48 minutes).
0:10 – Double click any Ribbon tab to toggle hide / show
0:50 – Use mouse scroll wheel to browse Ribbon menus
1:23 – Double click worksheet tab to rename
2:00 – Right click to show worksheet list in current workbook
2:15 – Select multiple worksheets using SHIFT or CTRL keys
4:00 – Double click to lock Format Painter tool (personal favorite)
5:30 – Working with long formulas
6:45 – Selecting whole words and sentences using double click trick
7:15 – Basic fill handle dragging with left and right mouse buttons
7:48 – Double click fill handle to fill down to last row
8:18 – Fill a series of weekdays
8:30 – Fill a series growth trend (e.g. 1 2 4 8 16 32)
9:35 – F2 to edit formula then drag formula cell ranges
9:50 – Double click on cell border to move to furthest part of table
10:12 – Hold SHIFT key and double click on cell border to select to furthest part of table
10:22 – Hold SHIFT key and single click to select range
10:42 – Hold CTRL key and click to select non-contiguous range
11:10 – Copy formulas and paste as values using mouse only
11:48 – Exchange position of columns or rows using SHIFT + drag (personal favorite)
Try them out and also make sure you watch the other tricks – you’ll probably find a special few that you’ll use day-in day-out.
And do write a comment to tell me if you have any favorite mouse tricks that I did not mention. We all love to learn tips & tricks and it’s nice to share! Use the comment section below.
Learning Microsoft Excel – Our Top 20 Mouse Tricks for Excel Step-by-Step Animations
Here’s the list of our Top 20 Mouse Tricks split into 6 sections:
Excel 2007 saw the introduction of the Excel Ribbon that replaced the menu system in older versions of Excel. These two tricks work in Excel 2007 and 2010, where the Ribbon has become the main method for navigating to most Excel features.
Double click any menu tab on the Excel Ribbon to show/hide the Ribbon
Double click any Ribbon menu tab to hide the Ribbon. This gives you more screen real estate.
When the Ribbon is hidden it will temporarily appear when you click on a menu tab, then hide itself after you click on a button.
Double click any menu tab again to show the Ribbon.
Use your mouse scroll wheel in Ribbon to browse the menu tabs
When you position the mouse pointer inside any part of the Ribbon you can browse between the different menu tabs using the scroll wheel.
Scrolling up with the mouse moves you left in the menus, and scrolling down with the mouse moves right in the menus.
This can be handy if you want to remind yourself where a certain Excel feature is kept in the Ribbon, as you can quickly browse through all the menus without clicking through each one.
And you can move from the first menu tab to the last menu tab by quickly scrolling the wheel.
These mouse tricks help you deal with naming worksheets, navigating worksheets and selecting worksheets.
Simply double click on a worksheet name to rename that worksheet. No need to right click on the worksheet then select “Rename Sheet”.
It’s only a small time saver but it makes the renaming process much faster if you are doing this over many worksheets. And I find it helps me to keep my mind focused on more important things like what I’m actually going to do on the worksheets after I have renamed them.
If you like keyboard shortcuts, try this combination where you can create a new worksheet and rename it quickly:
Right click on arrow icons to show a list of all worksheets
Click your right mouse button anywhere over the arrow icons just to the left of the sheet names.
The worksheets in your workbook are displayed and you can go to any of them without having to scroll to it individually.
This is helpful when working with workbooks with lots of worksheets.
If you have more than 16 worksheets, Excel will show you the first 16 worksheets and give you an option to view the rest by clicking on “More Sheets…”
Hold CTRL key and click on worksheets to group them together
You can select many worksheets by clicking on the first one, holding the CTRL key, then clicking on the second, third, fourth etc. all while holding the CTRL key.
The sheets don’t need to be next to each other, and you can select them in any order you want.
The first sheet you select will always remain selected, but you can deselect a sheet by clicking on it again. So if you went too far and selected four sheets instead of three you can simply click on the fourth sheet to deselect it (all while holding down the CTRL key).
Once you group sheets using the CTRL key you can apply an action to them all at once.
Say you have selected four worksheets using the CTRL key, you can change them all to a red sheet color and reposition them together all at once.
Select worksheet 1, hold the SHIFT key, click on worksheet 2, and this selects all the
worksheets from worksheet 1 to worksheet 2
Similar to 5. above but instead of clicking on sheets separately, you first select one sheet, hold down the SHIFT key, then click on the second sheet.
All sheets from the first sheet to the second sheet will be grouped together.
Again you can apply actions to all the grouped sheets in one go, which saves time if you need to change the color of many sheets or reposition lots of sheets.
The Excel Format Painter is a great tool for copying any existing cell formats to other parts of your workbook. This trick makes it more powerful, and is one of my favorite mouse tricks for Excel.
Double click to lock the Excel Format Painter
Every time you click the Format Painter icon you can only apply the selected formatting once. But if you double click the Format Painter icon you lock the Format Painter to ON and can apply your formatting as many times as you want.
To deselect the Format Painter press the Escape key or click on the Format Painter icon once.
Remember you can select more than one cell before you click the Painter icon.
Sometimes you will create or come across a very long formula, or maybe a big paragraph of text in one cell. When this happens it can get tricky to read and work with the cell’s content in the formula bar.
Drag the lower edge of the Formula Bar to expand it vertically or double click the lower edge to automatically resize it to fit the cell contents.
If you have multiple words to select, double-click on the first word and then drag while keeping the mouse button pressed to select the remaining words.
When you select one or more cells in Excel, a small black rectangle appears in the bottom right-hand corner of the selection.
Move the mouse pointer over the small black rectangle and the mouse pointer changes to a black cross-hair. Now you can use the Fill Handle to do some fast filling.
Click and drag the fill handle to fill in the direction of your drag
Click and drag the fill handle and Excel will try to continue your series (up, down, left, right), or if you don’t have a series Excel will simply fill the selected cells.
If you drag over existing cells they will be replaced, and if you drag ‘backwards’ you will see a grayed out area. The cell contents of the grayed out area are deleted when you let go of the mouse button.
Right click then drag Fill Handle - when you release the right mouse button you are
presented with a list of options to select
Right click and drag the fill handle and you can select from the options to:
Double click on the fill handle to autofill the series of data or formulas.
Excel will make an intelligent guess at where to stop filling down. With current versions of Excel you cannot autofill to the right by double-clicking.
Here’s a motley assortment of tricks to help you work with cell selections in Excel. This includes one of my favorite tricks for switching the position of rows and columns (trick number 18)
13. Edit Cell Ranges in Formulas
Use the mouse to edit cell ranges in a formula after pressing F2 to edit the formula
1. Click on the cell with the formula you want to edit.
2. Press F2 to edit the formula.
3. Excel shows you colored borders around each range in that formula. You can simply select a range by 4. clicking its border, then you can start dragging and resizing it.
5. Press Enter to accept the changes, or Escape to cancel.
Double click on cell border to jump to furthest row or column
First select a cell in your data table and double click on the cell-border in the direction you want to go (up, down, left, right).
You can press and hold the Shift key to make a selection when jumping. You can also select a region of cells before the jump.
Quickly select a range of cells by holding down the SHIFT key before clicking
1. First select a cell.
2. Hold the SHIFT key.
3. Select a second cell.
4. Your selection gets extended to the second cell.
Select multiple cells in a complex pattern by holding down CTRL while clicking
1. First select a cell or range of cells.
2. Hold the CTRL key.
3. Select a second cell or range of cells.
4. Select a third cell or range of cells.
5. … and so on as many times as you want.
6. Every cell or range you select gets added to your selection.
After selecting multiple cells you can change the cell formatting, number format, formulas all in one go. You can also copy and paste your selection.
If you want to change formulas in a multi-cell selection you should enter the formula then press CTRL + Enter. If you only press Enter the formula is only written in the active cell, not the whole selection.
One limitation is that you cannot deselect cells using this method, you need to start over again if you select a cell you didn’t want to select.
Move your selected range of cells by dragging the dark outside border
Select a range of cells then drag the border to move that range to a new position. If you move the range over cells with existing content, the existing content is overwritten.
One of my favorites! Quickly swap ranges using SHIFT + Drag
Select a range of cells then hold SHIFT and drag the border to move that range to a new position, swapping it for whatever was there before instead of overwriting any existing content.
A light gray insertion marker shows where the range will go.
This is great for swapping the order of rows and columns. Play around with it to find out how it works. This tip deserves its own article because it’s so cool!
Quickly copy a ranges by holding down the CTRL key while dragging the dark outside
Select a range of cells then hold CTRL and drag the border to copy that range to a new position, this overwrites any existing content.
You can combine tips 18 and 19 to insert a copy of your range. Select your range, then hold CTRL + SHIFT and drag to insert the copy. I think this is very cool!
Use the right mouse button to drag the selection border, then choose to copy here as
1. Select a range of cells
2. Use the right mouse button to click the border
3. Keep the right mouse button pressed, and drag one row to the right
4. Keep the right mouse button pressed, and drag back into the original position
5. Release right mouse button
6. Select ‘Copy here as values only’
Converting a whole column or row of formulas is pretty fast using this method, as you can see in the screencast I convert column F from formulas to values in a couple of seconds.
P.S. Keyboard enthusiasts will say it’s even faster with keyboard shortcuts, and I’m not going to argue, but it does take a bit more memorizing to get the keystrokes second-nature. For instance to replace the whole column with values:
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.