Contextures

Excel Worksheet Tips

Tips for working with Excel worksheets efficiently, scrolling, saving, and default settings

Video: View Two Sheets in Same Workbook

While working in an Excel file, you can create a new window, and see two of the worksheets at the same time. This is a good way to troubleshoot formulas, as shown in this short video. The written instructions are below the video.

View Two Sheets in Same Workbook

While working in an Excel file, you can create a new window, and see two of the worksheets at the same time.

To create a new window:

  1. On the Excel Ribbon, click the View tab
  2. Click the New Window command

    New Window command

  3. The file name in the title bar will now show a number at the end

    number at end of file name

To see both windows,

  1. On the Excel Ribbon, click the View tab.
  2. Click the Arrange All command

    arrange windows

  3. Select one of the Arrange options, such as Vertical
  4. If you have multiple workbooks open, and only want to see the windows for the active workbook, add a check mark to "Windows of active workbook"
  5. Click OK.

    arrange windows horizontal

  6. At first, both windows will show the same worksheet.

    arrange windows horizontal

  7. To see a different sheet in one of the windows, scroll through the sheet tabs, and click on the tab that you want to see in the second window.

    arrange windows horizontal

NOTE: If you save the file with two windows open, and close Excel, those two windows will appear the next time that you open the file. To return to one window, see the instructions in the next section.

Close One Window in Excel Workbook

If you have two windows open within an Excel workbook, you can close one of the windows, and go back to a single window.

To close one of the windows,

  1. Select the window that you want to close. NOTE: It is best to leave open the window that is numbered 1 -- otherwise, some of the view settings, such as hidden gridlines, might be lost.
  2. Click the X at the top right of the selected window, to close it.

    arrange windows horizontal

  3. OR, use the keyboard shortcut, Ctrl + W, to close the selected window.
  4. To return the remaining window to full size, double-click its title bar, OR click the Maximize button at the top right of the file's window.

    arrange windows horizontal

View Two Workbooks in Excel Window

In Excel 2010, and earlier versions, there is only one main window in an Excel session. You can arrange the workbook windows, to see two or more Excel files at the same time.

To see two or more Excel files:

  1. On the Ribbon, click the View tab, then click Arrange All

    arrange windows

  2. In the Arrange Windows dialog box, click one of the Arrange options, such as Horizontal, then click OK.

    arrange windows horizontal

Video: Copy and Rename a Worksheet

Watch this short video to see the steps for copying and renaming an Excel worksheet. The written instructions are below the video:

Quickly Copy a Worksheet

To quickly copy a worksheet:

  1. Click on the sheet tab that you want to copy.
  2. Press the Ctrl key, and drag the sheet tab to the location where you want the copy. You will see a small arrow, and a sheet symbol with a + sign, as you drag.

    copy worksheet

  3. When the arrow is in the location where you want the copy, release the mouse button, and then release the Ctrl key.

Quickly Rename a Worksheet

To quickly rename a worksheet:

  1. Double-click on the sheet tab, to select the current sheet name
  2. Type a new name, and press the Enter key

copy worksheet

Center Headings Without Merging

It's best to avoid merged cells, if possible, because they can make it harder to sort and filter a worksheet. To create multi-column headings, you can center text over several columns, without merging the heading cells. For example, type the heading "Qtr 01", and center it over the columns for January, February and March.

To center headings, without merging the cells:

  1. Type the heading in one of the cells where you want it centered. In this example, "Qtr 01" is typed in cell B3.

    copy worksheet

  2. Select all the cells where you want the heading centered -- cells B3:D3 in this example.
  3. On the Ribbon's Home tab, click the Alignment Settings button, at the bottom right of the Alignment group.
    Or, press Ctrl + 1 -- the shortcut to open the Format Cells window -- and click the Alignment tab.

    copy worksheet

  4. In the Horizontal dropdown, select Center Across Selection, then click OK

copy worksheet

The heading will be centered across the selected columns, but the cells are not merged -- you can select any cell individually.

TIP: Add an outside border to the centered heading, so it looks like a single, merged cell.

copy worksheet

Copy a Worksheet to another Workbook

NOTE: If both workbooks are visible, you can use the mouse shortcut to drag a copy from one window to another.

To copy a worksheet to a different workbook:

  1. Open both workbooks
  2. Activate the workbook which contains the sheet that you want to copy
  3. Right-click on the sheet tab that you want to copy, and click Move or Copy.

    copy worksheet

  4. From the "To Book" drop down, select the name of the workbook where you want to add a copy of the worksheet.
  5. From the "Before Sheet" drop down, select a location for the sheet.
  6. Add a check mark to "Create a Copy", and click OK

    copy worksheet

  7. Check that the sheet appears in both workbooks, and then you can save and close the files.

Enter Data on Multiple Sheets at Once

If you select two or more sheets, anything you do on the active sheet will also happen on the other selected sheets.

WARNING: After you finish your changes, remember to ungroup the sheets!

  1. To select all the sheets, right-click on any sheet tab, and click Select All Sheets.
  2. In the Title Bar at the top of the Excel window, you should see the file name, with [Group] after the name. That will remind you that multiple sheets are selected.
  3. Make your changes on the active sheet.
  4. Then, to ungroup the sheets, right-click on the sheet tab for one of the selected sheets
  5. Click Ungroup Sheets.

Save Worksheets as PDF File

To save worksheet(s) as PDF file:

  1. Select the worksheet(s) that you want in the PDF file
  2. On the Ribbon, click the File tab, then click Save & Send
  3. Click Create PDF/XPS Document, then click Create PDF/XPS

    createpdf/xps

  4. Select a folder for the file, and type a file name,
  5. (optional) Click the Options button, and select the file settings that you need, and click OK.

    createpdf/xps

  6. Click Publish, to create the PFD file in the selected folder.

Video: Prevent Scrolling on a Worksheet

To prevent people from scrolling on a worksheet, you can set the sheet's ScrollArea property. Watch this video to see the steps, and the written instructions are below the video.

Prevent Scrolling on a Worksheet

Even if a worksheet is protected, you are able to scroll in any direction, and see what is on the sheet. To prevent scrolling, you can change the ScrollArea property for the worksheet, either manually or with a macro.

The Scroll Area setting is handy as a navigation aid, and it might deter people from clicking or scrolling through the worksheet. However, it can be easily removed, so do not depend on it to add security to your file.

NOTE: The Scroll Area has to be set each time the workbook opens. To automate this, you can use a macro, as shown in the next section.

To manually set the Scroll Area:

  1. On the Ribbon, click the Developer tab, and click Properties

    properties button on Developer tab

  2. In the Properties window, enter a range address in the ScrollArea property box

    properties button on Developer tab

  3. Click on the worksheet, and you will be restricted to clicking and scrolling in the Scroll Area.

To manually clear the Scroll Area:

  1. On the Ribbon, click the Developer tab, and click Properties
  2. In the Properties window, delete the address in the ScrollArea property box
  3. Click on the worksheet, and you will be restricted to clicking and scrolling in the Scroll Area.

Set Scroll Areas With a Macro

The Scroll Area setting has to be set each time the workbook opens, and you can automate that with a macro.

Copy the SetAllScrollAreas macro to a regular module in your workbook. It will set the Scroll area for each worksheet in the file, based on the Used Range on that sheet.

Sub SetAllScrollAreas()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.ScrollArea = ws.UsedRange.Address
Next ws
End Sub

Then, add the SetAllScrollAreas macro name to the Worksheet_Open procedure in the ThisWorkbook module. This will set the scroll areas on all sheets, each time that the workbook is opened and macros are enabled.

Private Sub Workbook_Open()
    SetAllScrollAreas
End Sub 

properties button on Developer tab

Change Number of Sheets in New Workbook

In Excel 2010 and earlier, there are 3 sheets in a new workbook, by default. You can change that setting to a different number. See the steps in in Excel 2007, this short video.

Note: In Excel 2010, click the File tab, instead of the Office button, and go to the General category in the Options.

Download the Sample File

  1. Click here to download the ScrollArea sample file. It is zipped, and in xlsm format, and contains macros.
  2. Click here to download the Center Without Merging sample file. It is zipped, and in xlsx format, and does not contain any macros.

More Excel Tips and Tutorials

Worksheet VBA

Hide Sheets Based on Tab Colour

Worksheet Protection Selector

Worksheet AutoFilter VBA

Copy VBA to Sheet

Workbook Tips

VBA - Get Started

Search Contextures Sites

 

Excel Tools Add-in

 

Free Pivot Table Tools

 

 

excel chart tools by peltier tech

 

 

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

Excel UserForms for Data Entry

 

Last updated: November 2, 2016 4:45 PM
Contextures RSS Feed