Mastering Microsoft Excel: From Basics To Advanced Analysis

Whether you are a data analyst or a regular MS Excel user, you will definitely find this post useful because I will take you through a complete mastery of MS Excel. I will touch on how to:

• Organize and manipulate data
• Perform calculations using formulas and functions
• Visualize data through charts and graphs
• Create interactive dashboards and reports
• Perform statistical, financial, and logical analysis using MS Excel

So buckle up and let's dive right in:

1. What is Excel?

You may be asking yourself, "What is Excel?" And you're right to. MS Excel is a powerful data analysis tool developed by the Microsoft company. It is used by business analysts, data analysts, project and operations managers, teachers and lecturers, researchers, sales and marketing teams, etc., to help them organize data, perform calculations, visualize information, create interactive dashboards, and conduct statistical analysis. Let's dive into an overview of Microsoft Excel:

2. Launching MS Excel

MS Excel is primarily a Windows application, so this article is Windows-based. To start MS Excel on Windows, click on the Windows button, type "excel," and click Enter to launch. You will see many templates, but for now, we are working with creating a new blank workbook. When you create a new workbook, you will be redirected to a new window with an empty worksheet. However, if for some reason your application doesn't have MS Excel, you can search for an online version of Excel on your favorite browser, which requires you to have a Microsoft account.

3. Key Concepts in Excel

Workbook

A workbook is an Excel file that contains one or more worksheets. You can open a workbook in Excel only if it has a .xlsx extension, which used to be .xls for older versions of Excel.

Worksheet

A worksheet is a single spreadsheet consisting of grids of rows and columns within a workbook. Each worksheet has a name (e.g., Sheet1) and can be renamed to your liking. You can create multiple worksheets by pressing the little '+' button at the bottom of the Excel workbook.

Cell

That's right, a cell. A cell is the basic unit of a worksheet where you enter data. It is identified by a cell reference, such as A1 (Column A, Row 1). A cell reference is generated according to the current active cell.

Data Types

General, Number, Currency, Date, Time, Scientific, Fraction, etc. You can change the type of data stored in multiple cells by highlighting them, going to the Home tab, where you will find the data type dropdown as shown below.

Range

A range is a group of two or more cells. It is used to define the group of data you would like to work on. A range can be:

• Vertical: {A1:A5}
• Horizontal: {A1:E1}
• Rectangular block: {A1:C5}

Formula

Formulas in Excel are user-defined mathematical equations (meaning they start with an '=' sign) that the user employs to perform mathematical calculations, e.g., =A1+B1.

Functions

Functions are also mathematical tools; however, instead of being user-defined, they are built-in Excel functions.
NB: They also start with an '=' sign, e.g., =SUM(A2:A6) where A2:A6 is the range, and the built-in function SUM returns the sum of all the values in the given range. Other important functions in Excel include:

AVERAGE

=AVERAGE(C2:C6) 

COUNT

=COUNT(B2:B6) 

MAX AND MIN

=MAX(B2:B6) or =MIN(B2:B6) 

More advanced functions include:

CONCATENATE

Combines text from multiple cells into one, e.g.,

=CONCATENATE(A2, " ", B2) 

UPPER() 

→ makes all text uppercase

LOWER() 

→ makes all text lowercase

PROPER() 

→ capitalizes first letters (e.g., “John Smith”)

LEFT

Extracts a specific number of characters from the beginning of a string. E.g.,

=LEFT(A2, 3) 

returns the first 3 characters of cell A2.

IF, IFS, and SWITCH

• IF: Used to make a decision based on true or false. Example:
  

  =IF(C2>=50, "Pass", "Fail") 
  

• IFS: Used to make a decision based on multiple statements.
  

  =IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", TRUE, "F") 
  

• SWITCH: Used to compare one value or expression against a list of values and returns the matching result, e.g.,
  

  =SWITCH(A2, "HR", "Human Resources", "IT", "Information Technology", "FIN", "Finance", "Unknown") 
  

  Returns "Information Technology" if A2 is "IT".

XLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array) 

E.g.:

=XLOOKUP("Pen", A2:A6, B2:B6) 

With those key concepts, let's now look at the key data operations in Excel, like Data filtering, sorting, validation, cleaning, visualization, and formatting. We will also touch on Pivot Tables and Pivot Charts.

4. DATA OPERATIONS IN EXCEL

DATA FILTERING and SORTING

Filtering allows you to display only rows that meet specific criteria. Sorting helps arrange your data in a meaningful order, e.g., in ascending or descending order.

There are two ways of doing this: the table way and the un-tabled format way:

TABLE WAY:

This brings us to the concept of a table in Excel. To create a table in Excel is easier than you might think. First, you might want to select all the data you want to put in a table. Now, there are two ways of selecting:

• Click and drag your mouse, which is the easiest for small datasets.
• Select the first row using your mouse and then use Shift + Down Arrow Keys or Ctrl + Click your mouse for advanced selective selections.

Once your data is selected, you can right-click, then click on "Table," then "Create Table."

Or, you could go to the Insert tab, select "Table," and voila!

You have a table you can use to filter and sort your data to your liking, only if you have auto-filter enabled for your table on the Home tab in MS Excel.

RANGE WAY:

Select the column data you want to sort or filter as explained earlier. Then, on the Home tab, you can find "Sort & Filter" buttons where you can sort and filter your data according to your preferences.

DATA VALIDATION

Restrict user input to only allow "Yes," "No," or "Maybe" in selected cells.

Steps in Excel:

1. Select the cells where the user should input a response (e.g., C2:C20).
2. Go to the Data tab.
3. Click on Data Validation.
4. In the dialog box, under the Settings tab:
   • Allow: List
   • Source: Yes,No,Maybe
5. Under the Input Message tab:
   • Title: Response Required
   • Input message: Please select Yes, No, or Maybe from the list.
6. Under the Error Alert tab:
   • Style: Stop
   • Title: Invalid Entry
   • Error message: Only Yes, No, or Maybe are allowed.
7. Click OK.

Users will now see a dropdown list in each selected cell and can only choose "Yes," "No," or "Maybe," preventing invalid data entries.

DATA CLEANING

NB: Before cleaning your data, make sure you study it well enough so that you do not remove nulls or duplicates of crucial data that will be needed for analysis. Use your wisdom and knowledge as a data analyst to clean your data effectively.

a. DUPLICATES

Here's how you remove duplicates in Excel:

1. Select your data (click and drag to highlight the range, or click on any cell in the range if it's a full table).
2. Go to the Data tab on the ribbon.
3. Click Remove Duplicates.
4. A popup appears—check the columns you want to check for duplicates.
5. Click Remove Duplicates.

b. NULLS

Let's say you want to replace nulls in Excel using the mode:

1. Go to File > Options > Add-ins.
2. At the bottom, choose Excel Add-ins, click Go.
3. Check Analysis ToolPak, then click OK.
4. Go to the Data tab and click Data Analysis.
5. Choose Descriptive Statistics, then click OK.
6. Select your data range, choose where to output the results, and check Summary statistics.
7. Click OK.

You could also use the formula =MODE.SNGL(A2:A100), which returns the mode, but make sure you select the correct range.

After finding the mode, we need to replace the nulls with the mode value:

1. Click on the header of the column where the blanks are.
2. Open Find and Replace (Ctrl + H or Home > Find & Select > Replace).
3. In Find what: leave it completely blank (do NOT type a space).
4. In Replace with: type the mode value.
5. Click Replace All.

c. DATA TYPES

Make sure you select the correct data types for the columns, which is part of data cleaning.

d. Conditional Formatting

Highlight anomalies or errors visually:
Go to Home > Conditional Formatting. Example: Highlight cells with text in a numbers column, or highlight duplicates.

e. Data Visualization

We do data visualization using charts and graphs. The general way to insert a chart for your data is:

1. Select Your Data (include headers, as they act as labels for charts).
2. Go to the Insert Tab.
3. Choose a Chart Type you need.
4. Customize the Chart (change colors or chart styles, add titles, data labels, or legends, switch rows and columns if the chart isn't displayed as expected, move and resize the chart).

There are various types of visualization templates, but the most common include:

• Line charts: Great for trends over time.
  

• Column charts and bar charts: Good for comparing values.
  Column chart:
  
  Bar chart:
  

• Pie chart: Shows parts of a whole.
  

• Scatter Plot: For relationships between two variables, e.g., weight and height.
  

• Area chart: Perfect for showing how values change over time.
  

f. PIVOT TABLES

Pivot tables are a powerful tool in Excel for summarizing and analyzing large datasets. They allow you to extract meaningful insights by grouping and aggregating data based on different fields.

g. Creating a Pivot Table:

1. Select the data range you want to analyze. Ensure your data has clear headers.
2. Go to the Insert tab and click PivotTable.
3. In the "Create PivotTable" dialog box, confirm the data range and choose where you want to place the pivot table (a new worksheet is usually recommended). Click OK.

1. The "Pivot Table Fields" pane will appear on the right. Drag and drop fields into the four areas:

* Rows: Fields placed here will appear as row labels in the pivot table.
* Columns: Fields placed here will appear as column labels.
* Values: Fields placed here will be aggregated (e.g., summed, averaged, counted). Excel will often automatically suggest an aggregation.
* Filters: Fields placed here can be used to filter the entire pivot table.

Benefits of Pivot Tables:

• Easy Summarization: Quickly summarize large amounts of data.
• Flexibility: Easily rearrange fields to view data from different perspectives.
• Calculations: Perform calculations like sums, averages, counts, percentages, etc., without writing formulas.
• Grouping: Group data by date, time, or other categories.
• Filtering: Filter data to focus on specific subsets.

h. PIVOT CHARTS

A pivot chart is a dynamic chart that is directly connected to a pivot table. It provides a visual representation of the summarized data in the pivot table, making it easier to understand trends and patterns.

Creating a Pivot Chart:

1. Create a pivot table first (as described above).
2. Select any cell within your created pivot table.
3. Go to the PivotTable Analyze (or Options in older versions) tab.
4. In the Tools group, click PivotChart.
5. Choose the desired chart type (e.g., column, bar, line).
6. Click OK.

The pivot chart will automatically display the data from your pivot table. As you manipulate the fields in the pivot table, the pivot chart will update dynamically to reflect those changes.

You could also add a slicer for a more dedicated and easy viewing of the pivot chart as shown on the left of the pivot chart.

Benefits of Pivot Charts:

• Visual Summarization: Provides a visual representation of pivot table data.
• Interactivity: Changes in the pivot table are immediately reflected in the chart.
• Enhanced Understanding: Makes it easier to identify trends and patterns in summarized data.
• Dynamic Reporting: Creates dynamic reports where the visuals change based on data manipulation.

By leveraging Pivot Tables and Pivot Charts, you can significantly enhance your data analysis capabilities in Excel, allowing for more insightful and dynamic reporting.

With that, you are good to go in excel. Hope you enjoyed the post. Leave a comment for critics or anything you might have in mind. Thank you.