This article outlines 18 best practices for working with data in Google Sheets.

Use these practices to make your work more efficient, reduce errors, make your work easier to follow and understand, and add value to the workflow process.

Best practices:

• Organize your data.
• Keep a backup copy of your data.
• Document the steps you take.
• Go with wide-format data tables.
• Use good, consistent names.
• Use data validation for data entry.
• Even better, use Google Forms for data entry.
• One cell = one piece of information.
• Distinguish columns you add.
• Don't use formatting to convey data.
• Add an index column for sorting & referencing.
• Format the header row.
• Freeze the header row.
• Turn formulas into static values after use.
• Keep copies of your formulas.
• Create named ranges for your datasets.
• Avoid merged cells.
• Tell the story of one row.

Step Charts are useful for showing values that don't change steadily from one point to the next, but that instead are constant for a period of time, then jump to the next level, and are constant for another period of time.

For example, step charts are good for showing how things like postal rates or interest rates change over time.

Step charts are not native to Excel, but it's not hard to build a step chart, with a bit of data tweaking and some smoke-and-mirrors formatting.

There are two ways to build step charts:

• One geared towards timelines (line charts with X-axis dates) which uses duplicate points.
• The other towards XY Scatter charts which uses horizontal and vertical error bars.

This article describes problems that can occur when a formula reference includes its own worksheet name.

Specifically:

• The formula is longer and more difficult to read than necessary.
• If the formula is copied to another worksheet, then Excel may fail to update references correctly.

To avoid these problems, get into the practice of always removing sheet references to the current worksheet.

[An additional related problem we've seen is that the Sort feature may not work correctly when a formula being sorted includes a reference to its own worksheet name.]

This video shows a case study where data quality issues needed to be dealt with before the results could be trusted.

The essence of the message is "...look at our data and investigate the quality of it before we start building models and complex formulas and queries ...".

This tutorial shows you the exact steps required to combine multiple sheets into one single table using Power Query.

When using Power Query to combine data from all the Tables in a workbook, you will likely face an issue where Excel double counts your data. The tutorial also shows you how to avoid this issue.

A sample workbook is available to download, so you can follow each of the steps yourself.

If you work with any type of data, then you know that rarely does it come perfectly in the format you need. Power Query (also called Get & Transform in Excel 2016) is the solution to this problem.

Power Query is an amazing ETL tool (Extract, Transform, and Load). If you're not using it then you're missing out on the best new feature in Excel.

This article shows you, via an example, how to import and clean complex and messy data using Power Query.

There are situations where several criteria are required to add up the right numbers. That's straightforward if the numbers are found in a single column using SUMIF. Yet when you find your data in several columns, more complex formulas are needed.

This article shows you how to use SUMPRODUCT with multiple criteria to add numbers from multiple columns.

A list of the top 10 worst nightmares for a financial modeller, and how to solve them:

• Cleaning up someone else's models.
• Circular references appearing where you did not expect them.
• The financial model crashes, and you don't have a backup.
• Worksheet compatibility issues.
• A balance sheet that does not balance.
• Changes in accounting standards or applicable fiscal regimes which, in turn, make your life miserable.
• The first time you are asked to build a cash-flow waterfall when the only waterfall you knew was Niagara Falls.
• Clients who have Excel phobia.
• When you are given a financial model in Portuguese and your level of Portuguese is limited to the lyrics of Amar Pelos Dois from Salvador Sobral.
• Being called a data cruncher.