Serious data users, stakeholders, will print pdf extracts in order to appreciate the metrics available, the format and the features.
However, some skills with Excel are needed to navigate pivot tables. To view the options within pivot tables, not too difficult. To construct pivot tables from a data set will take more skills. Most health data analysts use Excel spreadsheets, and frequently make simple pivot tables.
After several years of downloading pivot tables, there are several tricks to more effective pivot tables.
- Open a data set in Excel, hover over a cell and "Format a Table". This makes "Table Tools" available, and controls the Data Table features.
- Insert a hyperlink above the dataset to identify the source of the data, the website.
- Modify the data set to make it more informative. For example, avoid duplicate counties by concatenating county name and state.
- Use vlookup formula to insert useful metadata columns.
- Select "Table Tools" to "Summarize Data in a Pivot Table". The pivot table will automatically create a new tab.
- In the first tab, insert a "list of measures" to show users what is available. Column headers could be the year or type of measure.
- Watch a YouTube tutorial
It has been useful to provide basic views, so list of states, list of counties, list of facilities
- the column headers are measures
- When pulling data into the pivot table box, use "Field Settings" to format.
- Above the header names, insert the percentiles (Max, 80th, 60th, Median, 40th, 20th and Min).
- Select a column of data and use "Conditional Formatting" to highlight cells eg green = favorable top 80th ptl, or select preferred icons.
- Select "Pivot Tools" for many feature options. Use the filter icon to choose metadata, For example, choose "state".
- Mainly, learn by experimenting!