How to Create Powerful Quickbooks Online Reports with Google Sheets

‍

Are you curious about turning your financial data into reports with engaging visuals?

‍

When creating dashboards with your QuickBooks Online (QBO) data, it's not just about the numbers—it's about the narrative. In today's fiercely competitive financial world, presenting your clients or stakeholders with information clearly and compellingly is crucial to making a lasting impact.

‍

Fortunately, with QBO's robust data and Google Sheets' flexible visual tools, you're set to transform rows of otherwise dull data into impactful charts, insightful graphs, and interactive visualizations. 

‍

So, get ready to elevate your financial reports from lifeless to inspiring, ensuring your data doesn't just speak but resonates.

‍

‍

Integrating your Quickbooks Online Data with Google Sheets

‍

First things first, let's talk about integration.

‍

Suppose you are looking for ways to connect your live data from QuickBooks Online to Google Sheets for analysis. The paths you can take are varied—some are manual, some demand coding skills, and others are more snappy and intuitive. 

‍

For instance, LiveFlow is one of the few financial automation platforms that can connect 

‍

QuickBooks to Google Sheets immediately and provide real-time updates to your data in seconds.

‍

But how can we use LiveFlow to create impactful visual reports that inform and engage?

‍

Creating Reports in Google Sheets 

‍

As stated above, you can start creating your reports when you link your QBO data with LiveFlow. You can now create a mix of charts, graphs, and visualizations to reflect the health of your business from data that will update automatically. This means no more cumbersome manual exports or remembering which CSV is which. 

This article will show you how to create a simple dashboard in Google Sheets. However, LiveFlow has 100+ prebuilt financial templates customizable to your particular needs, so you won’t have to start from scratch.

‍

Here’s how to build one of our dashboards: the Profit & Loss Template:

‍

‍

‍

LiveFlow Profit & Loss Template

‍

Here are a few things to note before we get started with building visualizations:

‍

• Create a Spreadsheet: You already have a sheet populated with your QBO report data, thanks to LiveFlow. Now, create a new sheet dedicated to your visual dashboard. This is where all your graphs and charts will come to life. 

‍

• Create an Inputs Sheet: This sheet will likely become the core of your dashboard, where you’ll summarize the information from your P&L sheet (or any other report you import from QBO), serving as the data source for your dashboard’s charts and graphs. Here are some tips to optimize it: 

‍

• Consolidate your data: Import crucial metrics like revenue, expenses, and net income from your P&L sheet.

‍

• Have Calculation Columns: Insert columns for essential calculations feeding your charts, like growth percentages or year-over-year comparisons.

‍

• Use Dynamic References: Employ Google Sheets functions for dynamic updates, ensuring your dashboard reflects the latest P&L changes. If advanced Sheets formulas aren’t your thing, you can use ChatGPT to help.

‍

• Have a Structured Layout: Arrange your data logically, grouping related metrics and labeling clearly for easy navigation.

‍

• Use Documentation (Optional): Optionally, add brief notes explaining formulas or calculations, enhancing clarity and future ease of use.

‍

Creating Scorecard Charts

‍

Scorecard charts are excellent for displaying key metrics, as in this example, Total Income, Net Income, and Expenses on the top of the dashboard. Here's how you can create them:

‍

• Select Your Data: Click on the cell that contains the data you want to display in your scorecard chart.

‍

• Insert Chart: Go to Insert > Chart. This opens the 'Chart editor' on the right.

‍

• Choose Chart Type: In the 'Chart editor,' click on the 'Setup' tab, and from the 'Chart type' dropdown, select 'Scorecard Chart'.

‍

• Choose Key Value and Baseline Value: The Key Value will be the one that will appear biggest on the Card, in this case, the Total Income for Sep 2023, and the Baseline Value will be the one against which it will be compared (August 2023 in the example).
• Customize: Switch to the 'Customize' tab in the 'Chart editor' to fine-tune the aesthetics of your scorecard. Here, you can modify the font, background color, and more:

‍

• Set the 'Chart Title' to something descriptive, such as 'Total Income'.

‍

• Configure the 'Baseline Value.' You can opt for an 'Absolute change' or a 'Percentage change,' depending on how you visualize the comparison.

‍

‍

‍

‍

Creating a Combo Chart

Combo charts are great for showing multiple data series with different visualization types. This would be the chart with the blue bars and a dotted orange line on the right-hand side of the dashboard. Here's how to create a combo chart:

‍

• Prepare Your Data:

‍

• Organize Time Data (X-axis): Arrange the dates horizontally from left to right, representing a time series (e.g., 12 months). This row will serve as your X-axis in the chart.

‍

• Input Series Names: In the first column (the Y-axis), input the data series names for clarity. In your case, these are 'Total Income' and 'Net Income Margin'. Leave the cell corresponding to the X-axis (dates) empty, which will help distinguish between the series names and the time data.

‍

• Arrange Data Series: To the right of each series name, horizontally input the corresponding data for each month. This means you'll have one column for 'Total Income' and another for 'Net Income Margin,' with each row in these columns representing a month's data.

‍

• Highlight the Data: Click and drag to highlight the data you want to include in your chart, which would be the data we just prepared.

‍

• Insert Chart: Go to Insert > Chart. This will insert a chart and open the 'Chart editor.'

‍

• Choose Chart Type: In the 'Chart editor,' click on the 'Setup' tab, and from the 'Chart type' dropdown, select 'Combo chart'. Here, make sure that under the ‘X-axis,’ you have the cells where your time data is, and under ‘Series,’ one is for ‘Total Income,’ and one is for ‘Net Income Margin.’

‍

• Customize Series: In the 'Chart type' dropdown in the 'Series' section, you can choose different types of visuals (like line, area, or column) for each data series.

‍

• Customize Axes: Click on the series and select which axis you want it to be associated with (right or left).

‍

‍

‍

Creating Doughnut Charts

‍

Doughnut charts are perfect for showing data in a proportional, easy-to-understand manner, such as the Breakdown of the Cost of Goods Sold and the Breakdown of Expenses in our dashboard. Here's how to create them:

‍

• Prepare Your Data: Organize your data into two columns. The first column should contain the categories and the Breakdown of COGS or Expenses, and the second should have the values.

‍

• Highlight the Data: Click and drag to highlight the data you want to include in your chart.

‍

• Insert Chart: Go to Insert > Chart. This will insert a chart and open the 'Chart editor.'

‍

• Choose Chart Type: In the 'Chart editor,' click on the 'Setup' tab, and from the 'Chart type' dropdown, select 'Doughnut chart'.

‍

• Customize: Further customize your chart's appearance, colors, and labels using the 'Customize' tab options.

‍

‍

Ready to Take the Next Step?

‍

Now that you've seen how to create a powerful dashboard in Google Sheets with your QuickBooks Online data, you are ready to wow your clients and stakeholders. So what are you going to do with this information?

‍

Reports turn complex data into actionable insights, so help your clients or business make informed decisions with effective reports.

‍

At LiveFlow, our platform helps you effortlessly connect your QuickBooks Online data to Google Sheets using 100+ customizable templates that save you time and resources.

‍

That's not all, so reach out today and schedule a quick demo with our team to hear more about how LiveFlow can save you time and help you with your FP&A struggles. 

‍

‍