GOOGLE SHEETS DATA ANALYSIS | Turn Raw Spreadsheet Records Into Useful Business Insights — Clean Data, Calculate Sales, and Build a Summary Report

A spreadsheet can contain hundreds or thousands of records

and still tell you very little about how your business is performing.

You might record orders every day, list products and prices, track payments, or maintain a register of customers. But when the information is inconsistent, incomplete, or difficult to summarize, answering simple questions becomes unnecessarily hard.

Which products generate the most sales? How much revenue came from completed orders? Which product categories perform best? Are there duplicate records or missing values that could distort your conclusions?

You do not necessarily need a specialist business-intelligence platform to begin answering these questions. Google Sheets provides tools for cleaning records, calculating values, summarizing data, and presenting findings.

In this practical guide, you will build a small sales-analysis workflow using Google Sheets. You will clean a sample dataset, calculate completed sales, summarize results, create a chart, and check whether your conclusions are supported by the underlying records.

The same method can be adapted to inventory, website performance, customer enquiries, project tracking, environmental surveys, and other datasets.

1. Understand the Difference Between Data and Information

Data consists of recorded observations. Information is what those observations help you understand.

Consider a sales register containing the following fields:

• Order date
• Order number
• Product
• Product category
• Quantity
• Unit price
• Order status

These fields are data. By themselves, they do not necessarily explain how the business is performing.

When you calculate the value of completed orders, compare sales between categories, or identify unusually high and low values, you begin turning the records into information.

Analytics goes a step further by using data to answer questions, investigate patterns, compare alternatives, and support decisions.

A useful workflow is:

COLLECT → CLEAN → CALCULATE → SUMMARIZE → VISUALIZE → INTERPRET

Each stage matters. A beautiful chart built from incorrect records can be more misleading than a plain spreadsheet.

2. Create a Working Sales Register

Open Google Sheets in a web browser on your computer and create a blank spreadsheet.

Rename the file Sales Analysis Practice.

Rename the first sheet Sales_Data.

In row 1, enter the following column headers:

Column	Header
A	Date
B	Order ID
C	Product
D	Category
E	Units
F	Unit Price (₦)
G	Status
H	Completed Sales (₦)

The headings identify what each column contains. Use one header row, keep each column focused on one type of information, and avoid merged cells in the main data table.

Now enter this sample data in cells A2:G11. The figures are invented for practice and do not represent actual business results.

Date	Order ID	Product	Category	Units	Unit Price (₦)	Status
2026-10-01	ORD001	Notebook	Stationery	10	1500	Completed
2026-10-01	ORD002	Pen	Stationery	20	300	Completed
2026-10-02	ORD003	USB Drive	Accessories	5	4500	Completed
2026-10-02	ORD004	Notebook	Stationery	8	1500	Pending
2026-10-03	ORD005	Keyboard	Accessories	3	12000	Completed
2026-10-03	ORD006	Pen	Stationery	15	300	Cancelled
2026-10-04	ORD007	USB Drive	Accessories	4	4500	Completed
2026-10-05	ORD008	Keyboard	Accessories	2	12000	Pending
2026-10-06	ORD009	Notebook	Stationery	6	1500	Completed
2026-10-06	ORD010	Pen	Stationery	12	300	Completed

This small dataset is enough to practise the main workflow. In a real business, you would normally work with your own properly maintained records.

3. Inspect the Data Before Calculating Anything

Do not begin by creating charts. First, inspect the records.

Check these questions:

• Does every column have a meaningful header?
• Are order IDs present and unique?
• Are product names written consistently?
• Are quantities valid numbers?
• Are prices stored as numbers rather than text?
• Are dates recognized as dates?
• Are status values consistent?
• Are there missing or duplicated records?

For example, Completed, completed, and Complete may represent the same intended status, but a spreadsheet may treat them as different text values in some calculations.

Likewise, Notebook and Notebook can look identical to a person while containing different text because of a trailing space.

These inconsistencies can cause grouping and counting errors.

Use Google Sheets cleanup tools

On a computer, open the spreadsheet and inspect the Data menu.

Depending on the current interface, the data-cleaning tools include options such as Data cleanup → Cleanup suggestions, Remove duplicates, and Trim whitespace. Google also documents Column stats, which can help you inspect distributions and unusual values. <Cite refs={["turn624712search2","turn624712search15"]}/>

Use these tools carefully:

1. Review the suggested issue.
2. Inspect the affected cells.
3. Decide whether the suggested correction is appropriate.
4. Apply the change only when you understand its effect.
5. Recheck the records afterward.

Do not automatically remove every duplicate-looking row. Two orders can legitimately contain the same product, quantity, and price. An actual duplicate order record is different from two valid orders with similar details.

For this example, the order ID is a useful field to inspect for duplication. In a real system, you would also check the source of the records before deleting anything.

4. Calculate the Value of Completed Sales

The Completed Sales (₦) column will calculate the value of orders whose status is Completed. Pending and cancelled orders will contribute zero to this particular metric.

This is a deliberate definition for the exercise. A real business may need additional rules for refunds, partial fulfilment, taxes, discounts, payment confirmation, and accounting recognition.

Click cell H2 and enter:

=IF(G2="Completed",E2*F2,0)

Press Enter.

The formula checks the order status. If it is Completed, it multiplies the quantity by the unit price. Otherwise, it returns zero.

For the first order:

• Units: 10
• Unit price: ₦1,500
• Status: Completed
• Completed sales value: ₦15,000

To apply the formula to the other records, select H2 and drag its small fill handle down to H11. Alternatively, copy H2 and paste it into H3:H11.

Check a few rows manually to confirm that the formula behaves as intended.

You can format the column as currency using the spreadsheet's number-formatting controls. Choose the appropriate currency and decimal display for your reporting needs.

Calculate total completed sales

Click an empty cell below the dataset or in a separate summary area and enter:

=SUM(H2:H11)

The result should be ₦111,000 for the sample data.

That total includes completed orders only. It excludes the pending and cancelled orders under the definition used in this exercise.

Count completed orders

Use:

=COUNTIF(G2:G11,"Completed")

The result should be 7.

This counts rows whose status is exactly Completed. It does not count the pending or cancelled rows.

Calculate average completed order value

Use:

=AVERAGEIF(G2:G11,"Completed",H2:H11)

The result should be approximately ₦15,857.14.

This is the average completed sales value per completed order row in the sample. It is not profit, cash collected, or necessarily the average value of a unique customer.

The distinction matters: a useful metric needs a clear definition.

5. Use Formulas to Answer Specific Questions

Google Sheets supports many functions for analyzing data. Start with a small set that answers practical questions.

Find the highest completed order value

Use:

=MAX(H2:H11)

The result is ₦36,000.

In the sample data, this comes from the completed keyboard order containing three units at ₦12,000 each.

Find the lowest positive completed order value

A standard MIN formula would return zero because the pending and cancelled rows have zero completed sales value.

To exclude zeros, use:

=MIN(FILTER(H2:H11,H2:H11>0))

The result is ₦3,600.

This is the smallest positive completed order value in the sample.

Calculate completed sales for one category

To calculate completed sales for Stationery, use:

=SUMIF(D2:D11,"Stationery",H2:H11)

The result is ₦33,600.

For Accessories:

=SUMIF(D2:D11,"Accessories",H2:H11)

The result is ₦77,400.

These figures make it possible to compare the two categories using the same measure.

Official Google Sheets documentation describes functions such as FILTER, SORTN, and QUERY for working with and transforming data. <Cite refs={["turn624712search3"]}/>

6. Build a Pivot Table to Summarize the Records

Formulas are useful for individual calculations. A pivot table makes it easier to summarize a dataset by different dimensions without writing a separate formula for every category.

Google Sheets supports pivot tables for grouping records, summarizing values, sorting results, and filtering data. <Cite refs={["turn624712search0","turn624712search17"]}/>

For this tutorial, create a pivot table showing completed sales by category.

Create the pivot table

1. Select a cell inside your dataset.
2. Select the full source range, including the headers and the calculated column. For this example, use A1:H11.
3. Open Insert → Pivot table.
4. Choose to create the pivot table in a new sheet.
5. Select Create.

The exact interface may change, but the essential requirement is that your source range includes the field names in its first row.

Configure the pivot table

In the pivot table editor, add these fields:

• Rows: Category
• Values: Completed Sales (₦)
• Summarize by: SUM

If needed, change the value field's summarization to SUM rather than COUNT.

Your pivot table should produce these results:

Category	Completed sales
Accessories	₦77,400
Stationery	₦33,600
Grand total	₦111,000

The grand total should agree with the total calculated earlier. If it does not, inspect the source range, formula column, value summarization, and category labels.

That agreement is a basic but important validation check.

Try a second analysis

Create another pivot table, or adjust the existing one, to investigate completed sales by product.

Use:

• Rows: Product
• Values: Completed Sales (₦)
• Summarize by: SUM

This lets you compare the contribution of notebooks, pens, USB drives, and keyboards.

A pivot table is especially useful when the source data becomes larger and you need to change the way it is grouped without rewriting the original records.

7. Turn the Summary Into a Chart

A chart can help readers recognize differences between categories quickly.

For the sample data, a column or bar chart is suitable because you are comparing the completed sales values of two categories.

To create one:

1. Select the category summary and its sales values in the pivot table.
2. Open Insert → Chart.
3. Inspect the chart type selected by Sheets.
4. In the Chart editor, open Setup and choose an appropriate chart type if necessary.
5. Give the chart a descriptive title, such as Completed Sales by Category.
6. Check that the category labels and currency values are correct.

Google's documentation explains how to insert charts and change their type, data range, and presentation. <Cite refs={["turn624712search5"]}/>

A chart should make the underlying comparison easier to understand. It should not replace the source table or hide the definition of the metric.

For example, a chart titled “Business Performance” would be too vague. “Completed Sales by Category” tells the reader what the values actually represent.

8. Interpret the Results Without Overclaiming

The sample dataset gives us several findings:

• Total completed sales are ₦111,000.
• Seven of the ten order rows have a Completed status.
• Accessories account for ₦77,400 of completed sales.
• Stationery accounts for ₦33,600 of completed sales.
• Accessories have the larger completed sales value in this particular sample.

These findings describe the data. They do not automatically explain why the results occurred.

For example, the larger accessories total does not prove that accessories are more profitable. The dataset contains no cost of goods, overhead, shipping costs, returns, or other expenses.

Nor does it prove that accessories will continue to outperform stationery. Ten sample orders are insufficient to establish a reliable long-term trend.

To investigate profitability, you would need cost information and a clearly defined profit calculation. To assess demand over time, you would need more complete records across an appropriate period.

Good analytics distinguishes what the data shows from what you suspect explains it.

9. Make the Analysis Repeatable

A useful spreadsheet should be easy to update when new records arrive.

Keep the original records in a clearly named data sheet. Keep summaries and charts in separate sheets where practical.

Use consistent field names and status values. Avoid manually typing totals that could be calculated from the source records.

When new orders are added, check whether your formulas and pivot table source ranges include the new rows. A pivot table may need its source range adjusted if new records fall outside the original range.

For recurring reports, document:

• The source of the data.
• The reporting period.
• The meaning of each metric.
• Any records excluded from the analysis.
• The formulas or summary rules used.
• The date the report was updated.

These details make the analysis easier to audit and reproduce.

If you later need more advanced processing, Google Sheets can also be extended with Apps Script, which allows scripts to read and modify spreadsheet data and automate selected workflows. <Cite refs={["turn624712search14","turn624712search16"]}/>

You do not need to begin with automation, however. First establish a correct and understandable manual workflow.

10. Common Problems and How to Fix Them

My formula produces an error

Check that the formula uses the correct cell references and quotation marks. Some spreadsheet locales use semicolons instead of commas as function argument separators. If the formula is rejected, check the spreadsheet's locale and adapt the separators.

My sales total is too high

Check for duplicate order records, incorrect quantities, wrong prices, or orders that should not be included in the completed-sales metric. Verify a few records manually before changing the formula.

My pivot table counts rows instead of adding money

Open the pivot table editor and check the summarization for the sales-value field. Select SUM when you want to add monetary values.

My chart includes the wrong fields

Open the Chart editor and check the data range, labels, and series. Confirm that you selected the category names and corresponding sales totals rather than unrelated columns.

My category totals do not agree with the overall total

Check whether some rows have missing categories, inconsistent category names, or formulas that were not copied correctly. Confirm that the pivot table includes the full intended dataset.

My analysis changes when I add more records

Check formula ranges, pivot table source ranges, and chart data ranges. Ensure that new records are included in all relevant calculations.

My numbers look correct but the conclusion seems wrong

Review the metric definition. Sales value is not the same as profit, cash received, customer demand, or business growth. The correct calculation depends on the question you are trying to answer.

11. Protect the Quality of Your Data

Data analysis is only as reliable as the records and assumptions behind it.

Before sharing a report, confirm that you have permission to use the data. Remove unnecessary personal information, restrict access to sensitive records, and avoid publishing customer or employee details without an appropriate basis.

Keep a copy of the original records before performing major cleanup operations. Document significant corrections, especially when the dataset supports financial or operational decisions.

For consequential business decisions, use the spreadsheet as evidence to investigate—not as an excuse to skip judgment, verification, or professional accounting and statistical advice where needed.

Final Takeaway

Google Sheets can help you move from scattered records to useful information without beginning with a complex analytics system.

The essential skill is not merely knowing how to insert a chart or write a formula. It is learning to ask a clear question, check the quality of the records, choose a suitable calculation, validate the result, and interpret the evidence honestly.

Use this workflow whenever you need to make sense of structured information:

CHECK THE DATA → CALCULATE THE METRIC → SUMMARIZE THE RESULTS → VISUALIZE THE PATTERN → VERIFY THE CONCLUSION

When you apply that process consistently, a spreadsheet becomes more than a place to store numbers. It becomes a practical tool for understanding operations, identifying questions worth investigating, and making better-informed decisions.

Official resources

• Google Sheets: Create and use pivot tables — https://support.google.com/docs/answer/1272900
• Google Sheets: Clean up data and inspect column statistics — https://support.google.com/docs/answer/10098582
• Google Sheets: Add and edit charts — https://support.google.com/docs/answer/63824
• Google Sheets: Analyze data and use spreadsheet functions — https://support.google.com/docs/answer/9330962
• Google Apps Script: Extend Google Sheets — https://developers.google.com/apps-script/guides/sheets

W3 NOTES BLOG | The Internet Money Feed 

Image: SK TECH Premium