Some General but Useful Excel Learning

Excel Shortcuts

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

2-      ALT+EE

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

In Cell A2, pls input

=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

Raw Data

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	Feb-08	AM	15000	B&T	25
Mary Elizabeth	Apr-08	Analyst	13500	M&C	28
Scott Zunic	Jan-08	S. Analyst	14500	Insurance	31
Robert Clark	Feb-08	Manager	25000	M&A	23
Mathi Ramki	Mar-08	AM	16000	CDS	24
Michael Wegener	Apr-08	Analyst	10000	B&T	22
Aaron Teitelbaum	Feb-08	S. Analyst	13500	M&C	25
Conor McDonnell	Mar-08	Manager	28000	Insurance	28
Henry Dickson	Apr-08	Manager	26500	M&A	31
Scott Sedlak	Feb-08	AM	13000	CDS	23
Brendan McKinley	Feb-08	Analyst	10000	CDS	24
Scott Cottrell	Jan-08	S. Analyst	12000	M&A	22
Matthew Schultheis	Jan-08	Manager	26000	CDS	25
Zeynep Leffler	Mar-08	Manager	26500	B&T	28
Roger Ashworth	Apr-08	AM	13500	M&C	31

Steps

Ø  Select the Data –Go to  Insert Ribbon-PivotTable

Ø  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)