Create a Browse Tab in Excel

Spreadsheet tables are great for computers, but difficult for people - all that scrolling right and left to see all the data in a row. A Browse tab is a freely arranged screen of data taken from a single row with a scroll bar for navigation among rows. A high number of columns poses no constraint, since the full area of the screen is available. The steps below take about 15 minutes, regardless of the number of columns and yield a most pleasing result.

Steps

  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.
  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.

    .
  3. Create a Temp tab. Repeat step 2 to create a tab named Temp. You will drop this tab when done.
  4. Enter the Row number on the Browse tab. 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.
  5. View the Control Toolbox. On the Menu, click on Developers tab and select Insert.
  6. Create a scrollbar. Within the toolbox, click on the scrollbar
  7. Draw 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.
  8. Set scrollbar properties. 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.
  9. Start generating formulas on the Temp tab. Click on the Temp tab and enter Tab, Heading Row, Linked Cell, Column, Heading and Value in row 1, columns A - F.
  10. Set parameters for the formulas. 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.
  11. With a good dose of trust, enter the following formulas in row 2, columns E and F:
    • E2: =INDIRECT(CONCATENATE(A2,"!R",B2,"C",D2),FALSE)
    • F2: =CONCATENATE("=INDIRECT(CONCATENATE(""",A2,"!R"",",C2,",""C",D2,"""), FALSE)")

      Copy the formulas to fill out the table.
  12. 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.
  13. Copy the formulas to the Browse tab. Return to the Browse tab, click on cell A3 and on the Menu, click on Edit, Paste Special and Values.
  14. Install the formulas. 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.
  15. Admire your work. You should now see unformatted data from the spreadsheet.
  16. Try the scroll bar to check navigation. Increment and decrement the row number. Try both the minimum and maximum values of the scroll-bar and verify against the original data.
  17. Rearrange and reformat the headings and values for aesthetics. You can copy and paste to any row and column within the Browse tab. For our example, here's the final result.
  18. Drop the Temp tab. Right click on the Temp tab and select Delete. Click OK on the dialog box popping up for confirmation. Save your spreadsheet.

Tips

  • Create a graph, which changes as you scroll through the data.
  • Copy the formulas twice on the Browse tab, one in the viewing area with extensive formatting and the other "off screen" for graphs.
  • Apply conditional formatting to cells on the Browse tab for extra effect.
  • In Step 12 click Transpose to have headings above the data.
  • Compare two rows by creating two Scroll bars and Linked Cells, independently scrolling through the data.
  • If you'd like to display three adjacent rows, then just after step 4, select cell C1 and enter "=B1+1" and in cell D1 enter "=C1+1". Then later in the Temp tab, call cell $C$1 the Linked Cell and copy the formulas to the Browse tab. Repeat, calling $D$1 the Linked Cell. Cells C1 and D1 are covered up by the scroll bar, but they'll still work.
  • Use a much larger font to give those with poor eyesight access to your spreadsheet.
  • The use of cells A1 and B1 in step 4 are arbitrary – just be consistent in your steps.

Warnings

  • You will receive #REF if your Data Tab name contains spaces, so remove any spaces from the Data Tab name to correct.
  • The data on the Browse tab is entirely read only and will fail to work if you overwrite a formula.
  • You may be typecast as a "techie" or "nerd" or "genius" if you engage in formulas like this too much.

Related Articles

Retrieved from "https://kipkis.com/index.php?title=Create_a_Browse_Tab_in_Excel&oldid=273392"