Home > Structure > Workbook > Workbook Tips Excel Workbook Tips Turn off Narrator voice, stop personal information warning, copy existing Microsoft Excel workbooks, change Save As settings, and more Excel workbook tips

Personal Information Warning

In some Excel workbooks, when you try to save the file, you'll see a warning message:

• "Be careful! Parts of your document may include personal information that can't be removed by the Document Inspector."

Here's a screen shot of the error message -- I put the text onto 2 lines, so it's easier to read.

If you click OK in that warning message, another message appears: "Document not saved."

Turn Off the Personal Information Warning

If you're tired of seeing that message, follow these steps to turn it off, in the active workbook.

NOTE: No other workbooks will be affected by the change to this setting.

• At the top left corner of Excel, click the File tab
• At the bottom left, click Options
• In the list at the left, click Trust Center
• At the right, click the Trust Center Settings button
• In the list at the left, click Privacy Options
• In the Document-specific Settings section, remove the check mark from "Remove personal information from file properties on save"
• Click OK, twice, to exit the Option settings

File Save to PC by Default

Recently, when saved new Excel files, or used the Save As command, the Personal location was always selected by default: Personal > OneDrive - Personal .

That added an extra step to the saving process, because I had to remember to click the "This PC" location every time!

Change the Location Setting

This setting must have changed after an Office update, and I finally figured out how to make "This PC" the default location again.

To change the setting for your workbooks, follow these steps:

• On the Excel Ribbon, click the File tab
• At the left, click Options (near the bottom of the list)
• In the category list, click on Save
• Then, in the Save Workbooks section, add a check mark to this setting:
• Save to Computer by default
• Click OK, apply the setting, and to close the Excel Options window
  

Turn off Narrator Voice in Workbook

While working in Excel, you can turn on the Windows Narrator, intentionally or accidentally. The Narrator will read the contents of your worksheet cells, when you select them.

Here is the shortcut key combination to turn the Windows Narrator on or off:

• Windows logo key + Ctrl + Enter

Video: Create Workbook Copy Without Save As

Instead of creating an Excel file from scratch, you can save time by basing the new workbook on an existing file. Open the old file, then use the Save As command, to create a copy.

However, it is easy to forget that step, and you end up making unwanted changes to the original file. To be safe, use one of the tips in this video, to open a copy of the original spreadsheet, and keep it safe from changes.

Watch this video to see the steps for creating a copy of the original file, without using the Save As command.

Copy Worksheet to New Workbook

With just a few clicks, you can create a new workbook, from an existing worksheet.

To move or copy worksheet(s) to a new workbook:

1. Right-click on the sheet tab (or, select a group of sheets, and right-click on one of the selected sheet tabs)
2. Click Move or Copy
3. In the Move or Copy window, click the arrow in the drop down list at the top
4. Select (new book) as the option
5. To copy the sheet, check the create a copy box (leave the check box blank if you want to move the sheet)
6. Click OK

Workbook Navigation Tips

It can be difficult to find a specific sheet, if an Excel file has many worksheets. This video shows how to use navigation arrows, worksheet list, coloured tabs, and spacer tabs, to make it easier to find a specific sheet. The written instructions for spacer tabs are below the video.

Add Spacer Tabs in Workbook

Thanks to AlexJ for sending this Spacer Tab tip. See more of his work on the AlexJ Sample Files page.

In a large workbook, you can colour the sheet tabs, to identify the sections, such as data entry and reports.

To add visual separation between the coloured sheet groups, add a spacer tab:

1. To insert a blank sheet, select the last sheet in a group, then click the New Sheet button.

   

2. To rename the new sheet, double-click the sheet tab, and type a single space character.



NOTE: If inserting additional spacer sheets, use an additional space character for each new spacer sheet, because sheet names cannot be duplicated.



3. Leave the spacer sheet tab with its default "No Fill" colour, or change it to White fill colour, to create a bit of "white space" between the tab groups.

Spacer Tab Tips

1. Don't use too many spacer tabs, because each one will be slightly wider than the previous one. A limit of 2 or 3 spacer tabs works best.
2. If you are concerned that people might click on the spacer tabs, and be confused by the blank sheets, you can add a message on that sheet, or a hyperlink that takes them back to the main sheet.



3. When you right-click the Excel Navigation arrows, the pop-up list of sheets will be clearly separated into groups, due to the blank sheet names. This will make it easier to scan a long list of sheet names.



4. If you view the sheets in the VBA Project Explorer, change the sheet code names, so they are grouped at the end of the list of worksheets. For example, use zSpace01, zSpace02, and so on.

Excel Workbook Macros

In the sections below, there are a few Excel macros to help you save time when setting up workbook, or while working in your existing Excel files.

--1) Quick Workbook Backup Copy Macro

--2) Automatically Add New Sheet in Workbook

--3) Create Annual Workbooks for Each Month

1) Workbook Backup Copy Macro

Here's an Excel workbook backup macro that I use almost every day.

Copy this macro code, and paste it into a regular code module in a workbook that's always open, when you use Excel, such as your Personal Macro Workbook.

Quick Backup Macro Code

The code below will save a copy of the active workbook, in the same folder. The copy has the same file name and extension, with the date and time added to the end of the name.

For example: MyFileName_20190627_0905.xlsx

Sub QuickBU_SameFolder()
Dim wb As Workbook
Dim strFile As String
Dim strName As String
Dim lExt As Long
Dim strDir As String
Dim strExt As String
Dim lPer As Long
Dim strStamp As String

Set wb = ActiveWorkbook
strName = wb.Name
strDir = wb.Path & "\"
strStamp = Format(Now, "_yyyymmdd_HhMm")
lPer = InStr(1, UCase(Right(strName, 5)), ".")

Select Case lPer
  Case 1:     lExt = 5
  Case 2:     lExt = 4
  Case Else:  lExt = 0
End Select

If lExt = 0 Then
  MsgBox "Please save the file and then try again."
  GoTo exitHandler
End If

strExt = Right(strName, lExt)
strFile = Left(strName, Len(strName) - lExt)

ActiveWorkbook.SaveCopyAs _
  strDir & strFile & strStamp & strExt
  
MsgBox "The file was saved as " _
  & vbCrLf _
  & strFile & strStamp & strExt _
  & vbCrLf _
  & vbCrLf _
  & "in the current folder:" _
  & vbCrLf _
  & strDir

exitHandler:
  Set wb = Nothing
  Exit Sub
  
End Sub

2) Macro: Add Sheet When Workbook Opens

In this video, see how a macro automatically adds a new worksheet with the month name, when an Excel file opens at the start of the month.

There are written steps on the Create Workbooks and Worksheets page, and you can download the sample file at the end of the page.

3) Macro: Create Workbooks for Year

In the download section below, there is a sample Excel file that automatically creates monthly workbooks, with daily sheets in each file, created by Roger Govier.

• You can use this macro at the end of the year, when you want to set up your files for the upcoming year.
• The macro prompts you to enter a year number, and another message asks if you want the month's day to be in ordinal format (1st, 2nd, etc.), or normal numbers.

There are written steps on the Create Workbooks and Worksheets page, and you can download the sample file at the end of the page.

Excel Workbook FAQs

Here are a few of the frequently asked questions (FAQs) about Excel workbooks.

Q: How many sheets in a workbook can be active at any given time?

A: Only one sheet can be active at any given time. You can select two or more sheets, which is called grouping. However, only one of those sheets will be active. The active sheet's tab has bold font, and that sheet's contents are visible in the Excel window.

Q: How do I close all the open workbooks?

A: To close all open windows in Excel 2013 and later versions, press the Shift key, and click the X at the top right of one of the Excel windows. You will be prompted to save any unsaved files, and then all the windows will close.

Q: When I start Excel, why do files open up automatically?

A: To stop files automatically opening, you can remove a folder name in the Excel options:

• Click the File tab, then click Options
• Click the Advanced category, and scroll down to the General section.
• In the box for 'At startup, open all files in', you might see the name of a folder, and its path
• Clear the folder information from that box (or go to that folder and remove the unwanted files).
• Click OK, to close the Options dialog box.

Get the Sample Files

• Add Sheet When File Opens -- Automatically add a new worksheet with the month name, when the Excel file opens at the start of the month. ExcelVBA_AddMonthSheet.zip 
• Monthly Workbook Creator -- Click a button, and macro creates a workbook for each month of the year, with a sheet for each day. Updated version of original template. excelcreateyrmthworkbks.zip

More Excel Tips and Macros

Worksheet VBA

List & Close Workbooks

Status Bar Tips

Custom Views

Workbook Backup Tool, Free

PDF Format, Macros to Save Sheets As

Picture Preview

Worksheet AutoFilter VBA

Copy VBA to Sheet

VBA - Get Started