Data Analysis Tips

1.    Introduction

Quantitative data analysis consists of the activities associated with organising data into information, in the form of tables and graphs, which can assist with understanding or deciding something.

The tips given here are especially for regular data analysis, where the reports generated should be consistent and comparable over a period of time.

The tips are most closely aligned with MS Excel, but are also generic.

2.    Report Title

2.1.  Use consistent titles for reports.  Suggested format:

“{Topic} {Time Period} as at {Date}”

For example:

Health Faculty Enrolments 2015 04 01 to 2015 06 30 as at 2015 07 02”

2.2.  State the information source in the title information (header, footer and / or title page).

2.3.  Further optional information, which can be stated in sub-titles, headers, footers or title pages, includes but is not limited to:

2.3.1.     report author;

2.3.2.     data selection criteria;

2.3.3.     target audience;

2.3.4.     limitations of usage of the data analysis; and

2.3.5.     copyright statements.

3.    Calculations

3.1.  Do all calculations using formulas – never manually (no results of manual calculations should be typed into data analyses).  Ensure that the formulas are the correct ones and are reading the correct source data.

3.2.  Estimate expected values, and / or use cross-checks of totals.  Investigate the causes of anomalies until all of them have been eliminated or explained.

4.    Cross-tabulations

4.1.  Cross-tabulations are known as “pivot tables” in MS Excel.

4.2.  Ensure that the rows and columns of each cross-tabulation are appropriate to represent what is being analysed.

4.3.  Ensure that all of the relevant rows and columns of the raw data are included in the source data for the cross-tabulation. 

5.    Graphs

Did you know?  In MS Excel, the F11 key instantaneously produces a basic graph, which can be customised using the Design function.

(Place the cursor anywhere in the table of the graph’s source information, which can be a pivot table or other table, and press F11.)

5.1.  Use the graph Design function (and Layout function if needed) to ensure that:

5.1.1.     the graph type is appropriate to the type of data being analysed;

5.1.2.     all of the relevant rows and columns of the graph’s source data are included in the graph;

5.1.3.     The graph has:

·       a title;

·       an appropriate scale; and

·       a legend or a data table.

5.2.  If you include data labels, these must also be done via the Design / Layout function – labels should never be typed in manually.

5.3 Exclude totals from the graph. (Including them would make for a very skew-looking graph, as everything else would be much smaller than the total.) If you want to include something that gives a sense of the overall picture, then include the average value rather than the total.