37 Awesome Excel Mouse Tips & Tricks You Should Know - KING OF EXCEL

Monday, October 26, 2020

37 Awesome Excel Mouse Tips & Tricks You Should Know

 

37 Awesome Excel Mouse Tips & Tricks You Should Know

Mouse Shortcuts For The Window

Close Multiple Workbooks At Once

Shift-Left-Click-to-Close-All-Workbooks 37 Awesome Excel Mouse Tips & Tricks You Should Know

If you’ve got a ton of Excel workbooks open and you want to close them all, then it can be a pain go through and close each workbook individually.

Save all those clicks with this easy trick. Hold the Shift key while you left click on the X. This will close all open workbooks at once.

Don’t Save All Option

Shift-Left-Click-for-Dont-Save-All 37 Awesome Excel Mouse Tips & Tricks You Should Know

Closing multiple workbooks is now easy with the above tip, but when these workbooks contain unsaved changes there is a new challenge.

There is no Don’t Save All option in the subsequent menu! This means if you want to discard all the changes when closing all workbooks, you need to click Don’t Save multiple times.

There’s a shortcut to save all this clicking. Hold the Shift key and left click on the Don’t Save button. This will choose Don’t Save for all the open workbooks.

Quickly Dock Floating Windows

Double-Click-to-Dock-Menus 37 Awesome Excel Mouse Tips & Tricks You Should Know

There are a lot of Excel menus that dock to the side of a workbook. The PivotTable Fields menu, Format Chart Area menu and Format Shape menu all dock to the side of the workbook, just to name a few of them.

You can also turn any of these into a floating window with a left click and drag action on the top title area.

You can then click and drag them back to the side to dock them again. But there is a slightly quicker shortcut for docking menus.

Double left click at the top of the menu when the cursor is a four-way arrow and the window will dock back to its last docked location (left or right side).

Enable Summary Statistics In The Status Bar

Right-Click-to-Enable-Summary-Statistics 37 Awesome Excel Mouse Tips & Tricks You Should Know

You can see at-a-glance summary statistics in the status bar for any range of values you select in Excel.

You can enable this or choose which statistics show up with a right click on the status bar and checking or unchecking the various options available in the following menu.

Mouse Shortcuts For Working With Sheets

Group Sheets Together

Group-Sheet-With-Ctrl-And-Click 37 Awesome Excel Mouse Tips & Tricks You Should Know

Grouping sheets together can be a really powerful tool. It allows you to do the same edits on all the grouped sheets at the same time, which can save massive amounts of time if you need to edit the exact same thing on dozens of sheets.

To group sheet, hold Ctrl and left click on any sheets you want to group. This will allow you to group sheets that aren’t necessarily in sequence as well.

When you’re done, you can right click any of the grouped sheets and select Ungroup Sheets.

Navigate Sheets With A Pop Up Menu

Activate-Sheet-Menu 37 Awesome Excel Mouse Tips & Tricks You Should Know

Navigation between sheets can be made easier using the Activate sheet pop up menu.

To use this menu, right click on either of the small arrows to the left of the workbook sheet tabs. This will open the Activate sheet menu. You can then select the sheet you want and when you press the OK button, you will be taken to that sheet.

Scroll To The First Or Last Sheet

Three-Dot-Sheet-Tabs 37 Awesome Excel Mouse Tips & Tricks You Should Know

If you have a lot of sheets in your workbook then Excel may not be able to display them all on screen. If this is the case, Excel may display three dots on either side of the sheet tabs to indicate there are more sheets.

Show-First-Or-Last-Sheet 37 Awesome Excel Mouse Tips & Tricks You Should Know

You can move the view of the sheet tabs either left or right without selecting a new sheet. Hold Ctrl and left click on the left or right arrow to scroll the tab view to the first or last sheet.

Quickly Copy A Sheet

Creating a copy of a sheet normally takes quite a few steps.

You need to right click on the sheet ➜ select Move or Copy ➜ select which workbook to make the copy in ➜ select the new location ➜ check the Create a copy box ➜ and press OK.

But with this shortcut, you’ll be copying sheets in record setting time instead.

Copy-A-Sheet 37 Awesome Excel Mouse Tips & Tricks You Should Know

To quickly create a copy of any sheet, hold Ctrl then left click and drag the sheet to its new location.

The cursor will turn into a small page icon with a plus sign and a small black arrow will indicate where the new sheet will go. Release the left click to create the new sheet.

Mouse Shortcuts For The Ribbon

Expand Or Collapse The Ribbon

Double-Click-to-Expand-or-Collapse-Ribbon 37 Awesome Excel Mouse Tips & Tricks You Should Know

If you’re looking for more screen real estate to view your awesome spreadsheet, then temporarily removing the ribbon can help.

You can quickly expand or collapse the ribbon with a double left click on any of the ribbon tabs.

A single left click on any of the collapsed ribbon tabs will temporarily expand the ribbon again while you choose a command to use.

Zoom In Or Out On A Worksheet

Scroll-Wheel-to-Zoom 37 Awesome Excel Mouse Tips & Tricks You Should Know

You can make the data in your spreadsheets larger by zooming in, or you can see more of the spreadsheet by zooming out.

Hold Ctrl and use the mouse wheel to zoom in or out of a sheet.

You can change the default behavior of the scroll wheel from scrolling to zooming by going to the File tab ➜ Options ➜ Advanced tab ➜ check the Zoom on roll with IntelliMouse option. This way, you won’t need to hold the Ctrl key to zoom with the mouse.

Select The Entire Sheet

Select-All 37 Awesome Excel Mouse Tips & Tricks You Should Know

The top left corner of the worksheet, where the rows and columns meet, is actually a button. Left click on this and the entire sheet will be selected.

Auto-Fit Column Width

Double-Click-to-Autofit 37 Awesome Excel Mouse Tips & Tricks You Should Know

If the data in your spreadsheet is longer than the column width, then you’ll need to adjust the column to display the data properly. You can use this trick to auto-fit the column to the exact width needed for your data.

Place the mouse cursor on the border between two columns. It will turn into a vertical bar with two arrows pointing left and right, then you can double left click to auto-fit the column width to your data.

Easily Rename A Worksheet

Rename-Sheets 37 Awesome Excel Mouse Tips & Tricks You Should Know

The quickest way to rename a sheet tab is with the mouse. Double left click on the sheet to rename it. Type out the new name and press Enter to confirm the change or Esc to discard the change.

Mouse Shortcuts In Power Query

Edit A Power Query

Edit-Power-Query 37 Awesome Excel Mouse Tips & Tricks You Should Know

The quickest way to open the power query editor and edit a query is using this mouse shortcut from the Queries and Connections window pane.

Double left click the query which you’d like to edit, and this will open the power query editor so you can edit that query.

Change Data Type For Multiple Fields

Change-Multiple-Data-Types 37 Awesome Excel Mouse Tips & Tricks You Should Know

In the power query editor, there’s a handy data type icon to the left hand side of each column heading. You can use this to easily change the data type of any column.

If you select multiple columns and hold Ctrl while pressing this icon, you’ll be able to change the data type for multiple columns at the same time. Holding Shift will also work.

Mouse Shortcuts With Pivot Tables

Show Detailed Pivot Table Data

Double-Click-to-Show-Detail 37 Awesome Excel Mouse Tips & Tricks You Should Know

Pivot tables are the great for summarizing data, but what if you then want to see the detail behind those summarized values?

With this mouse trick, you’ll be able to see just the data for any value inside a pivot table.

Double left click on any value inside your pivot table and Excel will create a new sheet will all the data that makes up that value. This is a must know shortcut for data exploration.

Open The Value Field Settings

Open-Value-Field-Setting-Menu 37 Awesome Excel Mouse Tips & Tricks You Should Know

There are a couple different ways to access the Value Field Settings for a field in your pivot table.

  • Go to the PivotTable Fields window ➜ left click on the field in either the Filters, Columns, Rows or Values area ➜ select Value Field Settings from the menu.
  • Select a cell in the field inside the pivot table ➜ go to the Analyze tab ➜ press the Field Settings command.
  • Select a cell in the field inside the pivot table ➜ right click on the field ➜ select Value Field Settings from the menu.

But the quickest way is with this mouse shortcut. Double left click on the column heading inside the pivot table.

Expand Or Collapse Pivot Table Fields

Double-Click-to-Expand-or-Collapse-Pivot-Table 37 Awesome Excel Mouse Tips & Tricks You Should Know

With two or more fields in the Rows (or Columns) area of a pivot table it’s possible to expand or collapse fields to show more or less detail.

Pivot tables have an option to show a small icon next to a field item to expand or collapse the field. This can be turned on from the Analyze tab with the +/- Buttons command.

If you have these buttons turned off, you can still expand or collapse fields using this mouse trick. Double left click on any field item and it will expand or collapse.

Mouse Shortcuts For Quick Navigation

Double-Click-to-Navigate-Data 37 Awesome Excel Mouse Tips & Tricks You Should Know

There are many ways to navigate around your data quickly using keyboard shortcuts. But if your hand is already on the mouse, there’s no need to move to the keyboard, you can use this mouse shortcut to navigate around your data just as fast as the keyboard.

Place the mouse cursor on any edge of the active cell. It will turn into a cursor with a four way arrow. You can then double left click and the active cell will move to the last non empty cell in your data.

Quickly Adjust The Scroll Bar

Expand-or-Collapse-the-Scroll-Bar 37 Awesome Excel Mouse Tips & Tricks You Should Know

If your scroll bar is too small or too big, you can quickly reset it to the default size with this tip.

There are three ellipses on the left side of the horizontal scroll bar. When you hover the mouse cursor over them, the cursor will change to a double bar with two arrows, and you can use these to left click and drag the scroll bar to resize it. If you want to quickly reset it back to the default size, simply double left click on these ellipses.

Scroll Bar Options

Scroll-Bar-Options 37 Awesome Excel Mouse Tips & Tricks You Should Know

The scroll bars in Excel are pretty handy, but they can sometimes be a bit clumsy. For more precise scrolling options right click on either the horizontal or vertical scroll bars.

Format Chart Area Shortcut

Format-Chart-Area-Shortcut 37 Awesome Excel Mouse Tips & Tricks You Should Know

This is one I use all the time, as it’s the quickest way to start formatting your charts. Double left click on any chart to open up the Format Chart Area window pane menu.

Select Or Deselect Multiple Non Adjacent Ranges

Select-and-Deselect-Non-Adjacent-Cells 37 Awesome Excel Mouse Tips & Tricks You Should Know

Holding Ctrl while selecting cells will allow you to select multiple non adjacent cells.

This will also work to deselect cells in cases where you accidentally select the wrong cell. No need to start over, just left click on the selected cell while holding Ctrl to deselect it.

Quick Navigation With The Watch Window

Navigate-with-Watch-Window 37 Awesome Excel Mouse Tips & Tricks You Should Know

The watch window is a great formula debugging option that allows you to keep your eye on multiple formula results across different sheets.

Double left click on any item in the watch window and you’ll be taken to the location of that cell in your workbook. A very handy navigation trick!

Mouse Shortcuts To Cut Copy And Paste

Double-Click-to-Open-the-Clipboard 37 Awesome Excel Mouse Tips & Tricks You Should Know

The clipboard allows you to copy multiple items at a time and then later paste any of them, not just the last item you copied.

You can open the clipboard with a double left click on the copy command found in the Home tab.

If this shortcut doesn’t work, you might need to enable it first. Go to the Home tab ➜ click on the small icon in the lower right corner of the Clipboard section ➜ press the Options button in the Clipboard menu ➜ enable the Show Office Clipboard When Ctrl + C Pressed Twice option.

Copy And Paste Any Object Or Data

Click-and-Drag-to-Copy-and-Paste 37 Awesome Excel Mouse Tips & Tricks You Should Know

This is my favourite way to copy and paste. It works for any object or data in Excel.

Select the object or data ➜ hover the mouse cursor over the edge so that it turns to a four way arrow ➜ hold the Ctrl key ➜ left click and drag the data or object to its new location ➜ release the left mouse button.

Click And Drag To Copy Down Data And Formulas

Click-and-Drag-to-Copy-Down 37 Awesome Excel Mouse Tips & Tricks You Should Know

You might have noticed the active cell selection has a small square on the lower right corner. This is called the fill handle, and it can be used to fill in data or formulas.

Hover the mouse cursor over the lower right corner of the active cell selection until the cursor changes to a small black plus cross. Left click and drag down to copy and paste the contents to the cells below.

Double Click To Copy Down Data And Formulas

Double-Click-to-Copy-Down 37 Awesome Excel Mouse Tips & Tricks You Should Know

This mouse tip is indispensable when it comes to copying down data or formula to the last row.

Select the cell which contains the data or formula which you want to copy and paste down. Hover the mouse cursor over the lower right corner of the active cell selection until the cursor changes to a small black plus cross. Double left click and this will copy and paste the data or formula down to the last filled cell in the column directly to the left.

Right Click And Drag For Advanced Options

Right-Click-and-Drag-for-Advanced-Options 37 Awesome Excel Mouse Tips & Tricks You Should Know

You can also get access to more advanced fill handle options by dragging the fill handle using the right mouse button instead of the left. When you drag down and release, a menu with more advanced options will appear.

This can be used to create simple patterns with numbers and dates, or you can use flash fill to fill text data by based on a couple examples you provide.

Click And Drag To Cut And Paste

Click-and-Drag-to-Cut-and-Paste 37 Awesome Excel Mouse Tips & Tricks You Should Know

This is a simple way to cut and paste data in Excel. Select the data then left click and drag the data to its new location.

Shift Existing Data With Cut And Paste

Shift-Left-Click-and-Drag-for-Cut-and-Paste 37 Awesome Excel Mouse Tips & Tricks You Should Know

This trick will allow you to cut and paste data but shift any existing data out of the way when moving it to a new location.

Select the data then hold Shift and left click and drag the data to its new location. Excel will show a green bar to indicate where the data will be inserted.

Click And Drag To Copy And Paste With Advanced Options

Right-Click-and-Drag-Selection-for-Advanced-Options 37 Awesome Excel Mouse Tips & Tricks You Should Know

You can cut/copy and paste data using the right mouse button as well. When you release the right button, you’ll get a menu with some more advanced options to cut/copy and paste.

Mouse Shortcuts To Use With Formulas

Enter Multiple Function Arguments

Add-Commas-with-Ctrl 37 Awesome Excel Mouse Tips & Tricks You Should Know

Some functions like SUM, COUNT, AVERAGE etc… allow for a varying number of argument inputs.

If you have a long list of argument to input, it can be a pain because you need to separate each of them with a comma.

Hold Ctrl while selecting the arguments in a function. This will add commas between each argument for you.

Edit Contents Of A Cell

Double-Click-to-Edit 37 Awesome Excel Mouse Tips & Tricks You Should Know

To edit the contents of any cell in a worksheet simply double left click on the cell. This will allow you to edit cells that contain data and formulas.

Select A Single Word Or Part Of A Formula

Double-Click-to-Select-Part 37 Awesome Excel Mouse Tips & Tricks You Should Know

Using the cursor click and drag to select part of the data or formula in the formula bar can be tedious. But you won’t need to with this shortcut.

Double left click to select a single word or part of a formula from the formula bar.

Select The Entire Data Or Formula

Triple-Click-to-Select-All 37 Awesome Excel Mouse Tips & Tricks You Should Know

If you need to select everything then there’s also a mouse shortcut for that. Triple left click to select everything in the formula bar.

Resize The Formula Bar

Resize-Formula-Bar 37 Awesome Excel Mouse Tips & Tricks You Should Know

If the formulas in your spreadsheet start to get long, then they might appear cut off in the formula bar. You can resize the formula bar vertically or horizontally by clicking and dragging along the horizontal edge or between the name box and formula bar.

When you hover the mouse over these areas the cursor will change to a two-way arrow. You can then use the quick collapse and expand toggle to quickly switch between the one-line size and the expanded size.

You can also quickly set the formula bar back to the default vertical size by with a double left click on the three ellipses between the name box and the formula bar.

Mouse Shortcuts With Shapes And Objects

Lock Drawing Mode

Lock-Drawing-Mode 37 Awesome Excel Mouse Tips & Tricks You Should Know

The problem with inserting shapes is you have to insert them one at a time. This means going to the Insert tab ➜ selecting Shapes from the ribbon ➜ selecting your desired shape ➜ then drawing in the sheet. You have to do this each and every time, even if you’re wanting to draw the same shape multiple times.

Lock drawing mode allows you to draw the same shape multiple times without needing to select it each time.

Right click on the shape and select Lock Drawing Mode from the menu. You will now be able to draw as many of the shapes as you want.

Snap To Grid

Snap-to-Grid 37 Awesome Excel Mouse Tips & Tricks You Should Know

Aligning shapes in the workbook is tough if you’re trying to do so freehand. You likely won’t be able to get it exactly right.

There are options in the ribbon to help with alignment, but there’s an even quicker mouse trick for this.

Hold Alt then left click and drag the object to snap them to the worksheet grids. Now you can easily align object using the grid.

Conclusions

There are many time saving tips and tricks you can use with your mouse in Excel.

Not all of these will be quicker to use over an equivalent keyboard shortcut, but they can still be quite handy when your hand is already on the mouse.

For the most efficiency and speed in Excel, you really need to know the mouse and keyboard. There are tons hidden mouse shortcuts to know, so let me know in the comments if I’m missing your favourite.

#evba #etipfree #eama #kingexcel 

Popular Posts