Excel Data Analysis is a powerful tool that is used to visualize and gain insights of records in a spreadsheet.
To sum a total in multiple tables, you can use the SUM function and structured references to refer to the columns to sum. See example below: Formula =SUM(Table1[column],Table2[column]) Note: the total row must be enabled. If you disable a total row, the formula will return the #REF error. Explanation In the example shown, the formula in I6 is: …
To conditional sum numeric data in an Excel table, you can use SUMIFS with structured references to for both sum and criteria ranges. Formula =SUMIFS(Table[sum_col],Table[crit_col],criteria) Explanation In the example shown, the formula in I5 is: =SUMIFS(Table1[Total],Table1[Item],H5) Where Table1 is an Excel Table with the data range B105:F89. How this formula works This formula uses structured references to …
This tutorial covers basic inventory formula. To calculate current stock, or inventory, you can use Excel Tables with a formula based on the SUMIF function. Formula =SUMIFS(In[Qty],In[Color],A1)-SUMIFS(Out[Qty],Out[Color],A1) Explanation In the example shown, the formula in K7 is: =SUMIFS(In[Qty],In[Color],J7)-SUMIFS(Out[Qty],Out[Color],J7) Where “In” is the Excel Table on the left, “Out” is the table in the middle. How this formula …
This tutorial shows how to count columns in an Excel table. To achieve this, you can use the COLUMNS function. See example below: Formula COLUMNS(table) Explanation In the example shown, the formula in I4 is: =COLUMNS(Table1) How this formula works This formula uses structured referencing, a syntax that allows table parts to be referred to by name. When …
To count rows in an Excel table, you can use the ROWS function. See example: Note: with just the table name, ROWS will count data rows only. Formula ROWS(table) Explanation In the example shown, the formula in I4 is: =ROWS(Table1) How this formula works This formula uses structured referencing, a syntax that allows table parts to be …
To build a formula with a dynamic reference to an Excel Table name, you can use the INDIRECT function with concatenation as needed. Formula =SUM(INDIRECT(table&”[column]”)) Note: INDIRECT is a volatile function and can cause performance issues in larger, more complex workbooks. Explanation In the example shown, the formula in L5 is: =SUM(INDIRECT(K5&”[Amount]”)) Which returns the SUM of Amounts …
To calculate a conditional percentile, you can use an array formula using the IF function inside the PERCENTILE function. See example below: Formula =PERCENTILE(IF(criteria,values),k) Note: This is an array formula and must be entered with control + shift + enter. Explanation In the example shown, the formula in G5 is: =PERCENTILE(IF(Table[Gender]=G$4,Table[Score]),$F5) Where “Table” is an Excel Table with data …
To use COUNTIFS with a variable table column, you can use INDEX and MATCH to find and retrieve the column for COUNTIFS. See example below: Formula =COUNTIFS(INDEX(Table,0,MATCH(name,Table[#Headers],0)),criteria)) Explanation In the example shown, the formula in H5 is: =COUNTIFS(INDEX(Table1,0,MATCH(G5,Table1[#Headers],0)),”x”) How this formula works First, for context, it’s important to note that you can use COUNTIFS with …
This tutorial shows illustrates a Running total in Excel Table. To create a running total in an Excel Table, you can use the INDEX function set up with a structured reference. Formula =SUM(INDEX([column],1):[@column]) Explanation In the example shown, the formula in F5 is: =SUM(INDEX([Total],1):[@Total]) When copied down the column, this formula will return a running total …
To get the index of a column in an Excel Table, you can use the MATCH function. See example below: Formula =MATCH(name,Table[#Headers],0) Explanation In the example shown, the formula in I4 is: =MATCH(H4,Table1[#Headers],0) When the formula is copied down, it returns an index for each column listed in column H. Getting an index like this …
To calculate the average for the last N values n an Excel table (i.e. last 3 rows, last 5 rows, etc.) you can use the AVERAGE function together with the INDEX and ROWS functions. See example below: Formula =AVERAGE(INDEX(table[column],ROWS(table)-(N-1)):INDEX(table[column],ROWS(table))) Explanation In the example shown, the formula in F5 is: =AVERAGE(INDEX(Table1[Sales],ROWS(Table1)-(F4-1)):INDEX(Table1[Sales],ROWS(Table1))) How this formula works This …
To get the name of a column in an Excel Table from its numeric index, you can use the INDEX function with a structured reference. See example below: Formula =INDEX(Table[#Headers],index) Explanation In the example shown, the formula in I4 is: =INDEX(Table1[#Headers],H5) When the formula is copied down, it returns an name for each column, based …
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 …
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: …
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 …
Sorting data is an integral part of data analysis. You might want to arrange a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons. This example teaches you how to sort data by color in Excel. 1. Click any single cell inside a data set. 2. …
This chapter teaches you an easy way to reverse a list in Excel. For example, we want to reverse the list in column A below. 1. Enter the value 1 into cell B1 and the value 2 into cell B2. 2. Select the range B1:B2, click the lower right corner of this range, and drag it down to cell B8. 3. …
How to randomize / shuffle a list in Excel. For example, we want to randomize the list in column A below. 1. Select cell B1 and insert the RAND() function. 2. Click on the lower right corner of cell B1 and drag it down to cell B8. 3. Click any number in the list in column B. 4. To …
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 …
This example teaches you how to apply a date filter to only display records that meet certain criteria. 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. 3. Click the arrow next to Date. 4. Click on Select …
The data form in Excel allows you to add, edit and delete records (rows) and display only those records that meet certain criteria. Especially when you have wide rows and you want to avoid repeated scrolling to the right and left, the data form can be useful. 1. Open the downloadable Excel file. 2. Add the Form command to the …
Outlining data makes your data easier to view. In this example we will total rows of related data and collapse a group of columns. 1. First, sort the data on the Company column. 2. On the Data tab, in the Outline group, click Subtotal. 3. Select the Company column, the column we use to outline our worksheet. 4. Use …
The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use. The SUBTOTAL function is designed for columns of data, or vertical ranges. Use the SUBTOTAL function in Excel instead of SUM, COUNT, MAX, etc. to ignore rows hidden by a filter or manually hidden rows. Rows …
Illustrate Project Schedule in Gantt Chart Excel does not offer Gantt as chart type, but it’s easy to create a Gantt chart by customizing the stacked bar chart type. Below you can find our Gantt chart data. To create a Gantt chart, execute the following steps. 1. Select the range A3:C11. 2. On the Insert tab, in the Charts …
If the Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales and Icon Sets are not sufficient, you can create a new rule. For example, highlight the codes below that occur more than once in the range A2:A10 andhave a score greater than 100. 1. Select the range A2:A10. 2. On the Home tab, in the Styles …
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 …
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 …
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. …
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. …
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. …
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 …
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. …
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 …