Microsoft Excel - Part 1 (Basics)

Microsoft Excel is according to me, one of the greatest softwares developed by Bill Gates. How would a business operate and for that matter how would human race might have progressed without Excel is hard to imagine.

This post on Excel has multiple parts. First part is basics which will cover the following topics:

1. Introduction

2. Calculation

3. Data Filter

4. Data Validation

5. Data Grouping

1. Introduction:

Let's understand briefly what is Excel toolbar and what is Excel sheet. Toolbar consists of ribbon tabs and ribbon bar. Each Excel workbook has 3 worksheets by default. These worksheets store data in rows and columns. You can rename and add colour to it for easy reference.

Ribbon tabs – Use commands on ribbon tab to perform little complex functions like adding charts, formula or data. The Home tab is used for basic commands such as formatting the data to make it more presentable, sorting and finding specific data within the spreadsheet.

Ribbon bar – these bars are used to group similar commands together. As an example, font style, size and colour under one bar, while alignment ribbon bar is used to group all the commands that are used to align data together.

Column – vertical set of cells is column. Here column E is highlighted.

Row – horizontal set of cells is row. Here row 17 is highlighted.

Cell – data in each of the rectangles that represent column and row is called a cell. Here row 17 and column E meet at a cell which is called as E 17

Excel Toolbar

2. Calculation

Use Excel to calculate basic mathematics operation - addition, subtraction, division and multiplication. Each number has to be in a separate cell as shown in the below image. Any calculation in Excel starts with = (equals to sign) followed by cell names with mathematics operation between them without any space.

Excel Calculation

Apart from basic calculations, Excel can also perform multiple mathematics operations. You need to separate each operation into brackets as seen in cell E7 in the above image.

3. Data Filter

Data filters allow us to get data that matches our desired criteria. Let's say we want to shortlist a car from the list of five cars as mentioned in below image.

First column has car model which you can sort either by A to Z or Z to A alphabetical order. You can also further shortlist text filters using additional options. Let's say you only want a Honda car. In this case, you will have text filter which contains text "Honda."

Similarly, you can even sort based on colour, car price and availability. So when you apply text filter as Honda, colour Grey and price between 5 lacs to 7 lacs, you get Honda Brio but this model is discontinued in availability status.

This example mere used 5 car models. You can break complex data involving 1000s data points in few minutes.

Excel Data Filter Tutorial

4. Data Validation

Data validation is very important feature which keeps data feeding in a standardised format and minimises changes of recording incorrect data. In the below image, one needs to feed in appropriate parent company's name for a selected brand and categories. With data validation, one gets a drop down which shows all the possible parent company that are relevant to this data. This helps to record data in a uniform way since it removes manual input. Without data validation, one could have written parent company of Jio Savn as Reliance or Reliance Group. Not only now we have two different names under Reliance, there could also be typo errors and inconsistency with lower case, upper case and title case.

Data Validation

Below tutorial explains steps to create and use data validation.

Step 1: create fields required for data validation drop down - refer yellow highlighted cells in column E

Step 2: select cells in column which needs data validation, then click on Data from ribbon bar and then click on Data Validation. From the drop down, click again Data Validation. Validation criteria - Allow -> "List" and source will be cell range where you have mentioned required field inputs in step 1.

Step 3: Apply this criteria

Step 4: Your data validation is applied. Now choose data fields from the drop down.

Data Validation Tutorial

5. Data Grouping

Groups allow us to view and hide unnecessary details easily from either columns or rows. In addition to that, we can also use groups to analyse data that belongs to a common category. Let's illustrate this with an example. Agency was reviewing their weekly marketing spent for their real estate clients and came up with the below data. They had advertised on 7 websites to facilitate sale of flats. With help of data grouping, agency can easily analyse total leads from branding and total leads from lead gen campaign. It can also get fair idea what could be average leads for branding and that for lead gen campaign.

Data Grouping

Data Grouping Outcome

Below is the tutorial to group and ungroup a data.

Data Grouping Tutorial

With the understanding of calculation, data filter, data validation and data grouping, you can surely work with ease on excel.

Subscribe to the newsletter and get notified when the next part is out on the blog.