How to Create a Browse Tab in Excel
Open the spreadsheet containing your data., Change a tab name to Browse., Create a Temp tab., Enter the Row number on the Browse tab., View the Control Toolbox., Create a scrollbar., Draw the scrollbar., Set scrollbar properties., Start generating...
Step-by-Step Guide
-
Step 1: Open the spreadsheet containing your data.
The original data must be a table, with headings at the top and rows of data.
Note the Tab name Row number containing Headings Row numbers containing data First and last column numbers (with Column A as 1, column B as 2, etc.) An estimate will do.
We'll use a Nations tab with health information.
Go ahead and peek ahead to step 18 to see the final result. -
Step 2: Change a tab name to Browse.
Right click on an unused tab at the bottom.
Click Rename and enter Browse.
If you need to create a blank sheet, click first on Insert and Worksheet and OK. . , Repeat step 2 to create a tab named Temp.
You will drop this tab when done. , In the Browse tab, select cell A1 and enter Row.
In cell B1, enter the number of the first row of real data of the table you're browsing. , On the Menu, click on Developers tab and select Insert. , Within the toolbox, click on the scrollbar , The cursor changes shape.
Left click on the upper left corner of the rectangle you're about to draw.
Keeping the left button down, drag the cursor to the bottom right corner to draw a rectangle about two rows high and four columns wide. , Right click on the new scrollbar and click on Format Control.
For Cell Link, enter B1.
In Min Value enter the row number of the first row of your table; In Max Value enter the last row.
Close out the properties box.
Click on the drafting box in the Control Toolbox and close the Toolbox.
The scrollbar should now work. , Click on the Temp tab and enter Tab, Heading Row, Linked Cell, Column, Heading and Value in row 1, columns A
- F. , Enter the name of the data tab in cell A2.
Enter the heading row number in cell B2 (generally this is row 1).
Enter $B$1 in cell C2 and the column numbers beginning in cell D2.
Fill out the rows of this table for all the column numbers of interest.
Columns A, B and C are usually identical, but has been designed for some flexibility. ,,, Return to the Browse tab, click on cell A3 and on the Menu, click on Edit, Paste Special and Values. , The headings should now appear, but the formulas remain.
Select the formula cells in column B and click on Edit and Replace.
Now this really strange, but replace = with = (no joke).
Click the Replace All button. , You should now see unformatted data from the spreadsheet. , Increment and decrement the row number.
Try both the minimum and maximum values of the scroll-bar and verify against the original data. , You can copy and paste to any row and column within the Browse tab.
For our example, here's the final result. , Right click on the Temp tab and select Delete.
Click OK on the dialog box popping up for confirmation.
Save your spreadsheet. -
Step 3: Create a Temp tab.
-
Step 4: Enter the Row number on the Browse tab.
-
Step 5: View the Control Toolbox.
-
Step 6: Create a scrollbar.
-
Step 7: Draw the scrollbar.
-
Step 8: Set scrollbar properties.
-
Step 9: Start generating formulas on the Temp tab.
-
Step 10: Set parameters for the formulas.
-
Step 11: With a good dose of trust
-
Step 12: enter the following formulas in row 2
-
Step 13: columns E and F: E2: =INDIRECT(CONCATENATE(A2
-
Step 14: FALSE) F2: =CONCATENATE("=INDIRECT(CONCATENATE("""
-
Step 15: FALSE)") Copy the formulas to fill out the table.
-
Step 16: Column headings should now appear in column E and a strange formula in column F. Select all the values in columns E and F and type CTRL-C to get ready to copy them onto the Browse tab.
-
Step 17: Copy the formulas to the Browse tab.
-
Step 18: Install the formulas.
-
Step 19: Admire your work.
-
Step 20: Try the scroll bar to check navigation.
-
Step 21: Rearrange and reformat the headings and values for aesthetics.
-
Step 22: Drop the Temp tab.
Detailed Guide
The original data must be a table, with headings at the top and rows of data.
Note the Tab name Row number containing Headings Row numbers containing data First and last column numbers (with Column A as 1, column B as 2, etc.) An estimate will do.
We'll use a Nations tab with health information.
Go ahead and peek ahead to step 18 to see the final result.
Right click on an unused tab at the bottom.
Click Rename and enter Browse.
If you need to create a blank sheet, click first on Insert and Worksheet and OK. . , Repeat step 2 to create a tab named Temp.
You will drop this tab when done. , In the Browse tab, select cell A1 and enter Row.
In cell B1, enter the number of the first row of real data of the table you're browsing. , On the Menu, click on Developers tab and select Insert. , Within the toolbox, click on the scrollbar , The cursor changes shape.
Left click on the upper left corner of the rectangle you're about to draw.
Keeping the left button down, drag the cursor to the bottom right corner to draw a rectangle about two rows high and four columns wide. , Right click on the new scrollbar and click on Format Control.
For Cell Link, enter B1.
In Min Value enter the row number of the first row of your table; In Max Value enter the last row.
Close out the properties box.
Click on the drafting box in the Control Toolbox and close the Toolbox.
The scrollbar should now work. , Click on the Temp tab and enter Tab, Heading Row, Linked Cell, Column, Heading and Value in row 1, columns A
- F. , Enter the name of the data tab in cell A2.
Enter the heading row number in cell B2 (generally this is row 1).
Enter $B$1 in cell C2 and the column numbers beginning in cell D2.
Fill out the rows of this table for all the column numbers of interest.
Columns A, B and C are usually identical, but has been designed for some flexibility. ,,, Return to the Browse tab, click on cell A3 and on the Menu, click on Edit, Paste Special and Values. , The headings should now appear, but the formulas remain.
Select the formula cells in column B and click on Edit and Replace.
Now this really strange, but replace = with = (no joke).
Click the Replace All button. , You should now see unformatted data from the spreadsheet. , Increment and decrement the row number.
Try both the minimum and maximum values of the scroll-bar and verify against the original data. , You can copy and paste to any row and column within the Browse tab.
For our example, here's the final result. , Right click on the Temp tab and select Delete.
Click OK on the dialog box popping up for confirmation.
Save your spreadsheet.
About the Author
Kathryn Fisher
Dedicated to helping readers learn new skills in home improvement and beyond.
Rate This Guide
How helpful was this guide? Click to rate: