Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: Workbook

List worksheet index numbers in Excel

To list the index numbers of sheets in an Excel workbook, you can enter the sheet names, then use a formula based on the SHEET and INDIRECT functions. The SHEET function then returns the current index for each sheet as listed. Formula =SHEET(INDIRECT(name&”!A1″)) Explanation In the example shown, the formula in C5 is: =SHEET(INDIRECT(B5&”!A1″)) How this formula works The INDIRECT…

List sheet names with formula in Excel

To list worksheets in an Excel workbook, you can use a 2-step approach: (1) define a named range called “sheetnames” with an old macro command and (2) use an INDEX formula to retrieve sheet names using the named range. Formula =GET.WORKBOOK(1)&T(NOW()) Note: because this formula relies on a macro command, you’ll need to save as a macro-enabled workbook if you want the formula…

How to reference named range different sheet in Excel

This tutorials shows how to reference a named range on another sheet. To achieve this, you can use the INDIRECT function with the required sheet syntax. Formula INDIRECT(“‘”&sheet&”‘!”&name) Explanation In the example shown, the formula in D6 is: =SUM(INDIRECT(“‘”&B6&”‘!”&C6)) Which returns the sum of the named range “data” on Sheet1. How this formula works The formula above evaluates something like…

How to get Excel workbook path only

If you want to get the workbook path (directory) only, you can do so with a formula that uses the LEFT and the FIND function. The result will look something like this: Drive:\path\to\file\ //Windows Drive:path:to:file: //Mac Formula =LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))-1) Explanation How the formula works The cell function is used to get the full file name and path: CELL(“filename”,A1) The result looks…

How to check worksheet name exists in Excel

To test if a worksheet name exists in a workbook, you can use a formula based on the ISREF and INDIRECT functions. Formula =ISREF(INDIRECT(“sheetname”&”!A1″)) Explanation In the example shown, the formula in C5 is: =ISREF(INDIRECT(B5&”!A1″)) How this formula works The ISREF function returns TRUE for a valid worksheet reference and FALSE is not. In this case, we want to find…

Get workbook name and path without sheet in Excel

If you want to get the current workbook’s full name and path without a sheet name, you can use a formula that employs several text functions to strip off the sheet name. The final result will be a text string that looks like this: path[workbook.xlsm] Formula =SUBSTITUTE( LEFT(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))-1),”[“,””) Explanation How the formula works The CELL function is used with “filename”…

How to get sheet name only in Excel

If you want to get the sheet name only (i.e. the sheet name without the file name or path) you can do so with rather long formula that uses the MID function along with the FIND function. The final result will look something like this: Sheet1 Formula =MID(CELL(“filename”,A1),FIND(“]”, CELL(“filename”,A1))+1,255) Explanation How the formula works The cell function is used to…

How to create dynamic worksheet reference in Excel

To create a formula with a dynamic sheet name you can use the INDIRECT function. Note: The point of this approach is it lets you to build a formula where the sheet name is a dynamic variable. So, for example, you could change a sheet name (perhaps with a drop down menu) and pull in information from different worksheet. Formula =INDIRECT(sheet_name&”!A1″)…

Create dynamic workbook reference to another workbook in Excel

To build a dynamic worksheet reference – a reference to another workbook that is created with a formula based on variables that may change – you can use a formula based on the INDIRECT function. See example below: Formula =INDIRECT(“‘[“&workbook&”]”&sheet&”‘!”&ref) Explanation In the example shown, the formula in E6 is: =INDIRECT(“‘[“&B6&”]”&C6&”‘!”&D6) How this formula works The gist of this formula…

How to retrieve workbook name only in Excel

If you want to get the workbook name only (i.e. the file name without path or sheet name) you can do so with a rather long formula that uses the MID function along with the FIND function. Formula =MID(CELL(“filename”,A1),FIND(“[“,CELL (“filename”,A1))+1,FIND(“]”,CELL(“filename”,A1)) -FIND(“[“,CELL(“filename”,A1))-1) Explanation How the formula works The cell function is used to get the full file name and path: CELL(“filename”,A1)…

How to get full workbook name and path in Excel

If you want to get the current workbook’s full path, name, and sheet with a formula, you can use the CELL function and a reference to any cell in the workbook. CELL will return the name in this format: path[workbook.xlsx]sheetname Note that you must save the worksheet in order to get the a result. Formula =CELL(“filename”,A1) Explanation A note about…

Make Workbook Read-only in Excel

This example shows you how to make your workbook read-only. Users can still save changes by creating a new copy of the workbook. For a better protection, protect a sheet. 1. Open a workbook. 2. On the File tab, click Save As. 3. Click Browse. 4. Click on the Tools button and click General Options. 5. In the Password to modify box,…

Mark Workbook as Final in Excel

To help prevent changes to a final version of an excel file, you Mark a workbook as final. Only use this feature to discourage editing. Users can still edit the workbook. Here are steps to Mark a workbook as final below: 1. Open a workbook. 2. On the File tab, click Info. 3. Click Protect Workbook, Mark as Final. 4. Click OK. Result.…

How to Protect and Unprotect Workbook in Excel

This example teaches you how to protect the workbook structure in Excel. If you protect the workbook structure, users cannot insert, delete, rename, move, copy, hide or unhide worksheets anymore. 1. Open a workbook. 2. On the Review tab, in the Changes group, click Protect Workbook. 3. Check Structure, enter a password and click OK. Note: The Windows option is not…

Save As in Excel Explained

Use the shortcut F12 to display the Save As dialog box in Excel. Press CTRL + s to save an existing workbook. It’s good practice to periodically save while you are working on your Excel file. Save As To display the Save As dialog box, execute the following steps. 1. On the File tab, click Save As. 2. Click Browse. Result: Note: instead of executing…

Excel Workbook Views

Excel offers three workbooks views, Normal, Page Layout and Page Break Preview. Normal At any time, you can switch back to Normal view. 1. On the View tab, in the Workbook Views group, click Normal. Result: Note: if you switch to another view and return to Normal view, Excel displays page breaks. Close and reopen the Excel file to hide these page breaks. To always…

AutoRecover file that was never saved in Excel

Excel periodically saves a copy of your Excel file. Learn how to recover a file that was never saved and how to recover a file that has been saved at least once. If Excel crashes, it displays the Document Recovery Pane, the first time you open Excel again. This is a quick way to recover the last autosaved file. File that…

View Multiple Workbooks at The Same Time in Excel

If you want to view multiple Excel workbooks at the same time, execute the following steps. 1. First, open two or more workbooks. 2. On the View tab, in the Window group, click Arrange All. 3. Select the desired arrange setting. For example, click Horizontal. 4. Click OK. 5. On the View tab, in the Window group, click View Side by Side…

Zoom Worksheet in Excel

Use the buttons on the View tab to zoom to a specific percentage and to zoom to a selection. In most cases, you can use the minus and plus symbols in the status bar to quickly zoom the document. 1. To quickly zoom the document, use the minus and plus symbols in the status bar. To zoom to a specific percentage, execute…

How to multiple users can work on a Workbooks at same time

If you share a workbook, you can work with other people on the same workbook at the same time. The workbook should be saved to a network location where other people can open it. You can keep track of the changes other people make and accept or reject those changes. To share a workbook, execute the following steps. 1. Open a workbook. 2.…

Steps To Password Protect a Workbook in Excel

Limit Others From Accessing Excel Workbook by Adding Password To Your File. Encrypt an Excel file with a password so that it requires a password to open it. 1. Open a workbook. 2. On the File tab, click Save As. 3. Click Browse. 4. Click on the Tools button and click General Options. 5. In the Password to open box, enter a password and click…

Creating and Opening an existing file in Excel Workbook

A workbook is another word for your Excel file as referred by Microsoft.. And, also a workbook comprises of several worksheets depending on the user. When you start Excel, click Blank workbook to create an Excel workbook from scratch. Open an Existing Workbook To open a workbook you’ve created in the past, execute the following steps. 1. On the File tab, click Open. 2. Recent…