Using Excel is a great way to start data analysis. Excel can handle large data sets with tools like pivot tables, pivot charts, and regression analysis.
This is Lesson 9 in Excel Basics. These lessons cover topics to help you learn data analysis using Excel.
For all the lessons, see The Ultimate Guide to Finally Learn Excel.
Excel Add-ins – Data Analysis
There are two buttons that can be added to the Data tab by using the Add-ins option. These buttons are Data Analysis and Solver.
Note: these buttons are included in Excel, but they are not turned on by default. You will have to add Data Analysis and Solver manually. Then the buttons are turned on in the future.
The Data Analysis tool includes statistical tools such as ANOVA, regression, t-test, and z-test.
The regression function is located in the Data Analysis tool on the Data ribbon. The regression formula is Y = a + bX.
- Y is the dependent variable
- X is the independent variable
- a is the y-intercept
- b is the slope of the line
One use of regression is to estimate fixed and variable costs. In this case, fixed costs would be A and the variable costs per unit would be B and the number of units would be X.
Data Analysis includes statistical tools such as ANOVA, regression, t-test, and z-test.
Pivot Tables and Pivot Charts
A pivot table can summarize many data points into a single, dynamic table. It can summarize by totals, by products, dates, names, etc. It can be changed with the use of slicers.
A dynamic chart based on a pivot table. It can be changed dynamically with the use of slicers.
Slicers are used for pivot tables and pivot charts in Excel. They can dynamically change the data shown by the pivot table. This can be used for a dynamic dashboard.
Workbook that is organized in a logical and consistent way to help users understand the assumptions and the calculated results. There should be an input section and a section for comments, documentation, and notes. All formulas should be built on the assumptions with proper formulas.
Located in the Data tab
Data Validation can minimize errors in data entry into worksheets. This defines what is a valid entry into the cell. It can be a list or a range of acceptable values. If there is an error, it can display an error message.
Located in the Tools menu
- Worksheet protection – protect the contents of the worksheet to prevent changing, moving, or deleting the data. Cells can be unlocked to allow for data entry.
- Workbook protection – protect a workbook to prevent inserting, deleting, or renaming a worksheet.
- File protection – protect an entire workbook file for optimal security.
Located in the Formula menu
- Trace precedents – show arrows to indicate which cells affect the value of the current cell
- Trace dependents – show arrows to indicate which cells are affected by the current cell
- Show formulas – toggles the display of formulas or the results of the formulas
- Error checking – error checking tool and trace errors
- Evaluate formulas – displays the different parts of a nested formula in the order of calculation
- Print ⌘P or Ctrl P
- Print to fit – minimize spreadsheet to set number of pages
- Print active sheets – only the selected sheet
- Print workbook – prints the entire workbook
Use the print function to preview how it will look after printing or as a PDF file.
Excel Video: Pivot Tables and Pivot Charts Explained
Excel Basics Lessons
We have a full set of Excel basics lessons. For all the lessons, see The Ultimate Guide to Finally Learn Excel.
Here are the Excel lessons: