Excel OfficeExcel Tutorials for beginners, Intermediates and experts.
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… read more »
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… read more »
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… read more »
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…. read more »
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… read more »
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:… read more »
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… read more »
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… read more »
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… read more »
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… read more »