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…
Tag: Worksheets
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…
Embed Excel worksheet in Word document
Embed an Excel worksheet in a Word document and work with Excel in Word. You can also embed files in Excel. 1. Select the Excel data. 2. Right click, and then click Copy (or press CTRL + c). 3. Open a Word document. 4. In word, on the Home tab, in the Clipboard group, click Paste. 5. Click Paste Special. The…
Protect and Unprotect Worksheet in Excel
When you share an Excel file with other users, you may want to protect a worksheet to help prevent it from being changed. 1. Right click a worksheet tab. 2. Click Protect Sheet. 3. Enter a password. 4. Check the actions you allow the users of your worksheet to perform. 5. Click OK. Note: if you don’t check any action, users can only view…
How to Delete Cells, Row and Rows in Excel
To quickly delete a row in Excel, select a row and use the shortcut key CTRL – (minus sign). To quickly delete multiple rows, select multiple rows and use the same shortcut. Delete Row To delete a row in Excel, execute the following steps. 1. Select a row. 2. Right click, and then click Delete. Result: Note: instead of executing step 2,…
How to Insert Cells, Row and Rows in Excel
To quickly insert a row in Excel, select a row and use the shortcut keys CTRL SHIFT +. To quickly insert multiple rows, select multiple rows and use the same shortcut. Insert Row To insert a row in Excel, execute the following steps. 1. Select a row. 2. Right click, and then click Insert. Result: Note: instead of executing step 2, use the…
How to Break Worksheets into Pages in Excel
Insert a page break in Excel to specify where a new page will begin in the printed copy. To insert a horizontal page break, execute the following steps. 1. Select the first row of the new page. 2. On the Page Layout tab, in the Page Setup group, click Breaks. 3. Click Insert Page Break. Excel displays a solid line (manual page…
Group and Ungroup Worksheets in Excel
You can group worksheets in Excel if you want to edit multiple worksheets at the same time. Our workbook contains 3 similar worksheets (North, Mid and South) and a blank fourth worksheet. 1. To group worksheets, hold down CTRL and click the sheet tabs of the sheets you want to group. 2. Release CTRL. Now you can edit multiple worksheets at the same time. 3. For…
View Multiple Worksheets at Once in Excel
If you want to view multiple Excel worksheets at the same time, execute the following steps. 1. Open a workbook. 2. On the View tab, in the Window group, click New Window. Excel opens a new window containing another view of the document. 3. On the View tab, in the Window group, click Arrange All. 4. Select the desired arrange setting. For…
How to Consolidate(Merge) Records From Multiple Worksheets in Excel
You can use Excel’s Consolidate feature to consolidate your worksheets (located in one workbook or multiple workbooks) into one worksheet. Below you can find the workbooks of three districts. Before you start: if your worksheets are identical, it’s probably easier to create 3D-references (if you have one workbook) or External References (if you have multiple workbooks) to consolidate your data. As you can see, the worksheets are not…
Freeze and Unfreeze Panes in Excel
If you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet. Freeze Top Row To freeze the top row, execute the following steps. 1. On the View tab, in the Window group, click Freeze Panes. 2. Click Freeze Top…
Split Worksheet Window in Excel
To view multiple distant parts on worksheet at once, you will need to split your worksheet. To split your worksheet (window) into an upper and lower part (pane), execute the following steps. 1. First, select a cell in column A. 2. On the View tab, in the Window group, click Split. 3. Notice the two vertical scroll bars. For example, use the…
Things you find in Excel Status Bar
The quickest way to see the average, count, numerical count, minimum, maximum or sum of selected cells is by taking a look at the status bar. 1. Select a range of cells. 2. Look at the status bar at the bottom of your window to see the sum of these cells. 3. Right click the status bar to add the average,…
Lock Cells in a Worksheet Excel
Lock specific areas of a worksheet You can lock cells in Excel if you want to protect cells from being edited. In this example, we will lock cell A2. Before you start: by default, all cells are locked. However, locking cells has no effect until you protect the worksheet. So when you protect a worksheet, all your cells (=worksheet) will be locked. As a result,…
Prevent invalid data entering in specific cells
— Set criteria in your worksheet to accept specific data. Use data validation in Excel to make sure that users enter only values that meet a set criteria into a cell. Steps to navigate to Data Validation icon in Excel Data Tab → Data Tools group → Data Validation Data Validation Example Create Data Validation Rule Input Message Error Alert Data Validation Result Data Validation…
Select, Insert, Rename, Move, Delete Worksheets in Excel
How To: Select, Insert, Rename, Move, Delete A Worksheet From A Workbook A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets. Select a Worksheet in Excel When you open an Excel workbook, Excel automatically selects Sheet1 for you. The name of the worksheet appears on its sheet tab at the bottom of…