Excel Commands for Large Spreadsheets

Help with Statistics

AMA has advice for using statistics with large data files, and a checklist. There are excellent articles on statistical analysis of health data in the British Medical Journal, though it requires a subscription, which you may find at a university or hospital library.

Help with Excel

This is a simple introduction to some Excel commands which are helpful with the large spreadsheets of medical data on the site. The commands here work in Excel 2010 and Excel 2003.

Contents:
Basics
Filter
Sort
Undo
Calculate
Pivot (Summaries)
Advanced Formulas
Help with Statistics
Why Excel

Spreadsheets on This Site:

Doctors: Lengths of Appointments - xlsx
Doctors: Experience with each Procedure  -  xlsx
Hospital Financial Statements  -  xlsx
Hospital Readmissions  -  xls
Accountable Care Organizations  -  xls

When you open an excel spreadsheet from the web, it may ask if you want to "enable editing." You'll need to say Yes to make any changes in your copy, or to "find all" entries of a certain type, or filter, or sort.

If the spreadsheet was slow to download, click File/Save As a couple times with new names, so you can get back to the original version without downloading again.

If you need extra help, many people who work in bookkeeping or finance are good with Excel spreadsheets, or you can search the web.

Basics. Most of you already know these:

• Find Records by pressing Ctrl and F at the same time, and type what you want into the search box. If you know which column it's in, Highlight that column by clicking the letter atop the column, before  you press Ctrl-F, and the search will be much faster.
• Rows start with 1, and Columns start with A. You can move around the spreadsheet with the arrow keys, the page down and page up keys, and the sliders on the right and bottom sides of the window, if they appear.
• Click a Cell and a little window just above the letters at the top of the columns shows you the number or formula in that cell, or Type something new to replace what's there.

Select Records (Filter), such as a State, or Specialty:

1. If small ▼Triangles already appear near the top of each column, skip to step 5. If you don't see triangles, start with step 2.
2. Click the letter at the top of the column showing State, or whichever column you want to select on.
3. Click Data. The Data menu is found alongside File, Home, Insert...
4. Click Filter (you may have to first click Sort-and-Filter). After you click Filter, a small ▼Triangle will appear near the top of the selected column(s).
5. Left-click the triangle, to see a little menu where you can de-select All, then Click the state(s) or specialty(ies) you want. Click OK, and only your state(s) or specialties will appear. You can get the others back, by clicking the little triangle again and selecting All. The little menu under the triangle has its own little slider to go up and down the list; All is at the top of the list.
6. The triangle may hide some of the column heading. You can remove the triangle by again clicking Data, then Filter.
7. To select a range of records, such as costs over $1,000, click Number Filters in the little menu under the triangle in the costs column, then click Greater Than, then fill in $1,000 ($ and comma are optional), then click OK. Number Filters can even give you Between 1,000-2,000, or Top 1 to 500, or Above or Below Average. Sometimes they're called Text Filters, with similar choices.
8. You can filter several columns, like female Pain doctors in Virginia and DC.

Sort in Any Order You Want:

• Choose cells to sort: Highlight any area by left-clicking the upper left corner, then shift-left-clicking the lower right corner, or Drag your mouse from one corner to another while holding the left key , or Click the letter atop any column to highlight a column, or Click the number left of any row to highlight a row
• Click Data at the top of the screen, then click Sort. Answer the questions to expand to all columns or rows. Then you will see a window of sorting choices.
• To keep headings at the top, be sure the box is clicked for "My data has headers"
• Normally it moves Rows up and down to put them in order. Click Options to move Columns right and left into order
• In the Sort By box, click the ▼ triangle, and choose a column (or row) to sort by
• If you want subsorting (such as by state and then by revenue within state), you may need to click Add Level and fill in the second item to sort by
• Click OK. A file over 100,000 records may take a few seconds to a minute to sort, depending on your computer's speed.

Undo a Sort or anything: press Ctrl and Z at the same time. You can do this repeatedly to back up to previous versions, sometimes as far as the last time you saved the file

Save Frequently, with new names, so you can go back to previous versions if you need to.

Calculate an Average or Sum: Suppose you want Average of Column T

• Insert a new row to hold the average, by right-clicking the Number to the left of a row, like row 2: On the menu which appears, left-click Insert
• Click a cell in the new row, probably in column T
• Type into that cell (don't forget the equals):  =Average(T3:T50000)

• Include the full range you want, which depends how big your spreadsheet is

• Press enter, the formula should disappear, and a number will appear.
• =Average( ) gives the average of numbers, including zeros, but ignoring blanks. It includes rows you hid with Filter. To do a lot of work on one state, you may want to delete all the others, so your averages or sums are just that state. Save Frequently.
• You can also use =Sum( ) or =Median( ) or many other functions (click Help)

Calculate a Ratio, or Difference Suppose you want the Ratio or Difference of Columns K and L:

• Insert a new column by right-clicking the Letter atop a column: On the menu which appears, left-click Insert
• Click a cell in the new column, on the row where you want the first ratio, such as row 2
• Type into that cell the ratio you want from that row (don't forget the equals): =K2/L2
• Press enter, the formula should disappear, and a number will appear.
• Then put your cursor over the lower right corner of the cell. Your cursor becomes a +; double-click it and blank cells below it will fill all the way to the bottom. Click some of those to see how the formula appears as it goes down the page.
• Formulas can use / * -  + ( ) and many other functions described in Help.
• If cells have too many decimal places, right-click the cell, column or row: On the menu which appears left-click Format cells: In the window of format choices which appear, click Number and the number of decimal places you want, then OK

Summary Tables (Pivot) can count or sum records in categories. For example if you want average revenue by state:

• Select state and revenue columns by left-clicking the letter of the left column, then shift-left-clicking the letter of the right column (every column in the range must have label in first row)
• Excel 2010: Click Insert (or in Excel 2003: click Data) then click PivotTable. Answer any questions, and it will open a new page.
• 2010: On the right side of the screen, drag a category, like State, down to Row Labels or Column Labels. (2003: click a category, then use ▼triangle to add the category to rows, columns, or data.)
• Move any variable, maybe State again, to Values (called Data in 2003). Be sure it appears there as "Count of.." Notice the summary table on the left side of the screen now shows the count of rows for each state
• 2010: Drag a numeric variable, like revenue, to Values. It will develop a little arrow, Left-click the little arrow to choose "Value Field Settings.." and then Sum, Count, Average, Max, Min, etc. (2003: Add a numeric variable, like revenue, to Data. See where it appears on the table as Count of. Right-click it: On the menu which appears, left-click Pivot Table Field and then Sum, Count, Average, Max, Min, etc.)
• The summary table on the left can give you the count of rows in each state, and the average and/or sum of as many variables as you wish, for each state.
• You could also do rows for each type of hospital or other category in your data
• 2010: If the menu of variables on the right side of the screen disappears, click anywhere in the table on the left side of the screen, and the right side will reappear. (2003: A Pivot Table bar floats on the page, with a button on the right end to hide or show the Field List.)
• 2010: If you want some states, but not others, move your cursor to the upper right corner where the state variable is listed. When you point at a variable there, a ▼triangle will appear to the right of the variable name. Click the triangle, then de-select as many states as you wish.

Advanced Formulas

• ^ means exponent so =2^3 means 2 cubed or 8, and 2^.1 means tenth root of 2
• You'll get warnings in cells where you divide by zero. You can avoid them by typing conditions: =IF(condition , result if condition is true , result if false): =IF(sum(L2)<>0,K2/L2," ")
• Excel often objects if you compare text to a number, but the sum of text is zero, which can be compared to a number as shown above. <> means "not equal to"
• When you copy a cell down a column, cell references generally change: K2/L2, K3/L3, K4/L4, etc. If some should not change (suppose L2 is a national average, and you want all the Ks as a ratio to that national average), then put in the first cell K2/L$2, and it will fill down as K3/L$2, K4'L$4, etc. The $ does not affect the value, and does not turn it into cash (which is done with Format Cell). The $ just says not to change 2. Similarly $L2 means to keep L, and $L$2 means to keep both L and 2.
• Besides clicking the bottom right of a cell, you can copy it by using Edit, or highlighting an area and using Ctrl-R to copy to the right, or Ctrl-D to copy down
  
  Why Excel
  This site uses Excel, since Google Sheets are limited to 2 million cells and Open Office is limited to 65,536 rows. The files here have hundreds of thousands of doctors with 20-60 cells for each, so they need the size of Excel or QuattroPro. These instructions cover Excel, since more people have it. You can do the same work in QuattroPro.