Extracting Bill Data to Excel

To extract the bills you have to excel, you will use the filter tool to select the data you want to extract.

You will then click on the Page/XLSX button to export the contents you are viewing to Excel.

When you open the file, you will see three different areas:

1. Summary

2. Summary_Full

3. Line Number and Invoice Number

Summary

The summary provides a summary overview of each invoice broken.

Summary_Full

This provides a detailed breakout of each line item on the invoice which can be used for line item reporting and analysis.

Line Number and Invoice Number

MakersHub will also show a breakout of each bill. In reviewing the Summary tab, you can identify the corresponding sheet that contains the bill extract based on which row it is. In the example below, line 2 of the summary tab starts with L2.

In this sheet, you will see a version of the bill from MakersHub in Excel format.

Pivot Tables

Highlight the data range and select Insert>Pivot Table.

From here you can select the fields you want to analyze. For the purposes of the example, we will want to track the

Pivot Table Settings: Organizing Dates

If you are new to pivot tables, you may notice that, at times, it is difficult to sort based on invoice date. In cases where Excel does not correctly read the dates, you can highlight the column containing the dates and select Data>Text to Columns.

Select Delimited and Next.

Then Tab and Next.

Then Date, select MDY or your preferred date format from the dropdown, and click Finish.

Now when you create a pivot table, Excel will recognize and be able to sort the dates.

Pivot Table Suggestions REMOVE

Should you want to run an analysis that references cells within the pivot table, I suggest:

• Removing Subtotals and Grand Total Rows

  • Click on the pivot table then from the ribbon bar select Design>Subtotals>Do Not Show Subtotals

  • Click on the pivot table and then from the ribbon bar select Design>Grand Totals> Off for Rows and Columns

• Classic Pivot Table View

  • Navigate to Pivot Table Analyze>Options>Display and select Classic PivotTable layout

• Repeat Members

  • Navigate to Pivot Table Analyze>Field Settings>Layout and Print and select Repeat Item Labels