Jane's Blog Tips, News, and Thoughts from the Jane Team

PivotTables: Get More Out of Jane Reports

October 05, 2021

Hello, to all our data lovers out there!

Every time we’ve hosted a webinar on Jane Reports, the response has been… well, enthusiastic, to say the least. And we get it because we love data too. 🤓

So to embrace our shared love of making numbers make sense, we’re offering an in-depth tutorial on (drum roll please 🥁)…

PivotTables!

Buckle up, number crunchers, because we’ve got some serious learning to do:


What is a PivotTable & Why Should I Use It?

☝️ How could we not?

A PivotTable is a tool that you can use in your spreadsheet software (like Excel) to help you better understand your data. Generally, when working with data in a spreadsheet, there’s a lot of sorting, filtering, calculating, and recording that has to happen. But, what if we told you there was a better way?

Enter, PivotTables! PivotTables allow you to select a data set, specify which calculations you’d like to perform, and immediately see the results side-by-side so that you can make quick, high-level comparisons. They’re flexible, exploratory, and can extract meaningful insights from your data to help you make business decisions. Plus, they’re sure to impress your family and friends at parties. 🥳 😉

☝️Behold, the beauty of a PivotTable.


How Can I Create a PivotTable?

Before diving into this section, we suggest pulling up a spreadsheet and choosing one of your favourite Jane Reports to work with. Maybe grab yourself a snack or a drink while you’re at it. 🍫 We’ll wait.

All settled? Great! First, you’ll want to Export the Jane Report that you are working with using the three dots in the filter bar. Excel and CSV will both work here.

☝️ We’re exporting the Appointment Report from our Demo Clinic.

A quick disclaimer here: The screenshots below were taken on Excel. Depending on the spreadsheet software you’re using, things might look a little different. We know Numbers for Mac doesn’t have PivotTables, but Google Sheets does! If you’re working with another program, try a google search like, ‘PivotTable with (insert software name + version here)’.

Okay, back to the action. You’ll want to open the report you just exported from Jane in your spreadsheet software. Now, highlight all of the data using ctrl+A (Windows) or cmd+A (Mac) to select all.

With all your data selected, you can click on the Insert tab at the top of the screen and choose PivotTable from the list.

The Create PivotTable menu will open and ask you to choose two things:

  • The range of data you want to use. You’ve already highlighted the entire table, so you’re good to go here.
    • If you did want to select a smaller amount of data, you could do so where it says Table/Range. A1:X31 just means you’ve selected all data between the top-left corner and bottom-right corner of the spreadsheet.
  • Where to place the PivotTable. We usually go with a New Worksheet to keep things tidy.

☝️ If you’re curious what the dollar sign means, you can read about it here. Or, continue to live in blissful ignorance. All good either way.

When you click OK, you’ll be brought to a new sheet. You’ll see a list of PivotTable Fields on the right-hand side, and this is where we get to start working the magic. 🧙


How Do I Use a PivotTable with Jane Reports?

Anatomy of a PivotTable

Before we start, here’s a quick breakdown of the PivotTable Fields:

  • Field Name will show all the data fields that were covered in your Jane Report (think column headings and filters). We’ll refer to any of the items in this box as Fields.
  • Filters will help narrow down the information being displayed.
  • Columns will display the information in a Field running up and down the spreadsheet.
  • Rows will display the information in a Field running left to right across the spreadsheet.
  • Values will be where you put the main Field you want to see a numeric value for.

Let’s Try it Together

It takes a bit of practice to get the hang of PivotTables. We always suggest starting with a question you want to answer. Once you know the question, you’ll have a much better idea of how to start filling in the PivotTable.

Our first question is:

“How many appointments were booked online?”

Here’s how we set up our PivotTable to answer it:

1) In Jane, find the Appointment Report and, following the instructions above, export it, open it in Excel, select all data, and open a PivotTable.

2) In the Field Name box, find the Field you are trying to assess. In this case, it’s how many appointments were booked online, so we’ll choose booked_online.

3) Pull booked_online down into the Rows box. Doing this will create labels on your spreadsheet telling you what you’ll be seeing in the rows.

  • Here, the True row refers to any data that falls under the Field you’ve selected (in this case, any appointment booked online). The (blank) row will show anything that doesn’t fall under that Field (in this case, appointments booked on the admin side of Jane).

4) Now, we want Excel to actually count all those data points for us, so pull booked_online down into the Values box as well.

👇 Voila! The answer to our question is that 18 appointments were booked online.

Let’s expand on that a little bit:

“How many appointments were booked online for each staff member?”

5) Find the staff_member_name Field and drag it into the Columns box. Now, each staff member’s name will appear as a column heading. The PivotTable is still calculating the number of appointments that fall under ‘booked_online’, but now it will separate out that number for each staff member!

Now that we know what we’re doing (Right? Kind of?), let’s try another question with the same data:

“How many of each treatment occurred at each of my Locations last month?”

1) The main part of the question is looking for # of treatments that occurred, so we need to decide which Field will tell us that. If we refer back to our Appointment Report in Jane, we can see that this is being shown under the heading ‘State’, which tells us if an appointment was Booked, Arrived, or No Showed. Now that we know that, we’ll drag the state Field into the Rows box.

2) Next, we know we want to see this further broken down by the type of treatment, so we’ll drag the treatment_name Field into the Columns box.

If you feel like this creates too many Columns, you can try swapping the Fields in the Rows and Columns boxes to see if that makes it easier to look at! 🔃

3) Since we’re trying to find the number of treatments that happened, we’ll drag the state Field into the Value box as well. Doing this will count the number of Booked, Arrived, and No-showed appointments for each treatment.

4) Finally, we want to further break this down by Location. We’ll find the location_name Field and drag it down to the Rows or Columns box, depending on how we’d like to view the information.

Ta-da! ☝️ Now if we want to know the number of Arrived 90-Minute Massages at the Village, we can look at the table and see that it’s 9.

🔥 Hot Tip: Do a quick check of the original data to confirm the numbers you’re seeing in the PivotTable are correct. If they’re not, you may need to try adding a different Field into the Value box.

Last but not least, you can Filter the results if you want to focus on something specific. Using the little drop-down arrows by the Row/Column labels, you can select/ deselect categories and sort the fields into ascending/descending order.


And that’s all, folks! You’re all ready to go face the world of data, PivotTables in hand. See you out there. 👋


For more on working with Jane Reports, check out these guides and webinars:

Blog Posts