35  The 80/20 of Data Analysis With Excel

The 80/20 principle (also known as the Pareto Principle) suggests that roughly 80% of effects come from 20% of causes. In the context of data analysis with Excel, this means that by mastering a core set of Excel functions and features, you can accomplish the majority of common data analysis tasks efficiently.

In the 80/20 spirit, we focus on the most impactful skills that will enable you to perform effective data analysis without getting bogged down in less frequently used features. You will learn how to use Excel to:

• Import and export data
• Manipulate data
• Analyze data
• Visualize data

The content list below provides a comprehensive overview of the skills you will acquire during the course. The links direct you to official Microsoft documentation and tutorials for each topic.

35.1 Import/Export Data

• Insert data from picture
• Import or export text (.txt or .csv) files
• Split (delimited) text into different columns
• Save charts as .SVG files
• Save tables as .PDF files (export selection)
• Print a worksheet or workbook

35.2 Manipulate Data

• Create and format tables
• Replace a formula with its result (paste values only)
• Use calculation operators (arithmetic, comparison, text concatenation, and reference)
• Switch between relative, absolute, and mixed cell references (freeze cells, columns, and rows)
• Use formulas and constants
• Use AutoFill and Flash Fill
• Change the column width or row height
• Use nested functions in a formula
• Load the Analysis ToolPak and Create a histogram (Pareto and Cumulative Percentage)
• Use IF with AND, OR, and NOT functions (conditional formulas)
• Generate random numbers with RANDBETWEEN and RAND functions
• Look up values with XLOOKUP, XMATCH, and INDEX functions
• Use string functions such as TRIM, LEFT, RIGHT, FIND, CONCAT, REPT, TEXT, Ampersand symbol (&)
• Sum numbers using SUM, SUMIF, SUMIFS, and SUMPRODUCT
• Count numbers using COUNT, COUNTIF, and COUNTIFS
• Calculate absolute values (ABS) and square roots (SQRT)
• Retrieve the row (ROW) and column (COLUMN) numbers and offset (OFFSET) values
• Round a number (ROUND, ROUNDUP, ROUNDDOWN)
• Display the relationships between formulas and cells (Trace Precedents/Dependents)
• Evaluate a nested formula one step at a time
• Calculate multiple results by using a data table (What-If Analysis)
• Use Goal Seek to find the result you want by adjusting an input value

35.3 Analyze Data

• Create a chart
• Create a PivotTable to analyze worksheet data
• Use the Field List to arrange fields in a PivotTable
• Highlight patterns and trends with conditional formatting
• Sort data in a Excel table using many levels
• Freeze panes to lock the first row or column
• Analyze data automatically

35.4 Visualize Data

Relevant skills when you are using generated figures and tables on reports:

• Export pretty, professional tables (see guide (latex), guide (APA), examples (latex), and tutorial).
• Convey graphical results using the right chart for your data.
• Provide captions with enough context to understand tables and figures without going back to the main text (see guide).