Excel Data Analysis is a powerful tool that is used to visualize and gain insights of records in a spreadsheet.
How to apply a number filter and a text filter to only display records that meet certain criteria. Follow the steps below: 1. Click any single cell inside a data set. 2. On the Data tab, in the Sort & Filter group, click Filter. Arrows in the column headers appear. Number Filter To apply a number filter, execute the …
A bar chart is the horizontal version of a column chart. Use a bar chart if you have large text labels. To create a bar chart, execute the following steps. 1. Select the range A1:B6. 2. On the Insert tab, in the Charts group, click the Column symbol. 3. Click Clustered Bar. Result:
Pie charts are used to display the contribution of each value (slice) to a total (pie). Pie charts always use one data series. To create a pie chart of the 2017 data series, execute the following steps. 1. Select the range A1:D2. 2. On the Insert tab, in the Charts group, click the Pie symbol. 3. Click Pie. …
Line charts are used to display trends over time. Use a line chart if you have text labels, dates or a few numeric labels on the horizontal axis. Use a scatter chart (XY chart) to show scientific XY data. To create a line chart, execute the following steps. 1. Select the range A1:D7. 2. On the Insert tab, in …
Color Scales in Excel make it very easy to visualize values in a range of cells. The shade of the color represents the value in the cell. To add a color scale, execute the following steps. 1. Select a range. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Color Scales and click …
The FREQUENCY function in Excel calculates how often values occur within the ranges you specify in a bin table. You can also use the COUNTIFS function to create a frequency distribution. 1. First, enter the bin numbers (upper levels) in the range C4:C8. 2. Select the range D4:D9 (extra cell), enter the FREQUENCY function shown below and finish by pressing …
Column charts are used to compare values across categories by using vertical bars. To create a column chart, execute the following steps. 1. Select the range A1:A7, hold down CTRL, and select the range C1:D7. 2. On the Insert tab, in the Charts group, click the Column symbol. 3. Click Clustered Column. Result: Note: only …
This chapter teaches you how to create a Pareto Chart in Excel. The Pareto principle states that, for many events, roughly 80% of the effects come from 20% of the causes. In this example, we will see that roughly 80% of the complaints come from 20% of the complaint types. To create a Pareto chart in Excel 2016, execute …
Learn all about how to Create a Chart, Change Chart Type, Switch Row/Column , Legend Position and Data Labels A simple chart helps to understand voluminous data in Excel than a sheet full of numbers. Create a Chart To create a line chart, execute the following steps. 1. Select the range A1:D7. 2. On the Insert tab, in the Charts group, …
Icon Sets in Excel make it very easy to visualize values in a range of cells. Each icon represents a range of values. To add an icon set, execute the following steps. 1. Select a range. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Icon Sets and click a subtype. Result: Explanation: …
An area chart is a line chart with the areas below the lines filled with colors. Use a stacked area chart to display the contribution of each value to a total over time. To create an area chart, execute the following steps. 1. Select the range A1:D7. 2. On the Insert tab, in the Charts group, click …
A higher rule always wins when working with multiple conditional formatting rules in Excel . This example illustrates two different results. 1. The value 95 is higher than 80 but is also the highest value (Top 1). The formats (yellow fill vs green fill and yellow text color vs green text color) conflict. A higher rule always wins. …
Data bars in Excel make it very easy to visualize values in a range of cells. A longer bar represents a higher value. To add data bars, execute the following steps. 1. Select a range. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Data Bars and click a subtype. Result: Explanation: by …
Use a scatter chart (XY chart) to show scientific XY data. Scatter charts are often used to find out if there’s a relationship between variable X and Y. Straight Lines To create a scatter chart, execute the following steps. 1. Select the range A1:D22. 2. On the Insert tab, in the Charts group, click the Scatter symbol. …
This example teaches you how to insert checkbox to create a checklist in Excel. First, turn on the Developer tab. Next, you can create a checklist. You can also insert a check mark symbol. To create this checklist, execute the following steps. 1. On the Developer tab, in the Controls group, click Insert. 2. Click Check Box in the Form Controls section. 3. …
So far we have only seen charts on the same worksheet as the source data (embedded charts). However, you can also move a chart to a separate sheet that only contains a chart (chart sheet). To move a chart to a chart sheet, execute the following steps. 1. Select the chart. 2. On the Design tab, in …
Thermometer chart shows you how much of a goal has been achieved. This example teaches you how to create a thermometer chart in Excel. Working with sales record in the table below; To create a thermometer chart, execute the following steps. 1. Select cell B16. Note: adjacent cells should be empty. 2. On the Insert tab, in the …
Error bars are graphical representations of the variability of data and used on graphs to indicate the error or uncertainty in a reported measurement. Excel error bars are used to display either the standard deviation, standard error, confidence intervals or the minimum and maximum values in a ranged dataset. To visualise this information, Error Bars …
Select Data Source Switch Row/Column Add, Edit, Remove and Move A row or column of numbers that are plotted in a chart is called a data series. You can plot one or more data series in a chart. To create a column chart, execute the following steps. 1. Select the range A1:D7. 2. On the Insert …
Axis Type Axis Titles Axis Scale Most chart types have two axes: a horizontal axis (or x-axis) and a vertical axis (or y-axis). This example teaches you how to change the axis type, add axis titles and how to change the scale of the vertical axis. To create a column chart, execute the following steps. 1. Select the range A1:B7. …
The essence of adding a trendline to chart is to show visual data trends. This example teaches you how to add a trendline to a chart in Excel. 1. Select the chart. 2. Click the + button on the right side of the chart, click the arrow next to Trendline and then click More Options. The Format Trendline pane appears. …
Combination Chart: A combination chart is a chart that combines two or more chart types in a single chart. To create a combination chart, execute the following steps. 1. Select the range A1:C13. 2. On the Insert tab, in the Charts group, click the Combo symbol. 3. Click Create Custom Combo Chart. The Insert Chart dialog box …
Sparklines in Excel are graphs that fit in one cell and give you information about the data. Insert Sparklines To insert sparklines, execute the following steps. 1. Select the cells where you want the sparklines to appear. In this example, we select the range G2:G4. 2. On the Insert tab, in the Sparklines group, click Line. 3. …
This chapter illustrates how create a Gauge Chart. A gauge chart (or speedometer chart) combines a Doughnut chart and a Pie chart in a single chart. This is what the spreadsheet looks like. To create a gauge chart, execute the following steps. 1. Select the range H2:I6. Note: the Donut series has 4 data points and the Pie …