Excel Data Analysis is a powerful tool that is used to visualize and gain insights of records in a spreadsheet.
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. …
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 …
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 …
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 …
These example describes how to compare two lists using conditional formatting. Example 1: Compare Lists of Customers for May 2010 and April 2010. Select cells in both lists (select first list, then hold CTRL key and then select the second) Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values Press ok. See other examples Example …
The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables. …
Examples of R Square, Significance F and P-Values, Coefficients and Residuals. Below you can find our data. The big question is: is there a relation between Quantity Sold (Output) and Price and Advertising (Input). In other words: can we predict Quantity Sold if we know Price and Advertising? 1. On the Data tab, in the Analysis …
How to Perform Analyses of Variance in Excel This example teaches you how to perform a single factor ANOVA (analysis of variance) in Excel. A single factor or one-way ANOVA is used to test the null hypothesis that the means of several populations are all equal. Below you can find the salaries of people who have a degree …
Find out how to calculate correlation coefficient between two data arrays in Microsoft Excel through the CORREL function. The correlation coefficient (a value between -1 and +1) tells you how strongly two variables are related to each other. We can use the CORREL function or the Analysis Toolpak add-in in Excel to find the correlation coefficient between two variables. – A correlation coefficient of +1 indicates a …
What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. The following example helps you master what-if analysis quickly and easily. Create Different Scenarios, Scenario Summary and Goal Seek. Navigation: Data Tab → Data Tools Group → What-If Analysis See Examples below: Assume you own a book store and have 100 books in storage. You sell …
You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order. Navigation: Data Tab → Sort & Filter Group → Sort One Column To sort on one column, execute the following steps. 1. Click any cell in the column you want to sort. 2. To sort in ascending order, on the Data tab, …
One Variable and Two Variable Data Table in Excel Instead of creating different scenarios, you can create a data table to quickly try out different values for formulas. You can create a one variable data table or a two variable data table. Assume you own a book store and have 100 books in storage. You sell a certain % …
Filter your Excel data if you only want to display records that meet certain criteria. By filtering data in a worksheet, you can find values quickly. You can filter on one or more columns of data. With filtering, you can control not only what you want to see, but what you want to exclude. You can filter …
Quickly identify variances in record using Conditional formatting. Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell’s value. By conditional formatting to your data, you can quickly identify variances in a range of values with a quick glance. Navigation: Home Tab → Styles Group → Conditional Formating Highlight Cells Rules To highlight cells that …
Did you know that you can use pivot tables to easily create a frequency distribution in Excel? You can also use the Analysis Toolpak to create a histogram. Remember, our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country. First, insert a pivot table. Next, drag the following fields to the different …
Manage Rules enables users click New Rule, Edit Rule and Delete Rule to create, edit and delete rules in Conditional Formatting in Excel. To view all conditional formatting rules in a workbook, use the Conditional Formatting Rules Manager. You can also use this screen to create, edit and delete rules. 1. Select cell A1. 2. On the …
How to insert pivot table / pivot chart in excel? Insert a Pivot Table Drag fields Sort and Filter Change Summary Calculation Two-dimensional Pivot Table Pivot tables are one of Excel’s most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. Our data set consists of 213 records and 6 fields. …
Tables allow you to analyze your data in Excel quickly and easily. Learn how to insert, sort and filter a table, and how to display a total row at the end of a table. Navigation: Insert Tab → Tables Group – Table Insert a Table To insert a table, execute the following steps. 1. Click any single cell inside …
Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems. Load the Solver Add-in To load the solver add-in, execute the following steps. 1. On the File tab, click Options. 2. Under Add-ins, select Solver Add-in and click on the Go button. 3. Check Solver …
This example teaches you how to create a histogram in Excel. 1. First, enter the bin numbers (upper levels) in the range C4:C8. 2. On the Data tab, in the Analysis group, click Data Analysis. Note: can’t find the Data Analysis button? Click here to load the Analysis ToolPak add-in. 3. Select Histogram and click OK. 4. Select the range …
When duplicates are removed in Excel, the first occurrence of the value in the list is kept, but other identical values are deleted. This example teaches you how to remove duplicates in Excel. Go here to only find duplicates. 1. Click any single cell inside the data set. 2. On the Data tab, in the Data Tools group, click …
Remove Duplicate Rows Using the Excel Remove Duplicates Command. In Excel, the Remove Duplicates command can help you to remove duplicated records in a column. The Remove Duplicates command is located in the ‘Data Tools’ group, within the Data tab of the Excel ribbon. Follow the steps below: Data Tab → Data Tools Group → Remove Duplicate …
To test null hypothesis for unequal variance This example teaches you how to perform a t-Test in Excel. The t-Test is used to test the null hypothesis that the means of two populations are equal. Steps in running a t-test in Excel I have Group 1 (female) and Group 2 (male) test scores of a classroom. I need …
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 …