Analyze Data

A few years ago, Excel, which comes as part of a Microsoft 365 subscription, got the Ideas option. Now this option has been improved and is called Analyze Data. It enables, with the help of artificial intelligence, simple and fast data analysis to generate tables, pivot tables or charts according to user questions, which help user to visualize data in appropriate business reports.

Bullet Chart

Bullet is a type of Column or Bar chart where we display series one above the other, and later in their display settings we determine the way the series overlap. This is a simple chart that is suitable for comparing one larger and one smaller size, e.g. Budget and Actual sales values. How to create this type of chart you will find out in the following “recipe”…

Sales volume in relation to the goal

Sales volume in relation to a given target can be shown by a goal achievement chart. There is another way to do this, and that is to display real sales values ​​instead of moving the intersections of the x and y axes, using a Combo chart in which the target would be a horizontal line based on the data in the table. Also, by adding the Spin Button control, we will be able to change a goal easily, which leads to the dynamic chart.

Picturesque labels

When we make charts in most cases we’ll use text labels, numbers, dates, percentages etc. to display categories on the x axis. Sometimes descriptions are too long, so we have to work in different ways to display them in an adequate manner. Could we use something that describes them more closely such as a currency symbol, an envelope, a smiley face? In this “recipe” you will learn how to add different symbols to a table or chart.

Filtering charts

Filtering affects the appearance of a report by setting certain criteria. If we create a pivot chart, it will be formed based on the data from the pivot table. If we change something in it, the chart will also change. Is this possible to do in traditional Excel reports? Yes, if you have an Office 2021 or Office 365 subscription, with the new XLOOKUP function. This is a simple method by which you can get effective reports.