Mastering Advanced Excel

Table of Contents

1.Introduction

2.Absolute and Relative References

3.Date and Text related Functions

4.Vlookup and Hlookup

5.Match

6.Index

7.Data Validation

8.Pivot tables and EDA

1.INTRODUCTION

Excel is a very famous package that is widely used in the world in analyzing data, reporting, and automation of jobs in industries. Even though the most basic features, which include formulae, tables, and charts, are well known, Advanced Excel opens doors for more work and takes the productivity level to another dimension, offering more complicated techniques and helping users do complex analysis while dealing with immense data. Users can process huge datasets using PivotTables and VLOOKUP. Use macros and Data Validation can be done and complex statistical functions, including Power Query.

2.ABSOLUTE AND RELATIVE REFERENCES

Relative references, like A1, will shift for a cell when you copy across a formula. In contrast, absolute references like $A$1 will be immobile when you copy the formula and is the referenced cell will also be the same, no matter where the formula is moved or copied. These two functions control the way formulas operate once applied in more than one cell.

• Relative Reference:

  • Example: =A1 + B1

  • Adjusts automatically when copied to other cells.

• Absolute Reference:

  • Example: =$A$1 + $B$1

  • Stays fixed when copied or moved.

• 

• Mixed Reference:

  • Example: =A$1 + $B1

  • One part is absolute (either row or column), and the other is relative.

    

3.DATE AND TEXT RELATED FUNCTIONS

DATE related Functionalities

• Today → =today() =>Returns the current date

• Now → =now() => Return the current date along with the time

• Year → =year(D3) \=> Returns the year of that particular date which in D3 cell.

• Month → =month(D3) => Returns the month of that particular date which in D3 cell.

• Day → =day(D3) => Returns the day of that particular date which in D3 cell.

• Hour → =hour(D4) => Return the hour of that particular cell.

• Minute → =minute(D4) => Return the minute of that particular cell.

• Second → =second(D4) => Return the second of that particular cell.

  

  TEXT related Functionalities

• Text → “=TEXT(P3,”mm/dd/yy”)” =>10/09/24

• Substitute → =SUBSTITUTE(R3,”m”,”j”) => substitute “m” with “j” for the string present in that cell.

• Concatination → =Concatenate(R5,S17) => Merges the content in that 2 cells.

• Find → =find(“m”,R3) => find the index of “m” in that cell

• Len → =len(R3) => Prints length of the string present in that cell.

• Left → =left(P9,4) => prints 1st 4 chars in that cell.

• Right → =right(P9,7) => Prints last 7 chars in that cell.

• Mid → MID(P9,3,8) => prints the characters present from 3 to 8.

• Trim → =trim(P9) →Remove the space if exist in the cell.

4.VLOOKUP AND HLOOKUP

• VLOOKUP

  • VLOOKUP or Vertical Lookup is the most powerful function that looks up a value in the leftmost column of a given range and returns a value in the row that contains it in a specified column.

  • The syntax for this function is as follows:

    =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: The value that you want to find. table_array: Range containing the data. col_index_num: The column number you want to return a value from. range_lookup: TRUE for a match close to the value of the function, FALSE for an exact match.

$850 is our target in this table:

• =VLOOKUP(A6,A1:E11,4,0)

HLOOKUP

• HLOOKUP (Horizontal Lookup) looks up a value in the first row of a table and returns a value in a specified row below it. Apply this formula where your data is laid horizontally.

  Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: The value you are looking for

• table_array: This is the table that you will be looking through

• row_index_num: Is the row number from where you will retrieve the value.

• range_lookup: Optional ; true for an approximate match. FALSE is an exact match.

  $900 is our target in the above table:

• =HLOOKUP(D1,A1:E11,8,0)

5.MATCH:

The MATCH function looks for any certain value in an array and comes back with its relative location. The function is mostly used to search for values in lists or other combinations. The more advanced lookup function is the INDEX function.

Syntax: =MATCH(lookup_value, lookup_array, [match_type])

lookup_value The value to search for.

lookup_array: The range to be searched.

Match Type: 0 means match exactly; 1 means closest smaller value; and -1 means closest larger value.

26 is our target in the above table => For row

\=match(B6,B1:B11,0)

Age column is our target in the above table =>For column

\=match(B1,A1:E1,0)

6.INDEX:

The INDEX function returns the value of a cell based on its position within a specified range. It takes a row and column number as input to locate the desired value.

Syntax:
=INDEX(array, row_num, [column_num])

78 is our target in the above table:

\=index(a1:e11,10,5)

7.DATA VALIDATION

Data Validation helps you regulate the kind of data that can enter a cell-thereby preserving accuracy and consistency. The input could be restricted to numbers, dates, lists, or ranges. It also enables you to generate customized error messages when invalid data is entered.

Select the cells you which you need he restriction → Data → Data validation → popup → In popup we will be having 3 tabs (setings,input,error alert) → fill the feilds as you required →click “OK”

By the above process the cells you selected will get restricted based on your conditions given in the popup and it also show the input message to the user when user click any of that restricted cells and also he can read custom error message when he enters the value and come to the next cell.

The popup model is like:

The text visibility when the user clicks of the restricted cells be like:

That is the input message we’ve given in the popup.

Even though if the user enters the other value which is not in between 12 and 15 he will get a error popup (which we gave in the error alert) that is like:

We can select any data type in the settings and restrict the cells we selected and the data types we have here are:

Based on the selected data type we’ll get the fields beneath.

8.PIVOT TABLE and EDA

A pivot table is a powerful tool that can be used to summarize, analyze, and reorganize very large datasets. It transforms raw data into meaningful insights by grouping, filtering, and calculating metrics, such as sums, averages, and counts.

Using just a few clicks, dynamic reports can be built and trends of data explored easily without having to execute complex formulas.

Load your excel data on which you will be performing the pivot table → insert →pivot table →popup (asks for the new worksheet/existed one) → prefer to go with new worksheet → click “ok” → a new worksheet with all the column names at the right side will appear as “PIVOT table fields”

The popup when we click on the PIVOT table option is like:

The columns appears in the manner:

Based on our selection of the columns the pivot table appear in the worksheet and here for example:

The pivot table for my selected columns is :

Insert slicer

A Slicer is a graphical filter to make the filtering of data in Pivot Table easy. It allows buttons to be selected by the user. It is a quick and intuitive way for interactive analysis.

How to insert a Slicer:

Click on the Pivot Table. PivotTable Analyze → Insert Slicer. Select the fields you want to filter, then click OK.

Recommended charts in Excel

One of the features available for you in Excel is Recommended Charts. This feature will be able to recommend the most appropriate chart types for your data. It will make it easier for you to quickly plot patterns and trends without your having to manually choose a chart type.

To access it:

Select the data range. Now, go to the Insert Tab → Recommended Charts. It will show chart options based on the data structure within the Excel worksheet, such as Column, Line, or Pie.

Example:

We can also add slicer to this for the filtering/comparing the things we want in our analysis.

We also have many types of stylings in the visualization we can apply it from the brush that appears at the top right of the graph when we click on that.