How do I summarise appointment data in Excel?

There are many ways in which you can analyse your BizDiary appointment data by exporting it to use with Excel (or other spreadsheet programs).

In this example the aim is to view appointment hours over time, to gain a view of how clinic hours fluctuate over the course of the year, and how the business is growing.

Step 1: Export the data

First we need to get the data.  For our example we want to look at information since the start of 2022 up to the current date.  We also want to exclude cancelled and no-show appointments.

1. Log in to BizDiary.
2. Go to Appointments > Search Appointments.
3. In the From date we enter 1/1/2022.  In the To date select the current date.
4. In Status select everything except No Show and Cancelled.  Hold down the Ctrl key to select multiple.
5. Click the [Search] button to run the search.
6. Click the Export link (at the bottom), select the fields (we will just keep them all for now), and then click the [Export] button.
7. Choose to Save the file.  Save in your My Documents folder (or similar) and name it appropriately.  E.g. "appointments.csv".

Step 2: Getting month / year and hours

Now we can start using Excel to analyse the data.  Firstly we will remove some of the data we don't really need.  This could also be done in the previous step, by selecting only the required fields.  However if you want to do a variety of analyses on the same set of data it is useful to keep all fields and then remove as needed.

1. Delete unnecessary columns.  Select columns by clicking on the column header (A, B, C, ...) and then pressing [Ctrl] + [-].
   For our example we are keeping just Date (column A), Duration (column B) and Service (column C).
2. Add labels - Year, Month, and Hours in columns D, E and F.
3. Create a formula in cell D2 (under Year) like this...
   =YEAR(A2)
   And copy it into the other cells in the Year column
4. Create a formula in cell E2 (under Month) like this...
   =MONTH(A2)
   And copy the cell to all the other cells in the Month column.
5. Create a formula in cell F2 (under Hours) like this...
   =B2/60
   And copy the cell to all the other cells in the Hours column.

Step 3: Analysing the data

Excel has a cool feature called a PivotTable which is great for summarising data.

1. Select the entire data range in columns D, E, and F (including headings).
2. From the menu, select Data > PivotTable and PivotChart Report.
3. Select Excel list or database as the choice for data, and PivotTable as the kind of report, and then click [Finish].
4. Now you choose how the fields are summarised.  From the field list...
   Drag Year to where it says Drop Column Fields here.
   Drag Month to where it says Drop Row Fields here.
   Drag Hours to where it says Drop Data Items here.

You now have a table showing the total number of hours summed by month for each year.

Step 4: Adding a graph

When the data is selected you will see that the PivotTable toolbox shows up.  You can turn the data into a graph by clicking on the PivotChart Wizard button.

Other options

You might also want to look at data on a weekly basis.  For this you would use the WEEKNUM function instead of MONTH.

You can also find various other options in the PivotTables, such as averages etc.  This article is intended just as a starting point to show how quickly you can analyse the data of interest.

see also...

• How do I export data?