Data Analysis

 

This section illustrates the powerful features Excel has to offer to analyze data.

1 Sort: You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.

2 Filter: Filter your Excel data if you only want to display records that meet certain criteria.

3 Conditional Formatting: Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.

4 Charts: A simple Excel chart can say more than a sheet full of numbers. As you'll see, creating charts is very easy.

5 Pivot Tables: 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.

6 Tables: Master Excel tables and analyze your data quickly and easily.

7 What-If Analysis: What-If Analysis in Excel allows you to try out different values (scenarios) for formulas.

8 Solver: Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems.

9 Analysis ToolPak: The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis.

Data Analysis+

 

Become an Excel pro! You can find related examples and features on the right side of each chapterat the bottom of each chapter. Below you can find an overview.

1 Sort: Custom Sort Order | Sort by Color | Reverse List | Randomize List | SORT function

2 Filter: Number and Text Filters | Date Filters | Advanced Filter | Data Form | Remove Duplicates | Outlining Data | Subtotal | Unique Values | FILTER function

3 Conditional Formatting: Manage Rules | Data Bars | Color Scales | Icon Sets | Find Duplicates | Shade Alternate Rows | Compare Two Lists | Conflicting Rules | Heat Map

4 Charts: Column Chart | Line Chart | Pie Chart | Bar Chart | Area Chart | Scatter Plot | Data Series | Axes | Chart Sheet | Trendline | Error Bars | Sparklines | Combination Chart | Gauge Chart | Thermometer Chart | Gantt Chart | Pareto Chart

5 Pivot Tables: Group Pivot Table Items | Multi-level Pivot Table | Frequency Distribution | Pivot Chart | Slicers | Update Pivot Table | Calculated Field/Item | GetPivotData

6 Tables: Structured References | Table Styles | Merge Tables | Table as Source Data | Quick Analysis

7 What-If Analysis: Data Tables | Goal Seek | Quadratic Equation

8 Solver: Transportation Problem | Assignment Problem | Capital Investment | Shortest Path Problem | Maximum Flow Problem | Sensitivity Analysis | System of Linear Equations

9 Analysis ToolPak: Histogram | Descriptive Statistics | Anova | F-Test | t-Test | Moving Average | Exponential Smoothing | Correlation | Regression

Check out all 300 examples.

What's Popular?

 

Especially popular are the 9 chapters discussed above. Also very popular are the examples and features below. Happy learning!

1 Find Duplicates: This example teaches you how to find duplicate values (or triplicates) and how to find duplicate rows in Excel.

2 Histogram: This example teaches you how to make a histogram in Excel.

3 Regression: This example teaches you how to run a linear regression analysis in Excel and how to interpret the Summary Output.

4 Pareto Chart: A Pareto chart combines a column chart and a line graph. The Pareto principle states that, for many events, roughly 80% of the effects come from 20% of the causes.

5 Remove Duplicates: This example teaches you how to remove duplicates in Excel.

6 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.

7 Line Chart: 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.

8 Correlation: We can use the CORREL function or the Analysis Toolpak add-in in Excel to find the correlation coefficient between two variables.

9 Pie Chart: Pie charts are used to display the contribution of each value (slice) to a total (pie). Pie charts always use one data series.

10 Data Tables: 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.

11 t-Test: 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.

12 Advanced Filter: This example teaches you how to apply an advanced filter in Excel to only display records that meet complex criteria.

13 Frequency Distribution: 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.

14 Scatter Plot: Use a scatter plot (XY chart) to show scientific XY data. Scatter plots are often used to find out if there's a relationship between variable X and Y.

15 Anova: This example teaches you how to perform a single factor ANOVA (analysis of variance) in Excel.

16 Bar Chart: A bar chart is the horizontal version of a column chart. Use a bar chart if you have large text labels.

17 Goal Seek: If you know the result you want from a formula, use Goal Seek in Excel to find the input value that produces this formula result.

18 Sparklines: Sparklines in Excel are graphs that fit in one cell. Sparklines are great for displaying trends. Excel offers three sparkline types: Line, Column and Win/Loss.

Check out all 300 examples.