Automate Data Analysis with Excel’s Ideas Feature
- Analyzing transactions to rank data and identify items(s) that are significantly larger or smaller than the rest of the population;
- Performing trend analysis to highlight trends over data based on the passage of time;
- Identifying significant outliers in data points, including potentially erroneous or fraudulent transactions; and
- Calling attention to situations where a substantial portion of the total value is attributable to a single factor.
If you run Excel through an Office 365 subscription, you can access Ideas from the Home tab of the Ribbon. Note, however, that you must have an active internet connection to use this feature.
Simpler Conditional Formulas with IFS, MAXIFS, and MINIFS
XLOOKUP – A Better and Easier Alternative to VLOOKUP
- XLOOKUP defaults to an exact match, whereas VLOOKUP and HLOOKUP default to an approximate match.
- With XLOOKUP, you do not have to specify a column index number as you do with VLOOKUP or a row index number as you do with HLOOKUP.
- The arrangement of columns and rows does not matter with XLOOKUP. This is because the function can look to the left or right when using it as an alternative to VLOOKUP. Likewise, it can look above or below when using it as an alternative to HLOOKUP.
- XLOOKUP allows you to specify what happens if your lookup value isn’t found, without having to include an IFERROR function.
Illustrating Dynamic Arrays
Analyze the Quality of Your Data with Power Query
A recent enhancement to Power Query is its’ ability to automatically analyze your data for quality issues such as completeness and accuracy. With this feature, you can quickly identify potential problem areas such as erroneous data, missing records, and even duplicated records. To take advantage of this feature, on the View tab of the Power Query Editor, check the Column quality, Column distribution, and Column profile boxes, as shown in Figure 3. As you can see, Power Query generates a “quality snapshot” of each of the columns of data in the query; further, clicking on any column in the query exposes a more detailed view of the data, including statistics for that column and a value distribution graph. This feature was made available beginning in February 2020 for Office 365 subscribers.