Learn how to become an Excel Power User with our Top 20 Mouse Tricks for Excel

Mouse Power TipsTricks 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.


[tubepress video=”01trj3i6o5s”]

Video highlights

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).

Section 1 – Excel Ribbon Tricks

0:10 – Double click any Ribbon tab to toggle hide / show
0:50 – Use mouse scroll wheel to browse Ribbon menus

Section 2 – Working with Worksheets

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

Section 3 – Locked Format Painter

4:00 – Double click to lock Format Painter tool (personal favorite ☺ )

Section 4 – Editing Long Formulas

5:30 – Working with long formulas
6:45 – Selecting whole words and sentences using double click trick

Section 5 – Fill Handle Tricks

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)

Section 6 – Cell Range Tricks

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 ☺ )

What do you think?

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:

  1. Excel Ribbon Tricks
  2. Working with Worksheets
  3. Locked Format Painter
  4. Editing Long Formulas
  5. Fill Handle Tricks
  6. Cell Range Tricks

Section 1 – Excel Ribbon Tricks

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.

1. Double click to hide/show Ribbon for more workspace

[Image] Double click Ribbon to show/hide

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.

2. Ribbon scroll trick

[Image] Use mouse scroll wheel in 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.


Section 2 – Working with Worksheets

These mouse tricks help you deal with naming worksheets, navigating worksheets and selecting worksheets.

3. Double click to rename worksheet

[Image] Double click to rename Worksheet

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:

  • Shift + F11 (create a new worksheet)
  • ALT ➜ H ➜ O ➜ R (rename worksheet)

This comes courtesy of xlcalibre’s post “Top 5 Excel Keyboard Shortcut Combos“.

4. Right click to show worksheets list

[Image] Right click to show worksheets list

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…”

5. CTRL + Click to select non-adjacent worksheets

[Image] 5. CTRL + Click to select non-adjacent worksheets

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.

6. Shift + Click to select adjacent worksheets

[Image] Shift + Click to select adjacent worksheets

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.


Section 3 – Locked Format Painter

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.

7. Lock the Excel Format Painter [Personal Favorite]

[Image] Lock the Excel Format Painter

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.


Section 4 – Editing Excel Formulas

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.

8. Vertically Expand the Excel Formula Bar

[Image] Vertically Expand the Excel 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.

9. Double click to select whole words

[Image]

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.


Section 5 – Fill Handle Tricks

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.

10. Drag Fill Handle

[Image] Drag Fill Handle

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.

11. Right click then drag Fill Handle

[Image] Right click then drag Fill Handle

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:

  • Copy Cells
  • Fill Series
  • Fill Formatting Only
  • Fill Without Formatting
  • Fill Days
  • Fill Weekdays
  • Fill Months
  • Fill Years
  • Linear Trend
  • Growth Trend
  • Series…

12. Double click Fill Handle

[Image] Double click Fill Handle

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.


Section 6 – Cell Range Tricks

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

[Image] 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 clicking its border, then you can start dragging and resizing it.
  4. Press Enter to accept the changes, or Escape to cancel.

14. Jump to furthest row or column

[Image] Jump to furthest row or column

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.

15. Fast cell range selection with Shift + Click

[Image] Fast cell range selection with Shift + Click

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.

16. Complex cell range selection with CTRL + Click

[Image] Non-contiguous cell range selection with CTRL + Click

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.

17. Move selected range by dragging the border

[Image] Move selected range by dragging range border

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.

18. Swap selected cell ranges fast with Shift + Drag [Personal Favorite]

[Image] Swap selected cell ranges fast with Shift + Drag

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!

19. Copy selected cell ranges fast with CTRL + Drag

[Image] Copy selected cell ranges fast with CTRL + Drag

Quickly copy a ranges by holding down the CTRL key while dragging the dark outside border

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!

20. Convert formulas to values

[Image] Convert formulas to values

Use the right mouse button to drag the selection border, then choose to copy here as values only

  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:

  • CTRL + Space to select the whole column
  • CTRL + C to copy
  • CTRL + ALT + V to bring up the paste special dialog box
  • V to select paste as values
  • Enter to close the paste special dialog box

Subscribe to our email newsletter

If you like Excel tips & tricks why not subscribe to our free weekly newsletter, which you can do by filling out the form below the sharing buttons.

And remember to share this great free resource with your friends and colleagues using the sharing buttons – they’ll love you for it!

Cheers – Victor


About-Launch-ExcelWelcome to Launch Excel
If this is your first time to LaunchExcel.com - Welcome to Launch Excel. My name is Victor and I'm the Chief Teaching Officer of Launch Excel, a website I started to help you learn how to use Microsoft Excel more effectively. Check out my welcome page created specially for you: Click here to view Welcome Page


7 Responses to Learn how to become an Excel Power User with our Top 20 Mouse Tricks for Excel
  1. rolo
    March 8, 2012 | 9:44 pm

    Hi, I always read your tips. They are very nice.

    Can you tell me which software do you use to build the animated gif?

    Thanks!

    • Victor
      March 9, 2012 | 3:29 pm

      I searched for a long time to find good software for animated GIFs, then finally found Camtasia Studio 7. It’s pretty good for recording screencasts with audio plus it can create animated GIFs very fast and at good compression rates resulting in small file sizes.

      Check out Camtasia Studio 7 by clicking here

      P.S. I also tried Snagit 10 (from the same people who create Camtasia Studio) – while it was very cool for still screenshots it does not support animated GIFs. But it does do full web-page captures and lots more. If you’re interested in both Camtasia Studio 7 and Snagit 10, check out this link here.

  2. Excel Data Entry Tricks
    April 1, 2012 | 11:32 pm

    […] Learn how to become an Excel Power User with our Top 20 Mouse Tricks for Excel […]

  3. […] to make your work a little easier. I use a lot of worksheets in my files and his tip on Right-clicking to show a worksheets list was a quick win for […]

  4. mouse tricks$tips
    September 27, 2012 | 12:45 am

    […] your $ profit,  if you use these 20 mouse tricks for excel which are presented in this article in […]

  5. frankie castellini
    September 30, 2012 | 1:41 am

    I have a bunch of excel files in a directory filled with data.
    If I need to find out where the word ” Apple” is stored in, how would I search for that.
    Windows search used to work for me but does not work anymore.
    Do you have a tip on this search thing mate!!!

    • Victor
      October 1, 2012 | 10:36 am

      Hi Frankie, I recommend you post your request on an Excel forum (e.g. http://www.excelforum.com/). Sorry I can’t be of direct help.
      Cheers
      Victor