How to learn Microsoft Excel for Data Science in 2024

If you're aiming for a career in data, mastering Excel is a must. Almost every business uses Excel—it's one of the most versatile and widely used tools for data analysis globally. Microsoft Excel provides a wide range of functions and tools that can be used to analyze and visualize data.

For those who want to learn Excel, we've crafted a detailed roadmap in this post. It won't cover every Excel feature because there are many, but it'll give you a strong set of foundational skills you can start using in your job right away. So let’s begin to explore our roadmap for mastering Microsoft excel.

‍

Why Microsoft Excel for Data Analysis?

Microsoft Excel needs no introduction, it's been a go-to tool for all kinds of professionals for a long time. Wondering, why? Well, here's a breakdown of the features that make people love using Excel.

• Organize Data: Excel helps arrange data in rows and columns, making it easy to view and manage information.

• Perform Calculations: It has built-in formulas for quick calculations, allowing users to analyze and manipulate data effortlessly.

• Create Visuals: Excel generates charts and graphs to visually represent data trends, aiding in better understanding and communication.

• Filter and Sort: Users can quickly filter and sort data, facilitating the identification of patterns or specific information.

• Data Validation: Excel ensures data accuracy through validation rules, minimizing errors and enhancing the reliability of analysis.

‍

How to learn Microsoft excel: Complete Roadmap

Step 1: Getting started with Excel

Begin your Excel journey by familiarizing yourself with the interface and layout. Learn to navigate the ribbons, understand cells and columns, and grasp the fundamental elements that make Excel an essential tool for data professionals.

‍

Step 2: Build your knowledge of Excel functions

Excel has a wide range of functions that can be used to perform various calculations and operations on data. Some functions that are essential for data analysis include:

1. SUM

This function adds up a range of values in a column or row. The formula is =SUM(A1:A10) will add up the values in cells A1 to A10.

‍

‍

2. AVERAGE

This function calculates the average value of a range of numbers. The formula is =AVERAGE(A1:A10) will give you the average values in cells A1 to A10.

‍

3. COUNT

This function counts the number of cells that contain a value within a specified range. The formula is =COUNT(A1:A10) will count the number of cells that have a value in cells A1 to A10.

‍

4. IF

Evaluates a condition and returns one value if the condition is true and another value if the condition is false. The formula is =IF(condition, value_if_true, value_if_false).

‍

5. MAX

This function finds the maximum value in a range of cells. The formula is =MAX(A1:A10) will give you the highest value in cells A1 to A10.

‍

6. MIN

This function finds the minimum value in a range of cells. The formula is =MIN(A1:A10) will give you the lowest value in cells A1 to A10.

‍

7. VLOOKUP

This function searches for a specific value in the first column of a table and returns a value in the same row from a specified column. The formula is =VLOOKUP(A1,Table1,2,FALSE)

will search for the value in cell A1 in the first column of "Table1" and return the value in the second column of that row.

8. CONCATENATE

This function merges or joins several text strings into one text string. The formula is: =CONCATENATE(value1, " ", value2)

Step 3: Explore Data Analysis Techniques

After building the basics of excel formulas, the next step is to explore different data analysis techniques.

1. Sorting and filtering:

These steps should help you efficiently sort and filter data in Excel.

1. Sorting 

1. Basic Sorting

• Click on a column header and use the A-Z or Z-A button on the toolbar.
• Alternatively, right-click on the selected cells and choose "Sort."

2. Custom Sorting:

• Select the range, go to the "Data" tab, and click "Sort."
• Specify the column and criteria in the Sort dialog box.

‍

2. Filtering

1. Basic Filtering:

• Select the range and click on "Filter" in the "Data" tab.
• Use the dropdowns in the header row to filter data.

2. Custom Filters:

• Click the dropdown in a column header for options like text filters or color filters.

3. Advanced Filtering:

• Go to the "Data" tab, click "Advanced" for more complex criteria.

4. Clearing Filters:

• Go to the "Data" tab and click "Clear" to remove filters.

‍

2. PivotTables

‍A PivotTable is an interactive way to quickly summarize large amounts of data. PivotTable is especially designed for: Querying large amounts of data in many user-friendly ways. Here’s a quick guide of creating a PivotTable in Excel!

• Prepare Data: Ensure your data is organized with clear headers.

• Select Data: Click anywhere in your data range.

• Insert PivotTable: Go to the "Insert" tab, click "PivotTable," and confirm the range.

• Design PivotTable: Drag fields to "Rows," "Columns," "Values," or "Filters" to structure your PivotTable.

• Customize: Rearrange fields, apply filters, and choose summary functions.

• Refresh: If your data changes, right-click and select "Refresh."

‍

Learning Resources

1. Excel Basics for Data Analysis by Coursera

Duration: 11 hours

Level: beginner

‍

Step 4: Build Some Visualizations

Data visualization in Excel is an excellent way to present information in a graphical format to help you understand and analyze data more easily. Excel offers a wide range of data visualization tools to understand and analyze complex data more effectively by revealing patterns, trends, and relationships within the data.

Here’s a list of visualization tools provided by Microsoft excel.

‍

• Charts: Excel offers various chart types like bar, line, pie, etc., to visually represent data trends and comparisons.

• PivotTables: Allows you to summarize and analyze large datasets, making it easier to identify patterns and trends.

• Sparklines: Tiny charts placed in individual cells to show trends within a range of data without taking up much space.

• Conditional Formatting: Highlights cells based on specified criteria, making it easy to spot trends or outliers visually.

• Data Bars: Adds a horizontal bar to cells, the length of which corresponds to the cell's value, offering a quick visual comparison.

• Heat Maps: Uses colors to represent values in a matrix, making it easy to identify patterns and variations.

• Map Charts: Visualizes geographical data on a map, showing variations in data across regions.

• Scatter Plots: Displays individual data points on a two-dimensional graph, helpful for identifying relationships between variables.

‍

Learning Resources

1. Data visualization in excel by Coursera 

Duration: 17 hours

Level: Intermediate

‍

Step 5: Learn automation and customization

Automation and customization in Excel are two powerful tools that can help you save time, improve accuracy, and make your spreadsheets more user-friendly.

1. Automation

Excel has a number of built-in automation tools, such as macros and VBA, that can be used to automate repetitive tasks. For example, you could create a macro to automatically format a table, or write a VBA script to import data from a text file.

‍

2. Customization

Excel also allows you to customize the look and feel of your spreadsheets to make them more user-friendly. For example, you can change the fonts, colors, and cell formatting to make your spreadsheets easier to read. You can also create custom tabs, ribbons, and dialog boxes to make your spreadsheets more easy to use.

Getting started with automation and customization

There are a number of resources available to help you get started with automation and customization in Excel. These resources include:

• Microsoft's documentation: Microsoft provides a number of documentation resources on automation and customization in Excel.

• Third-party websites: There are a number of third-party websites that provide tutorials and resources on automation and customization in Excel.

• Books: There are a number of books available on automation and customization in Excel.

‍

Learning resources:

Here are some suggested learning resources of the Microsoft Excel for data analysts:

1. Microsoft’s official guide:

Microsoft Excel Official Tutorial

Data Analysis with Power Pivot

‍

2. YouTube:

• Power pivot tutorial
• chandoo.org

‍

3. Books:

• Mastering data analysis in excel
• Modern data analytics in excel

‍

4. Courses:

1. Data Analytics using Excel by GreatLearning

• Duration: 1.5 hours
• Level: beginner (with certification)

‍

2. Data analysis in excel by DataCamp

• Duration: 3 hours
• Level: Intermediate

‍

Conclusion

Mastering Microsoft Excel is crucial for data analysis, given its role in organizing, calculating, and visualizing data. This roadmap, from basic functions to automation, offers a structured approach. By exploring data analysis techniques, creating visualizations, and learning automation, one can gain a comprehensive skill set. The suggested courses and resources provide a practical path for skill development, empowering individuals to efficiently handle and analyze data for informed decision-making.