Did you know, that many techniques can be used in data analytics in Excel, for example creating descriptive statistical analysis, regression, data manipulation, data visualization, creating tables/graphs, creating PivotTable reports, creating analytics dashboards, etc…
In this Excel Data Analytics Handbook, I have organized into 8 main chapters arranged based on each feature’s function.
So, I hope you read and understand each sub-chapter of this guide to gain a deep understanding.
Note: Most of the content is available in Indonesian. Please visit the Excel Data Analytics Guide!
Chapter 1: Sort & Filter
First of all, understand how to use the Sort & Filter feature as the most basic technique in data analytics in Excel.
If you understand how to Sort & Filter in Excel, it will likely make it easier for you to learn more complex Data Analysis. For example, to create charts, Pivot Tables, etc…
There are around 10 sub-chapters that you can learn. So I hope that several examples + case studies in each sub-chapter can provide in-depth understanding for you.
- Sort: How to sort data in Excel (1 column, 2 or more columns, and custom).
- Sort by Color: How to sort data based on cell color, font, and icon.
- Reverse & Randomize List: How to randomize names using the RAND formula, reverse the data order using Sort and an example of taking names from a data list randomly (a combination of the INDEX, RANDBETWEEN, and COUNTA functions).
- Filter & AutoFilter: How to filter data (numbers, text, dates and colors) manually and automatically using the AutoFilter feature.
- Quick Filter: How to quickly filter using keyboard shortcuts (without touching the mouse / touchpad) such as using CTRL + SHIFT + L, ALT + Down arrow – E etc.
- Advanced Filter: Filter data based on certain criteria (Can use Excel functions).
- Data Form: How to create a data input form (such as stock data, employee attendance, employee data, sales data, etc.)
- Remove Duplicates: 3 best ways to delete duplicate data.
- Outlining Data: Grouping data (Grouping) and displaying Subtotals automatically.
- Subtotal: Displays subtotals based on data filters.
Chapter 2: Conditional Formatting
With Conditional Formatting, you can change the cell format (such as cell color) to a certain color if it meets the criteria or create a mini graph in the cell and can even search for duplicate data.
Most interestingly, you can also combine it with formulas/functions for more complex purposes.
However, using formulas/functions for Conditional Formatting isn’t the same as the rules for writing regular formulas/functions. There’s a special trick!
Start from the first sub-chapter so you don’t miss important parts in using Conditional Formatting.
- Conditional Formatting: Highlights cells / ranges with certain conditions. For example, if cell A2 contains >50, then the cell color changes to red.
- Manage Rules: Manage Conditional Formatting that already exists in Worksheets.
- Data Bars: Create Data Bars in cells to provide an overview of your data in the form of a Mini Bar Chart.
- Color Scales: Create Color Scales to provide an overview of your data in the form of visual colors.
- Icon Sets: Create Icon Sets to provide an overview of your data in the form of icons.
- Formulas + Conditional Formatting: How to Use Formulas / Functions in Conditional Formatting (10++ Examples).
- Find Duplicates: Search for the same data and mark it (highlight it).
- Compare Data: Steps to compare data from two or more different tables.
- Conflicting Rules: Conflicts that occur between each Conditional Formatting and how to resolve them.
- Checklist: How to create a Checklist Box in a particular Cell and combine it with Conditional Formatting.
- Heat Map: Create an attractive Heat Map (Example: Visualization of Website Traffic in Calendar form).
Chapter 3: Tables
In Excel, there is a Table Format feature so you can give a name to a table with an identity known to Excel.
This identity is almost similar to the Named Range which allows you to use a structured formula, making the formula easier to read.
For example, the following AVERAGE formula. =AVERAGE(Purchase[10% Off]). That’s not the same as the AVERAGE formula in general, right?
Meanwhile, if you create a table only using Borders, then you can’t use Structured Formulas.
Important! If you can use the Table Format feature, please study the Excel Structured Formula Guide.
- Table: How to create a Table and define it with a name.
- Table Styles: How to change and style table designs.
Chapter 4: Charts
A chart (graph) can provide an overview of hundreds of thousands of rows of data or even more. Data analysis using this chart is very popular in every profession.
For example, a teacher wants to make a Pie Chart to see the percentage of male and female students in class XII.
Another example is an investor who wants to see stock price movements (daily data) from 2000 – 2021.
Are there any other examples? Lots and lots. For example, sales growth analysis, website traffic, etc.
However, don’t choose the wrong type of chart to describe your data. Why? Because some types of charts are only suitable for using certain data.
To make it easier, look at each example of the following chart types:
- Charts: How to create, modify, and replace charts in Excel.
- Column Chart: How to create a Column Chart (Clustered, Stacked, 100% Stacked for 2-D and 3-D).
- Data Series: How to change and organize data sources on a graph.
- Axes: Sets the X (horizontal) and Y (vertical) axes on the graph.
- Chart Sheet: How to move a Chart (Graph) to 1 special sheet.
- Trendline: How to add a Trendline to a chart.
- Error Bars: How to add Error Bars to the chart.
- Sparklines: How to add Sparklines into 1 cell. Complete example.
- Combination Chart: How to use 2 charts into 1 chart.
- Gauge Chart: How to create a complete gauge chart (such as a speedometer).
- Thermometer Chart: How to create an indicator to measure goal achievement using a thermometer chart.
- Gantt Chart: How to create a Gantt Chart that doesn’t exist in Excel’s default settings.
- Pareto Chart: How to create a Pareto Chart.
Chapter 5: PivotTables
This is the most popular Excel Data Analytics feature, namely “PivotTable”. This is a feature that can retrieve data from source tables and then create specific summaries that can be customized for various purposes.
This feature will save you time significantly. Especially if you have large amounts of data.
You can use PivotTables to create data summaries in both table and chart form.
Apart from that, for more complex purposes, you can also create sophisticated filters with the Slicers feature, use formulas in Pivot reports, and so on. For complete details, please study each of the following sub-chapters:
- PivotTable: How to Insert a PivotTable to create a report.
- Group PivotTable: Group PivotTable data by date, product, category, etc.
- Multi-Level PivotTable: Create PivotTable reports with multi-rows, multi-columns, multi-values, multi-filters, etc.
- Frequency Distribution: Create a frequency distribution (such as a histogram) using PivotTables.
- PivotChart: Learn how to create a PivotChart into a PivotTable report.
- Slicers: How to use Slicers to filter PivotTable data and create more attractive reports.
- Update PivotTable: How to update or change the PivotTable data source
- Calculated Field/Item: Changing the value calculation method, using a combination of formulas/functions (such as IF, etc.) for more complex report calculations.
- GETPIVOTDATA: How to use the GETPIVOTDATA function to retrieve pivot table data without errors.
Chapter 6: Analysis Toolpak
If you want more complex technical and statistical analysis, then use the Analysis Toolpak which is an additional data analytics add-in that uses popular engineering / statistical macro functions.
For example, Descriptive Analysis, ANOVA, Regression, t-test, Correlation, etc. You can also do analyses like this using Excel.
- Analysis ToolPak: Get to know the Analysis Toolpak add-in for financial, statistical, and engineering data analysis.
- Histogram: How to create a histogram using Analysis Toolpak.
- Descriptive Statistics: How to analyze descriptive statistics using Analysis Toolpak.
- ANOVA: How to analyze variance (one-way ANOVA) in Excel.
- F-Test: How to F-test using Excel.
- t-Test: How to t-test using Excel.
- Moving Average: How to create a Moving Average chart using Excel.
- Exponential Smoothing: How to create an Exponential Smoothing Moving Average graph in Excel.
- Correlation: Test Correlation using Excel.
- Regression: How to test Linear Regression using Excel.
Chapter 7: What-If Analysis
Note: The What-If Analysis chapter requires advanced skills that are more suitable for use in a business environment with a user-level equivalent to a manager or decision-maker. It’s better to ignore this feature if you don’t need it, rather than causing a headache :D.
- What-If Analysis: An example of mastering the complete Excel What-If Analysis feature. (Different Scenarios, Scenario Summary, and Goal Seek)
- Data Table: Getting to know Data Tables in What-If Analysis (One Variable and Two Variables)
- Goal Seek: Complete Goal Seek example.
- Quadratic Equation: Example of using What-If Analysis for quadratic equations etc.
Chapter 8: Solver
Solver is also an additional add-in in Excel which is useful for analyzing data and determining optimal values (results).
It might be a little difficult for me to explain it in this paragraph. It’s a good idea to start from the first sub-chapter, then deepen the material with several examples from sub-chapters 2, 3, and so on…
- Solver: How to use Solver in Excel to find optimal solutions to all types of decision problems.
- Transportation Problem: Example of using Solver 1.
- Assignment Problem: Example of using Solver 2.
- Shortest Path Problem: Example of using Solver 3.
- Maximum Flow Problem: Example of using Solver 4.
- Capital Investment: Example of using Solver 5.
- Sensitivity Analysis: How to create a sensitivity report in Solver.
Microsoft Excel Guide Shortcut
