How to Make a Table Lens

A table lens display provides a way to view potential correlations between several quantitative variables simultaneously.

Step 1. Enter the labels for the data set in column A. See attached Excel spreadsheet.

How to Make a Table Lens 01

Step 2. Enter each set of quantitative values. In the example attached, values are entered in columns B through G for discharges, total patient days, average daily census (ADC), average length of stay, mean charge per discharge, case-mix adjusted charge per discharge.

Step 3. Sort the rows by the first column of values in descending order. Highlight cells A1 through G36 and click on “Sort and Filter”. Select “Custom Sort” and select Discharges in the “Sort By” drop down box, “Values” in the “Sort On” drop down box, and “Smallest to Largest” in the “Order” drop down box. Click “OK”.

How to Make a Table Lens 02

How to Make a Table Lens 03

How to Make a Table Lens 04

Step 4. Create a horizontal bar graph. Highlight cells B1 through B36. Click on the Insert tab and click on the down arrow under the Column icon. Click on “All Chart Types” on the bottom of the pop up box. Select the Horizontal Bar Chart first option “Clustered Bar” and press “OK”.

Step 5. Format chart. Click on legend and press DEL key.

Step 6. Click on Vertical Axis then right click and click on “Select Data”. In the right hand side of the pop up box under “Horizontal Axis Labels” click on “Edit”. Highlight cells A2 through A36 and click “OK”. Then click “OK” again.

How to Make a Table Lens 05

How to Make a Table Lens 06

How to Make a Table Lens 07

Step 7. Click on the Y-axis to select it. Right-click and select “Format Axis” then select “Axis Options”. Click on “Axis Labels” and change to “High”. Then click on Minor Tick Mark Type and change to “None”. Next select “Line Color” on the left hand side and select “No Line”. Click on “Close”.

How to Make a Table Lens 08

How to Make a Table Lens 09

Step 8. Adjust the font: Click on “Vertical Axis Labels”, right click and select “Font”. Change font to 6. Click on “OK”. Click on the ‘Horizontal Axis”, right click and select “Font”. Change font to 6. Click on “OK”.

How to Make a Table Lens 10

How to Make a Table Lens 11

Step 9: Click on the vertical grid lines and press DEL key.

How to Make a Table Lens 12

Step 10. Click the chart and drag the handles to the desired size.

Step 11. Click on the Chart Title to select it. Right click and change the font. In this example we use 9. Click on “OK”.

How to Make a Table Lens 13

Step 12. Click on the chart and right click. Select “Format Chart Area”. Under “Border Color” select “No Line”. Click on “Close”.

How to Make a Table Lens 14

How to Make a Table Lens 15

Step 13. Click on the chart you just created and press the “CTRL” key and “D” key together to create a duplicate of the chart. Position this new chart next to the first chart you created.

Step 14. Click on the Vertical Axis and press the “DEL” Key.

Step 15. Click on the new chart and right click. Click on “Select Data” on the left hand side of the pop up box under “Legend Entries”. Click on “Edit”. In the “Series Name” section, highlight cell E1 – Mean LOS and under “Series Values” highlight cells E2 through E36. Click “OK”.

How to Make a Table Lens 16

How to Make a Table Lens 17

How to Make a Table Lens 18-19

Step 16. Click on the chart you just created and press the “CTRL” key and “D” key together to create a duplicate of the chart. Position this new chart next to the chart you just created.

Step 17. Click on the new chart and right click. Click on “Select Data” on the left hand side of the pop up box under “Legend Entries”. Click on “Edit”. In the “Series Name” section, highlight cell F1 – Mean Charge per Discharge and under “Series Values” highlight cells F2 through F36. Click “OK”.

How to Make a Table Lens 20

Step 18. Click on the Horizontal Axis and right click. Select “Format Axis” then select “Axis Options”. Under “Minimum” click on “Fixed” and type 0. Under “Maximum” click on “Fixed” and type 60,000. Under “Major Unit” click on “Auto”. Then select “Number” on the left hand side of the pop up box. Change decimal places to 0. Click on “Close”.

How to Make a Table Lens 21

How to Make a Table Lens 22

How to Make a Table Lens 23

Step 19. Click on the chart you just created and press the “CTRL” key and “D” key together to create a duplicate of the chart. Position this new chart next to the chart you just created.

Step 20. Click on the new chart and right click. Click on “Select Data” on the left hand side of the pop up box. Under “Legend Entries” click on “Edit”. In the “Series Name” section, highlight cell G1 Case-Mix Adjusted Mean Charge per Discharge and under “Series Values” highlight cells G2 through G36. Click “OK”.

How to Make a Table Lens 24

Share
This entry was posted in Editing Charts & Graphs, Excel Tips & Tricks. Bookmark the permalink.

2 Responses to How to Make a Table Lens

  1. Greetings! Very useful advice in this particular article!

    It’s the little changes that make the greatest changes. Thanks a lot for sharing!

  2. rodrigo says:

    Thank a lot for your article

Leave a Reply

Your email address will not be published. Required fields are marked *


× 4 = four