Convert Date number to Date format
Removing the space from beginning and end of a cell value
Arranging Raw Data in an organized format
Create and name a table
Using VLOOKUP function
Pivot Table
Condition formatting to highlight duplicate values
To Get VLOOKUP + 1 value
1 ALT+ES
Paste special > Values
Find & Replace
3 ALT+DPP
Pivot Table & Pivot chart Wizard
3.1 CTRL+PageUp or Page Down
To toggle between sheets of a workbook
4 Formula to convert the date number to date format
=TEXT(A1,"dddd d, mmmm")
5 While you copy data from some source and find that a value in a cell have a space in the beginning or the end, please use the below formula to remove it.
=TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32)))
6 For arranging the raw data in a format that you might require
Store your raw data in Sheet1
Create a new sheet (say, Sheet 2) for capturing the result
Name Cell A1 and B1 in Sheet2 as Data and Year respectively
=OFFSET(Sheet1!A1,ROW(Sheet1!A1)1,0)
In Cell B2, pls input
=OFFSET(Sheet1!A1,ROW(Sheet1!A1),0)
To copy the above 2 formulas to the other cells in Col A and Col B of Sheet2
Arranged Data
7 To create and name a table
Select the data and press CTRL+L and then ok. Under the Table Tools on Design Tab, you will see the default table name which you can change by simply typing there the new name and pressing enter. To view the design tab/Table Tools, your cursor should be somewhere in the table.
8 VLOOKUP
VLOOKUP locates the value in the first column of a table and returns a value in the same row from another column in the table array.
Example
We have the below table with us in the sheet of a Workbook namely sheet3.
In the sheet 2 we have a list of KDs and now for those KDs we want to pull the respective ticker. So we will use the VLOOKUP function for same.
=VLOOKUP(A2,Sheet3!A:D,2,FALSE)
In this example formula the A2 is the reference of KD or lookup value that we want to look in the table which is in sheet3 so after putting the comma we will select that table, and then after a comma, we will put the number of column in which our required data is. So in this example the table has in total 4 columns A:D and our required data is in the second column so we will put 2 in the formula. Then after the comma we will type False or 0 which is to make sure that the values we are looking for which in our case is KD, is exactly matching. Press entry and you will find the required result there.
9 Creating Pivot Table
Pivot Tables in Excel are used for summarizing analyzing, exploring and presenting our data.
It can be used for summarizing data like finding Avg., Count or Sum of some value like Salary, Sales, or Time for each department, designation wise and with a filter of office.
Creating Pivot Table an example
We have the below raw data with us and now we want to calculate the Total Salary, designation wise for each department along total salary (all designations.) of each dept. The best way is to do that through a Pivot table.
Emp Name

Hire Month

Designation

Salary

Dept

Age

Chris Siedman

Feb08

AM

15000

B&T

25

Mary Elizabeth

Apr08

Analyst

13500

M&C

28

Scott Zunic

Jan08

S. Analyst

14500

Insurance

31

Robert Clark

Feb08

Manager

25000

M&A

23

Mathi Ramki

Mar08

AM

16000

CDS

24

Michael Wegener

Apr08

Analyst

10000

B&T

22

Aaron Teitelbaum

Feb08

S. Analyst

13500

M&C

25

Conor McDonnell

Mar08

Manager

28000

Insurance

28

Henry Dickson

Apr08

Manager

26500

M&A

31

Scott Sedlak

Feb08

AM

13000

CDS

23

Brendan McKinley

Feb08

Analyst

10000

CDS

24

Scott Cottrell

Jan08

S. Analyst

12000

M&A

22

Matthew Schultheis

Jan08

Manager

26000

CDS

25

Zeynep Leffler

Mar08

Manager

26500

B&T

28

Roger Ashworth

Apr08

AM

13500

M&C

31

Steps
Ø Select the Data –Go to Insert RibbonPivotTable
Ø A window will open, now chose where you want the Pivot report to be placed, New Worksheet or existing, if existing, specify the location by providing cell reference and then press OK.
Ø On your left hand you will see the Pivot Table Field List, If not showing then just click anywhere in the blank Pivot table, it will start showing.
Ø Now select the Row & Column Labels, here in our case it would be Dept. as row and Designation as Column. Drag the fields from the list in the respective section i.e. Column or Row.
Ø In the values section, select the field for which you want calculated results, in our case it would be salary. You will see the label there as ‘Sum of Salary’. In case if you want these numbers to be Avg. or Count, just click on the Drop down and the Value Field settings, there you can change the Sum to Avg. or whatever you want.
Ø Report Filter is the section where you can select a field for which you would be able to filter your overall results. Like in this case we can select the Filter as ‘Age’ for understanding purpose. The default filer would be ‘ALL’; we can change it to any age value.
Now see in the below snapshot, how organized our raw data is looking in the Pivot Table. Just practice it yourself and then you will feel the importance of it more
10 Conditional Formatting to highlight duplicate values
Through conditional formatting, we can easily identify the duplicate number or text values by highlighting them.
Simply select the data I.e. the column(s) and go to Home > Conditional Formatting and then Highlight Duplicates and Press ok, you will see the duplicate values highlighted, if any.
11 To Get VLOOKUP + 1 value
We can use the Index+Match function to find the value that is below the value that VLOOKUP finds.
The formula would be like =Index (value’s column, Match (search value, search column, 0) +1)
For below example in the snapshot the formula would be =INDEX(A1:A10,MATCH(A12,B1:B10,0)+1)