Learn Excel Blog

Copy Excel Worksheet – Easy and Fast

               

You’ve created the perfect report for January. Now you need to make 11 copies for the other months.

QuickTipCopySheet

Hold down the Ctrl key.

While holding down the Ctrl key, click on the sheet tab and drag to the right. You should see the mouse pointer change to an arrow pointing to a sheet with a + sign on it. Repeat this 10 more times to create 11 worksheets.

Downside: The sheets will be called Jan (2), Jan (3). You will have to rename each sheet.

Someday: Excel will be smart enough to use the Fill Handle logic to rename the sheets.  But not yet.

 

Use Wheel Mouse to Scroll Left & Right in Excel

               

Sure… you can roll the mouse wheel to scroll up and down…

What about left and right?

QuickTipScrollHorizontal

Your mouse wheel can be pushed in, like you are clicking a button on the mouse, but you are “clicking” the mouse wheel. While you hold the mouse wheel down, move the mouse left or right to scroll!

 

 

Move to Top of Selection in Excel

               

You start at the top of the data set. You press Ctrl+Shift+DownArrow and Ctrl+Shift+RightArrow. This is great… you’ve selected the whole data set, but now how do you get back to see the top of the selection?

quicktipstopofselection

Press Ctrl+Period twice.

Each press of Ctrl+Period keeps the current selection but moves to the next corner (in a counter-clockwise direction). So, if you are currently at the bottom right of the selection, press Ctrl+Period twice will get you to the top left corner of the selection.

 

Easy Territories in Excel Pivot Tables

               

You pivot table has a list of cities, but no Territory field:

ptterritories

There is an easy way to create a virtual Territory field:

1) Select the cities in the first territory (Click on first city, Ctrl+Click on others)

2) In the Pivot Table Tools ribbon, click Group Selection.

3) By default, they call the territory “Group1”, but you can click in the cell and type a meaningful name, like “South Florida”. Also, click on the heading cell and type a new name such as Territory.

4) To create the next territory, select those cities and repeat steps 2 & 3.

When you are done, select the Territory heading, click Field Settings, and change the Subototals from None to Automatic.

 

View 2 Excel Workbooks Side by Side where both Scroll!

               

Need to see two workbooks side by side?

syncscrolling

 

With both workbooks open, go to the View tab and click View Side by Side. This column of 3 icons are the first to shrink on this ribbon tab, so most of the time it is a tiny unlabeled column as you see on the right.

Why is this better than View, Arrange All, Vertical? Because it offers Synchronous Scrolling… if you move the left workbook down 15 rows, the right workbook moves as well.  What if the workbooks are not quite lined up in the first place? Toggle off Synchronous Scrolling, adjust them so they line up, and toggle it back on.

Needs to see two worksheets of the same workbook side by side? Do View, New Window first. Your one workbook will now appear in a :1 and a :2 window in the Switch Windows menu. It is not two copies of the workbook, just two window containers that can each look at different parts of the workbook (Sheet1 versus Sheet2…. or Top of Sheet1 and Top of Sheet2). After doing View, New Window, turn on View Side by Side. When you are done, use the “X” icon at the top of the :2 version to close the second window.

Learn Excel from MrExcel