12 Things Guided You How to Manage An Excel Workbook

An Excel workbook is a basic document used to store data and it is a collection of one or more worksheets within a single file. Whereas an Excel worksheet is a single spreadsheet that typically contains descriptive labels, numeric values, formulas, functions, and graphical representations of data. 

By default, a new Excel workbook has a single worksheet (Sheet1). We can add multiple worksheets according to our needs.

(A spreadsheet is an electronic file that contains a grid of columns and rows used to organize related data and to display results of calculations, enabling interpretation of quantitative data for decision making.)

How many worksheets have a workbook depends upon the available computer’s memory.

Each cell is identified by an address which consists of the column letter and the row number. The Name box displays the address of the active cell where we are at the moment.

(01) CREATING A NEW EXCEL WORKBOOK / NEW EXCEL FILE

A. TO CREATE A NEW EXCEL WORKBOOK

When we start a new workbook, we have 03 choices:

(i) Using Run Window: Press Windows+R, which will open ‘Run’ window  ➪ type ‘excel’ in the box right to the ‘Open:’ ➪ press Enter or click OK.

(ii) Using the Search Box: Type ‘excel’ in the ‘Type here to search’ box beside the Window icon ➪ press Enter or click on the Excel icon.

(iii) Right-click on Mouse: Right-click on the desktop or in the folder wants to create a workbook ➪ Select New ➪ Click on Microsoft Excel Worksheet.

B. TO CREATE A NEW EXCEL WORKBOOK BASED ON OPEN EXCEL FILE

If any workbook opens in the window,

(i) Using Excel shortcut: then press Ctrl+N to create a new workbook. This shortcut is the fastest way to start a new workbook if we’re not using a template.

(ii) Using Excel shortcut: Alt+F+N+L (sequentially press Alt, F, N, L).

(iii) Using the File Tab: Go to the File tab ➪ Click New ➪ Select Blank.

(02) NAMING AND SAVING AN EXCEL WORKBOOK

Saving a new Excel file enables us to later open it for additional updates or reference. Excel files are saved to a storage medium such as a hard drive, USB drive, flash drive, to the cloud on OneDrive, or other storage location.

We must first identify where the document is to be saved and assign a file name.

By default in all Office applications, documents are saved to the Documents folder, or Download folder or to OneDrive, depending on the settings specified during the program installation.

We can choose any of the following methods for saving an Excel file.

(i) Using the File Tab: Click the File tab to open the Backstage view ➪ Click Save As which will open the Save As dialog box.

(ii) Using Excel shortcut: Alt+F+A (Press sequentially, Alt, F, A) which will open the Save As dialog box.

(iii) Using Excel shortcut: Press F12 which will open the Save As dialog box.

(iv) Using Excel shortcut: Press Alt+F2 which will open the Save As dialog box.

(v) Using Excel Shortcut: To save a new Excel file, press Ctrl+S  which will open the Save As dialog box.

Finally, select a location or click Browse to navigate to the desired file storage location ➪ Type the file name ➪ Click Save or press Enter.

(03) SAVE AN EXCEL WORKBOOK IN OneDrive

OneDrive is a cloud-based application that allows us to store and sync our files so we can retrieve them anywhere and share them with other people if desired. OneDrive is also a great place to store backup files of important documents. OneDrive comes with recent versions of Windows and Microsoft Office. A free desktop app is also available for mobile devices.

Go to the File tab ➪ Click Save As ➪ Click OneDrive Sign up (or we can sign in to OneDrive if we have an account) ➪ Keep the file with the same name or Rename the file ➪ Click the New folder button in the Save As dialog box, rename the folder and enter into the folder by double-clicking ➪ Click the Save button or Press Enter.

(04) SAVING AN EXISTING EXCEL WORKBOOK IN DIFFERENT NAME

Often, we can rename an existing workbook to create a new workbook. In this case, we open the ‘Save As’ dialog Box any of the following methods:

➢ Using the File Tab: Click the File tab ➪ Click Save As which will open the Save As dialog box.

➢ Using Excel shortcut: Alt+F+A (Press sequentially, Alt, F, A) will open the Save As dialog box.

➢ Using Excel shortcut: Press F12 will open the Save As dialog box.

➢ Using Excel shortcut: Press Alt+F2 will open the Save As dialog box.

Then select a folder (can be same or different) or Browse to a new storage location ➪ Type the new file name ➪ Click Save. As a result, a new Excel workbook is created by saving an existing workbook with a new name.

(05) SAVING AN EXCEL WORKBOOK IN DIFFERENT EXCEL FILE EXTENSION

We can save an Excel file in a format other than .xlsx or .xls. The file formats are listed in the ‘Save as type:’ drop-down menu in the Save As dialog box. When we save a file to another file format (such as .csv format), some of the formatting, data, and features might be lost.

Note: The PDF and XPS formats are designed to deliver workbooks as electronic representations of the way they appear when printed. Both types of files can easily be sent by email to many recipients and can be made available on a webpage for downloading by anyone who wants them. However, the files are no longer Excel workbooks and cannot be opened or edited in Excel.

When we save an Excel workbook in PDF or XPS format, we can optimize the file size of the document for our purposes—the larger Standard file size is better for printing, whereas the Minimum file size is suitable for online publishing.

We can also configure the following options:

■ Specify the pages and worksheets to include in the PDF or XPS file.

■ Include or exclude non-printing elements such as properties.

■ Create an ISO-compliant PDF file.

(06) OPEN AN EXISTING EXCEL WORKBOOK / EXCEL FILE

To open an existing file, we first open the Excel application and then click a location such as This PC, or OneDrive and navigate to the drive or folder where the document is stored. Once the file is found, double-click the file to open it.

➢ Using Excel Shortcut: Press Ctrl+O which will open the ‘Recent’ documents list and we can select the file from the list on the right. Only the most recently used files are listed (approx. 25).

Each file in the recent documents list displays a pushpin icon on the right when we hover the mouse pointer over the file name. Click the pushpin icon, and that file becomes “pinned” to the list and will always appear at the top of the list. This handy feature ensures that important files always appear on the recent workbooks list — even if we haven’t opened the file recently.

Also, notice that we can right-click a workbook in the list and choose Remove from List. If we choose Clear Unpinned Workbooks to clear the list and start fresh.

➢ Using the File Tab: Go to File tab ➪ Click Open and choose a location (such as This PC or OneDrive) from the list on the left ➪ Navigate to the folder or drive and double-click the file to open it.

While we trying to open a workbook from the Open dialog box, Notice that the Open button is actually a drop-down list. Click the arrow, and we see these additional options:

■ Open: Opens the file normally.

■ Open Read-Only: Opens the selected file in read-only mode. When a file is opened in this mode, we can’t save changes with the original filename.

■ Open as Copy: Opens a copy of the selected file. If the file is named Advance Excel.xlsx, the workbook that opens is named Copy(1) Advance Excel.xlsx.

■ Open in Browser: Opens the file in the default web browser. If the file can’t be opened in a browser, this option is disabled.

■ Open in Protected View: Opens the file in a special mode that doesn’t allow editing. In this view, most of the Excel Ribbon commands are disabled.

■ Open and Repair: Attempts to open a file that may be damaged and recover information contained in it.

Note: In the Open dialog box, we can hold down the Ctrl key and select multiple workbooks. When we click Open, all the selected workbook files open.

Right-clicking a file name in the Open dialog box displays a shortcut menu with many extra Windows commands. For example, we can copy, delete, or rename the file; modify its properties; and so on.

(07) FILTERING EXCEL FILE NAMES

At the bottom of the Open dialog box is a button with a drop-down list. When the Open dialog box is displayed, this button shows All Excel Files (and a long list of file extensions). As a result, we see only standard Excel files.

If we want to open a file of a different type, click the arrow in the drop-down list and select the file type that we want to open. This changes the filtering and displays only files of the type that we specify.

We can also type a filter directly in the File Name box. For example, typing the following will display only files that have .xlsb extension (press Enter after typing the filter): *.xlsb.

(08) CHOOSING THE EXCEL FILE DISPLAY PREFERENCES

The Open dialog box can display our workbook filenames in several styles: as a list, with complete details, as icons, and so on. We can control the style by clicking the More Options icon (in the upper-right corner) and then selecting a display style from the drop-down list.

(09) RENAME AN EXCEL WORKBOOK / EXCEL FILE

➢ METHOD1: USING THE MOUSE RIGHT-CLICK ON THE FILE

(i) Right-click the file.

(ii) Choose Rename from the shortcut menu. The original filename becomes highlighted.

(iii) Type the new file name. Filenames cannot contain asterisk, slash, backslash, or question mark characters.

(iv) Press Enter after finishing typing.

➢ METHOD2: USING THE SECOND CLICK ON THE FILE NAME

First, click on the file name to select the file ➪ then click again to edit the file name.

Note: Do not double-click the file because double-clicking the file opens it.

➢ METHOD3: PRESSING THE F2 KEY ON THE FILE NAME

Click on the file name to select the file ➪ then press the F2 key to edit the file name.

➢ METHOD4: USING SAVE AS OPTION

⇒ Open the Excel file by double-clicking  ➪ Press F12 key which will open the Save As dialog box  ➪ File name already highlighted, rename it ➪ Click Save or press Enter.

⇒ Alternatively, Press Alt+F+A which will open Save As backstage view ➪ Choose file location ➪ Save As dialog box opens ➪ Rename ➪ Click Save or press Enter.

⇒ Alternatively, File ➪ Save As ➪ Choose file location ➪ Save As dialog box opens ➪ Rename ➪ Click Save or press Enter.

(10) CLOSING AN EXCEL WORKBOOK / EXCEL FILE

After work completion with a workbook, we can close it to free the memory that it uses. Other workbooks will remain open.

We can close a workbook by using any of the following methods:

■ Using Excel Shortcut: Press Ctrl+W which allows closing only the current workbook.

■ Using Excel Shortcut: Press Ctrl+F4 which allows closing all the workbooks.

■ Go to the File ➪ Close.

■ Click the Close button (the X) in the right corner of the window’s title bar.

■ Double-click the Excel icon on the left side of the workbook’s title bar.

If we’ve made any changes in the workbook since it was last saved, Excel asks whether we want to save the changes to the workbook before closing it.

(11) DELETE AN EXCEL WORKBOOK / EXCEL FILE

■ Press Delete Button: Select unwanted file just single click ➪ press Delete button. A confirmation message appears ➪ Click Yes. File deleted and moved to Recycle Bin.

If Press Shift+Delete, the file removes from the hard disk.

■ Using Right-Click: Right-click on the unwanted file ➪ Choose Delete from the shortcut menu. A confirmation message appears ➪ Click Yes. Excel file deleted.

■ Using Save As dialog box: Open Excel, but not the file we want to delete ➪ Press F12 which is open the Save As dialog box ➪ If necessary, navigate to the folder containing the file we want to delete ➪ Press Delete button.

(12) EDITING AN EXCEL WORKBOOK’S PROPERTIES

The workbook has a number of properties that are associated with it to make managing it easier. The properties include items that we indirectly change such as file size and last edit date.

The workbook properties also include items we directly change such as tags. Assigning tags (also called keywords) to the document properties makes it easier to organize and find documents For Example ‘Basic Excel’, ‘Master Data’ etc. We can assign more than one keyword to a document.

We can also add a category for classification and document management, e.g., ‘Tutorial1’, ‘Project data’, ‘Business data’, ‘Sales data’ etc.

➢ Method 1:

i) Go to the File tab. The Backstage view displays current properties on the right side of the window.

ii) At the bottom of the right pane, click the Show All Properties link to display additional properties.

iii) Click the Tags field and type ‘Basic Excel’.

iv) Click the Categories field and type ‘Tutorial1’.

v) Click the Company field and type ‘Advance Excel forum’.

➢ Method 2:

i) Above the Size field, click the Properties drop-down arrow, and then click Advanced Properties. The Properties dialog box opens.

ii) Click the Summary tab in the dialog box.

iii) Click OK to close the Properties dialog box.

iv) Press the Esc key to return to the worksheet.