Data Analysis

 

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

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 | Quick Analysis | Merge Tables

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

Data Analysis +

 

Become an Excel pro! For an overview, scroll up. Below you can find a description of the first 50 pages.

Custom Sort Order: You can use Excel to sort data in a custom order. In this example, we would like to sort by Priority (High, Normal, Low).

2 Sort by Color: This example teaches you how to sort data by color in Excel.

3 Reverse List: Learn how to reverse the order of a list in Excel. If you have Excel 365 or Excel 2021, use SEQUENCE, SORTBY and ROWS to sort a list in reverse order.

4 Randomize List: Learn how to randomize (shuffle) a list in Excel. If you have Excel 365 or Excel 2021, use RANDARRAY, SORTBY and ROWS to randomize a list in Excel.

5 SORT function: Use the SORT function in Excel 365/2021 to sort your Excel data on one column or multiple columns. Let's give it a try.

6 Number and Text Filters: This example teaches you how to apply a number filter and a text filter to only display records that meet certain criteria.

7 Date Filters: This example teaches you how to apply a date filter in Excel.

8 Advanced Filter: Learn how to apply an advanced filter in Excel to only display records that meet complex criteria.

9 Data Form: The data form in Excel allows you to add, edit and delete records (rows) and display only those records that meet certain criteria.

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

11 Outlining Data: Outlining data makes your data easier to view. In this example, we will total rows of related data and collapse a group of columns.

12 Subtotal: Use the SUBTOTAL function in Excel (instead of SUM, COUNT, MAX, etc.) to ignore rows hidden by a filter or to ignore manually hidden rows.

13 Unique Values: To find unique values in Excel, use the Advanced Filter. You can extract unique values or filter for unique values. If you have Excel 365 or Excel 2021, use the UNIQUE function.

14 FILTER function: Use the magic FILTER function in Excel 365/2021 to extract records that meet certain criteria. The FILTER function is quite versatile.

15 Manage Rules: 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.

16 Data Bars: Use data bars in Excel to visualize values in a range of cells. A longer bar represents a higher value.

17 Color Scales: Use color scales in Excel to visualize values in a range of cells. The shade of the color represents the value in the cell.

18 Icon Sets: Use icon sets in Excel to visualize values in a range of cells. Each icon represents a range of values.

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

20 Shade Alternate Rows: To shade alternate rows in Excel, use conditional formatting or apply a table style. Coloring every other row makes it easier to read your data.

21 Compare Two Lists: This example describes how to compare two lists using conditional formatting. For example, you may have two lists of NFL teams.

22 Conflicting Rules: Sometimes multiple conditional formatting rules in Excel conflict. A higher rule always wins. This example illustrates two different results.

23 Heat Map: To create a heat map in Excel, simply use conditional formatting. A heat map is a graphical representation of data where individual values are represented as colors.

24 Column Chart: Column charts are used to compare values across categories by using vertical bars. To create a column chart in Excel, execute the following steps.

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

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

27 Bar Chart: 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 in Excel, execute the following steps.

28 Area Chart: 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.

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

30 Data Series: A row or column of numbers in Excel that are plotted in a chart is called a data series. You can plot one or more data series in a chart.

31 Axes: 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.

32 Chart Sheet: So far, we have only seen Excel 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).

33 Trendline: This example teaches you how to add a trendline to a chart in Excel.

34 Error Bars: This example teaches you how to add error bars to a chart in Excel.

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

36 Combination Chart: A combination chart is a chart that combines two or more chart types in a single chart.

37 Gauge Chart: A gauge chart (or speedometer chart) combines a Doughnut chart and a Pie chart in a single chart. If you are in a hurry, simply download the Excel file.

38 Thermometer Chart: This example teaches you how to create a thermometer chart in Excel. A thermometer chart shows you how much of a goal has been achieved.

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

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

41 Group Pivot Table Items: This example teaches you how to group pivot table items in Excel. Learn how to group products and how to group dates by quarters.

42 Multi-level Pivot Table: It's perfectly ok to drag more than one field to an area in a pivot table. We will look at an example of multiple row fields, multiple value fields and multiple report filter fields.

43 Frequency Distribution: Did you know that you can use pivot tables to easily create a frequency distribution in Excel?

44 Pivot Chart: A pivot chart is the visual representation of a pivot table in Excel. Pivot charts and pivot tables are connected with each other.

45 Slicers: Use slicers in Excel to quickly and easily filter pivot tables. Connect multiple slicers to multiple pivot tables to create awesome reports.

46 Update Pivot Table: Any changes you make to the data set are not automatically picked up by a pivot table in Excel. Refresh the pivot table or change the data source to update the pivot table with the applied changes.

47 Calculated Field/Item: This example teaches you how to insert a calculated field or calculated item in a pivot table.

48 GetPivotData: To quickly enter a GETPIVOTDATA function in Excel, type an equal sign (=) and click a cell in a pivot table. The GETPIVOTDATA function can be quite useful.

49 Structured References: When working with tables in Excel, you can use structured references to make your formulas easier to understand.

50 Table Styles: Quickly format a range of cells by choosing a table style. You can also create your own table style in Excel.

Check out all 300 examples.