Excel Insights is an AI powered service that automatically identifies patterns in your data and provides you with charts you can insert into your workbook.

This article provides an introduction to the features of AI Insights, including the four insight classes:

• Trends.
• Rank.
• Majority.
• Outliers.

A very common requirement is to count only the unique occurrences of some values. But Excel doesn't have a direct way to count unique values.

In this post we show 5 different ways of counting unique values in Excel:

• SUMPRODUCT and COUNTIF
• SUM, FREQUENCY and MATCH array formula
• PivotTable
• SUM and COUNTIF
• COUNTUNIQUE user defined function

Excel can analyze data from many sources. But are you using the Data Model to make your life easier?

In this post you learn how to create a pivot table using two tables by using the Data Model feature in Excel.

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.