About the Author The Author of this book has Bachelors in Engineering (Comp. Sc.), Masters in Business Administration (E Commerce) and is a certified Analyst. He has working Experience from one of the world’s top Management Consulting MNC and US top Fortune Healthcare Company. For any suggestion, consulting or queries you can email at
[email protected] [email protected].. You can find me on – Linkedin - linkedin.com/in/sumitsaxenaa Facebook - fb.com/sumitsaxenaa
Table of o f Contents Cont ents
About the Author The Author of this book has Bachelors in Engineering (Comp. Sc.), Masters in Business Administration (E Commerce) and is a certified Analyst. He has working Experience from one of the world’s top Management Consulting MNC and US top Fortune Healthcare Company. For any suggestion, consulting or queries you can email at
[email protected] [email protected].. You can find me on – Linkedin - linkedin.com/in/sumitsaxenaa Facebook - fb.com/sumitsaxenaa
Table of o f Contents Cont ents
Chapter 1
1. Introduction to MS Excel 2. Around the Excel Sheet 3. Operators in Excel 4. References in Excel 5. SUM, AVERAGE, PRODUCT, POWER (AN INTRODUCTION TO EXCEL MATH FUNCTIONS) 6. PROPER, UPPER, LOWER, LEN, TRIM (YOUR FIRST FIVE TEXT FUNCTIONS) 7. YOUR FIRST 10 EXCEL KEYBOARD SHORTCUTS 8. Format Painter in Excel 9. Concatenate 10. Count, Counta, Countblank, Countif (Counting Functions in Excel)
Chapter 2
1. Important Date Functions (TODAY, NOW, YEAR, MONTH, DAY, DATE ) 2. Age Calculations 3. Calculate End of Probation period using EDATE & WORKDAY 4. Calculate no. of working days between two dates (Networkdays Function) 5. Common Error types in Excel and error handling by Iferror Ife rror function. 6. Substitute and Find 7. Left, Right and Mid 8. Common IS Functions : Istext, Isnumber, Isnumber, Isblank, Iserror Ise rror 9. Len and Text
Chapter 3
1. Validation 2. Text to Column 3. Sheet Protection, Share workbook, Workbook Protection 4. Formatting in Excel
5. Conditional Formatting 6. Type of calculation 7. How to remove the duplicate value 8. Freeze Pane
Chapter 4
1. Max, Min, Large, Small 2. Percentage Calculation 3. Efficiency, Growth Rate 4. Pivot Table 5. EXCEL ROUNDING FUNCTIONS (ROUND, ROUNDUP, ROUNDDOWN, MROUND) 6. More Shortcuts in Excel
Chapter 5
1. Vlookup, Hlookup,Lookup 2. Index and Match 3. Name Manager 4. Offset and Indirect 5. GetPivotData
Chapter 6
1. Sumif, Sumifs & Countif, Countifs 2. Paste Special Command 3. AutoFill 4. Hyperlinking 5. Sorting 6. Filter and Advance Filter
Chapter 7
1. Logical Functions (AND, OR, NOT, IF) 2. Array Function (SumProduct, Sum, if, Max, Min, Average) 3. Database Function (Dsum, Dcount, Dcounta, Dmax, Dmin, Daverage)
Chapter 8
1. SparkLines (Line, Column and Win/Loss) 2. Charts and Its Types(Column, Line, Pie, Bar, Area, Scatter etc.) 3. Design & Layout 4. Format 5. Dashboard & KPI(Key Performing Indicators) 6. Pareto Chart 7. Camera Tool
1. Introduction to MS Excel
Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows, Mac OS X, and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language
called Visual Basic for Applications Is easy to learn and use but cannot handle very big size data.
2. Around the Excel Sheet
Number
1
2
3
4
Contains
Description
Title Bar
Contains the name of the file that is currently open.
Menu Bar
Contains various commands that can be performed by Excel.
Tool Bar
Contains buttons for some commonly commanded tasks.
Formula Bar
Displays the content of an active cell. The left hand side of the bar is the“address“ of the active cell.
Column
5
Label
Contains the heading of the columns.
6
Row Label
Contains the heading of the rows.
7
Sheet Area
Enters and stores the actual data (i.e. text, date and numbers).
8
Sheet Tab
Gives identities to sheet which is currently active.
Status Bar
Gives a status update of the entire currently active sheet.
9 .
3. Operators in Excel
There are four different types of Operators in Excel : Arithmetic, Comparison, text concatenation, and Reference.
Arithmetic operators
To perform basic mathematical operations, such as addition, subtraction, multiplication, or division; combine numbers; and produce numeric results, use the following arithmetic operators.
Arithmetic operator
Meaning
Operation Performed
+ (plus sign) Addition
3+3
– (minus sign)
Subtraction
3–1
-
Negation
-1
Output
6
2 -1
* (asterisk)
Multiplication
3*3
/ (forward slash)
Division
3/2
% (percent sign)
Percent
20%
^ (caret)
Exponentiation 3^2
9
1.5
0.20 9
Comparison operators
You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value — either TRUE or FALSE.
Comparison operator
Meaning
Example
= (equal sign)
Equal to
A1=B1
> (greater than sign)
Greater than
A1>B1
< (less than sign)
Less than
A1
>= (greater than or equal to sign)
Greater than or equal to
A1>=B1
<= (less than or equal to sign)
Less than or equal to
A1<=B1
<> (not equal to sign)
Not equal to
A1<>B1
Text concatenation operator
Use the ampersand (&) to concatenate (join) one or more text strings to produce a single piece of text.
Text operator
Meaning
Connects, or concatenates, two & values to produce one continuous (ampersand) text value
Example
“North”&“wind” results in “Northwind”
Reference operators
Combine ranges of cells for calculations with the following operators.
Reference Meaning operator
Example
: (colon)
Range operator, which produces one reference to all the cells between two references, including the two references.
, (comma)
Union operator, which combines SUM(B5:B15,D5:D15) multiple references into one reference
(space)
Intersection operator, which produces one reference to cells common to the two references
B5:B15
B7:D7 C6:C8
Note: AND,OR etc are logical functions not logical operators as they return value on passing arguments
4. References in Excel
Three Types - Absolute, Relative and Mixed references
Relative references A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy or fill the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy or fill a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.
Absolute references An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, so you may need to switch them to absolute references. For example, if
you copy or fill an absolute reference in cell B2 to cell B3, it stays the same in both cells: =$A$1.
Mixed references A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy or fill the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy or fill a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1.
5. SUM, AVERAGE, PRODUCT, POWER (AN INTRODUCTION TO EXCEL MATH FUNCTIONS)
Formula
Input
Output
Sum
=SUM(3,4,5)
12
Average
=AVERAGE(2,4,6)
4
Product
=PRODUCT(2,3,4)
24
Caret
=POWER(2,2)
4
6. PROPER, UPPER, LOWER, LEN, TRIM (YOUR FIRST FIVE TEXT FUNCTIONS)
7. YOUR FIRST 10 EXCEL KEYBOARD SHORTCUTS
1. Ctrl+Z Undo 2. Ctrl+Y Redo 3. Ctrl+C Copy 4. Ctrl+V Paste 5. Ctrl+S Save 6. Ctrl+N New Workbook 7. Ctrl+O Open 8. Ctrl+F Find 9. Ctrl+P Print 10. Ctrl+A Select All
8. Format Painter in Excel
Format Painter copies formatting from one place and applies it to another quickly. It is usefull in Tables, Value and Header formatting. Double click the Format Painter button to apply the same formatting to multiple cells
9. Concatenate
CONCATENATE is a text function used to join two or more text strings into one string.
Syntax: CONCATENATE(text1, [text2], …)
10. Count, Counta, Countblank, Countif
Count - Count only numeric value in a range not text Counta- Calculates all except blank and including space in a range Countif - Count no. of cell within a range that meet the given condition. Countblank - Count blank cells space will be counted as zero.
CHAPTER 2 1. Important Date Functions (TODAY, NOW, YEAR, MONTH, DAY, DATE )
A TODAY
NOW
B Returns Current Date
C =TODAY()
Returns Current Date and Time =NOW() Returns numeric month from
D 11/29/2015 11/29/2015 17:43
MONTH date
=MONTH(D4)
YEAR
Returns year in YYYY format from date
=YEAR(D4)
DAY
Returns numeric day from date
=DAY(D4)
DATE
=DATE(YEAR,MONTH,DAY) =DATE(2015,4,1)
11
2015 29 4/1/2015
2. Age Calculations
=datedif(d1,d2,“x”) d1- old date d2- new date x- It can be Y, M, D, YM, MD Y- Return no of year between 2 dates M- Return no. of months between 2 dates D - Return no. of days between 2 dates YM- Return no. of month in current year MD- Return no of days in current months
Example – Date1 - 10/29/1992 (Written in cell C14) Date2 - 10/25/2015 (Written in cell C16) =CONCATENATE(DATEDIF(C14,C16,“Y”),” Years “,DATEDIF(C14,C16,“YM”),” Months “,DATEDIF(C14,C16,“MD”),” Days”) 22 Years 11 Months 26 Days
3. FIND END OF PROBATION PERIOD USING EDATE
Returns no of month before or after the start date. =EDATE(Start_date,Months)
, Months can be positive or negative
So type in “=EDATE(“, click on the cell that contains their start date, type a comma, then enter the number of months. 3 months? 6 months? Type a 3 or a 6 or whatever it is. Then close your brackets “)” and press enter. Done.
4. Calculate no. of working days between two dates (Networkdays Function)
Calculate employee benefits that accrue based on the number of days worked during a specific term. The calculation includes all weekdays (Mon - Fri) =NETWORKDAYS(startDate,endDate,[holidays])
holidays - [optional] A list of one or more dates that should be considered non-work days. E.g- 25 Sept 2015 is written as 25 Note that the start_date, end_date and [holidays] arguments should be input as either: References to cells containing dates or Dates returned from formulas. - If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2012,5,23) for the 23rd day of May, 2012. Problems
can occur if dates are entered as text. 5. Common Error types in Excel and error handling by Iferror Ife rror function.
#NAME? error The #NAME? error occurs when Excel does not recognize text in a formula. e.g- SU to SUM
#VALUE! #VALUE! error Excel displays the #VALUE! error when a formula has the wrong type of argument.
#DIV/0! error Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.
#REF! error
Excel displays the #REF! error when a formula refers to a cell that is not valid. e.g- Delete B column it will give error
6. Substitute and Find
=substitute(text,old, new) Substitute old text with new text e.g- Singh is replaced with kumari
“=SUBSTITUTE(A89,““Singh””,““Kumari””) “ Arti Kumari Chauhan =Find(find_text,text)
Find the position of one text within other text
e.g - g is at 9 position in Arti Singh Chauhan 9
=FIND(“g”,A89)
7. Left, Right and Mid
8. Commomon IS Functions : Istext, Isnumber Isnumber,, Isblank, Iserror Is error
IS functions, checks the specified value and returns TRUE or FALSE FALSE depending on the outcome. e.g- isblank
9. Text
=text(value,format_text)
FORMATS -
6/5/09 2:34 AM 6/7/12 12:34 12:34 PM
- Convert a value to specific text format
11/10/2011
Monday, October 10, 2011
Oct/11
10/12/11 12:00
06/05/2009
Friday, June 05, 2009
Jun/09
06/05/2009 02:3
=TEXT( =TEXT(A12 A125,“m 5,“mm/d m/dd/y d/yyyy yyy”) ”)
=TEXT(A125,“dddd, mmmm mm mm dd, dd, yyyy yyyy”) ”)
=TEX =TEXT( T(A1 A125 25,“m ,“mmm mm/y /yy” y”))
=TEXT(A125,“ hh:mm:ss AM/P
5000 5000 =TEX =TEXT( T(A1 A128 28,“ ,“00 00.0 .00” 0”))
=TEX =TEXT( T(A1 A128 28,“ ,“00 00.0 .00” 0”))
5000
=TE =TEXT(A1 (A129,”. ,”.00$”)
=TEXT(A129,”. ,”.00$”)
CHAPTER 3
1. Validation
Data Validation - It allows you to define validation on cells for entering value. Common Validations are - List, Date, Time, Text Length etc.
List Validation - It will create a drop down containing a range or defined values
Select the Cell/Range to Validate -> Goto Data -> Data Validation -> select the Validation criteria here and press OK. Now cell will take only defined values
Circle Invalid Data - It will put cirlcle on cell containing Invalid values, when validation is defined after entering values
2. Text to Column
To separate the contents of one Excel cell into separate columns, you can use the ‘Convert Text to Columns Wizard’. For example - when you want to separate a list of full names into last and first names.
Full Name
Last
First
Polamalu, Troy
Polamalu
Troy
Johnson, Andre
Johnson
Andre
Revis, Darrelle
Revis
Darrelle
1. Select the range with full names. 2. On the Data tab, click Text to Columns. The following dialog box appears. 3. Choose Delimited and click Next. 4. Clear all the check boxes under Delimiters except for the Comma and Space check box. 5. Click Finish.
Note: Commas and spaces are common delimiters. You may have other delimiters in your data.
3. Sheet Protection, Share workbook, Workbook Protection
(1)Worksheet Level Protection and Locking/Unlocking of Cell Format Cells -> Protection
NOTE: Make sure to lock the cells before you protect the sheet or document. Once a sheet or a document has been protected, you cannot access menu selections that allow you to make changes to cells.
In Menu bar goto Review - Protect Sheet -> Give Password and select/unselect the various access for user
(2)Workbook Level Protection
You can prevent a workbook from having its structure and windows modified or resized by another user. Structure Prevents the user from changing the order of the sheets within a workbook. This includes adding or deleting worksheets. Windows Prevents the user from being able to resize or move the window.
(3)File Level Protection To password protect Excel file - Save as - Tools - General Options and can give password here to open and edit.
OPTIONAL: If you would like Excel to recommend that this file be opened as a read-only file each time it is opened, select Read-only recommended HINT: Read-only files can be modified, but the changes cannot be saved without creating a new file. If you no longer need to password-protect the file, you can remove the password by going to save As -> Tools -> and delete the current password, save it and replace old file
4. Formatting in Excel
Formatting is avaiable under Home Tab in Excel -
Format Painter - Used to Copy formatting from one Cell/Range to other. Double click this option to apply multiple time Font Formatting - It consists of Font Name, Size and Bold/Italics/Underline option
Border Formatting - It allows you to modify cell borders Text Formatting - It has various alignment option and Wrap Text option Number Formatting - It gives various options of formatting Number like Currency, Percentage, Decimals etc. Table Formatting - Format table data, various styles available to choose from.
Cell Formatting (Cell Styles) - It allows to adjust height, width, insert and delete options
You can do formatting manually, by selecting fonts, font color and size, background colors and borders, or you can do the formatting quickly and automatically using styles.
A style is a mixture of formatting that you can apply over and over, like paint. You can use -
Modifying a Built-In Style OR Creating and Applying a Custom Style
5. Conditional Formatting
Highlight Cells Rules
Highlight cells according to selected condition
Top/Bottom Rules
Highlight cells with Top- Bottom rule. E.g - Top 5, Bottom 5 etc. Applicable on numeric values
Data Bars
Show the values of cell with a databar to give graphical view
Color Scales
Shade of the color reprent the value in cell
Icon Sets
Display the value of cell with the help of icon sets
New Rules, Clear Rules and Manage Rules allows you to create, delete and modify formatting rules
6. Type of calculation
In a Excel sheet Formulas Calculation have 3 options 1). Automatic - Calculates every time any formula/text is entered 2). Manual - Calculates only when a sheet is refreshed or reopened. This option is preferred when data is huge to prevent sheet from large calculation during work.
3). Automatic Except for Data Tables - Calculation is automatic except for Data Tables
7. How to remove the duplicate value
Using Remove Duplicate, it will delete duplicate from last and keep the first data only. Goto Data - Remove Duplicates - Select fields for which you want to remove duplicate and click OK.
8. Freeze Pane
Used to keep an area of a worksheet visible while you scroll to another area of the worksheet, you can lock specific rows or columns in one area by freezing or splitting panes. You may want to see certain rows or columns all the time in your worksheet, especially header cells. By freezing rows or columns in place, you’ll be able to scroll through your content while continuing to view the frozen cells.
1. Select the row below the row(s) you want to freeze. 2. Click the View tab on the Ribbon. 3. Select the Freeze Panes command, then choose Freeze Panes from the drop-down
menu.
The rows will be frozen in place, as indicated by the gray line. You can scroll down the worksheet while continuing to view the frozen rows at the top. Repeat the same for column freezing To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu.
CHAPTER 4
1. Max, Min, Large, Small
=MAX(n1,n2,….)
Returns largest value in set of values ignoring logical values and text =MAX($G$3:$G$9)
7
=MIN(n1,n2,….)
Returns smallest value in set of values ignoring logical values and text
1
=MIN($G$3:$G$9)
Returns the Kth largest value in =LARGE(values,K) data set =LARGE($G$3:$G$9,2) 6
Returns the Kth smallest value =SMALL(values,K) in data set =SMALL($G$3:$G$9,2) 2
2. Percentage
Calculation
Percentage calculation have 3 major type -
Percentage as a Proportion E.g. if, Out of 180 questions John answer 135 correctly, what is John’s percentage score ? =135/180
75%
Percentage Change E.g. Sales of Iphone is $12,50000 in January and $15,00000 in February, what is the percentage increase in Feb, compared to last month? =1500000/1250000
120%
Decrease/Increase by % E.g. Salary of Mary increased by 30%, her monthly salary was 10,000 dollar, calculate new monthly salary =old_salary(1+.3)
13000
Eg - Annual CTC - 6 lac, Basic - 30% of CTC, PF - 12% of Basic, Calculate monthly PF contribution ? =(600000/12)*0.3*0.12
1800
3. Efficiency, Growth Rate
Efficiency Ratio of the effective or useful output to the total input in any system. It is usually expressed in percentage. Total Work Hours - 8
Efficiency
No. of hours worked - 10
Efficiency
=10/8
125%
Growth Rate (1). Average Annual Growth Rate You have to calculate the annual growth rate of every year with the formula = (CurrentYearValue - PreviousYearValue) / PreviousYearValue, and then average these annual growth rates
(2). CAGR (Compound Annual Growth Rate)
CAGR is similar to Rate of Compound Interest calculated for Loans. To calculate CAGR we have many ways, two are common -
1) CAGR = (LastYearValue/FirstYearValue)^(1/TotalYears)-1 Don’t worry its similar to (A/P)^(1/N) – 1 which is derived from A = P * (1+R/100)^N =(915/518)^(1/9)-1
6.5%
2) CAGR = IRR(Range of all values) 3) CAGR = RATE(NoOfYears,,-FirstYearValue,LastYearValue) parameter blank
* Leave 2nd
=RATE(nper,pmt,pv,[fv],[type],[guess]) nper – the number of periods pmt – the payment per period pv – present value fv – future value type – whether the interest is calculated at the beginning or end (default) of the period guess – a user input that can be used if the iterative calculation does not converge on a rate 4. Pivot Table
Pivot Table is one of the powerful feature of Microsoft Excel. It is used to summarize the huge data with necessary fields only. For e.g – In below data we can summarize it using
pivot table to get different views. We can COUNT the no. of employees Department wise, SUM the salary department wise etc.
Pivot Tables - Field List, Report Filter, Row Label, Column Label, Value Field
Slicers in Pivot Table Calculated Field
5. EXCEL ROUNDING FUNCTIONS (ROUND, ROUNDUP, ROUNDDOWN, MROUND)
Used to round figure the nos.
ROUND(number,No of digits)
=ROUND($B$80,2)
1234.57
ROUNDUP(number,No of digits)
=ROUNDUP($B$80,2)
1234.57
ROUNDDOWN(number,No of digits)
=ROUNDDOWN($B$80,2) 1234.56
MROUND(number,multiple) =MROUND($B$80,10)
1230
ROUNDUP rounds a no. up while ROUNDDOWN rounds the no. down towards zero. MROUND, or Multiple Round, rounds your number to the nearest multiple of your choice. So above we decide to round to the nearest 10, and it uses “standard” rounding and returns the figure up or down to the nearest multiple of 10
6. More Shortcuts in Excel
EXCEL KEYBOARD SHORTCUT COMBOS
1. Change font type and font size – Alt+HFF [type font name], Alt+HFS [type font size] 2. Ungroup all grouped rows and columns – CTRL+A, Alt+AJ 3. See what part of a formula works out to – F2, [optionally select part of the formula] F9, Escape
4. All borders, with a thick border around the outside – Alt+HBA, Alt+HBT 5. Close a workbook and re-open it – Alt+FC, Alt+FR1
KEYBOARD SHORTCUTS FOR TEXT ALIGNMENT
Using HOT keys in combination with ALT key for Text Alignment ALT + H + …. AL - Align Left AC - Align Centre AR - Align Right AT - Align Top AM - Align Middle AB - Align Bottom 5 EXCEL KEYBOARD SHORTCUT COMBOS
1. Apply autofilters and freeze panes on top row – CTRL+Shift+L, Alt+WFF 2. Unmerge all cells in workbook – CTRL+A then Alt+HMC 3. Add a new worksheet and name it – Shift+F11, Alt+HOR 4. Close the current workbook and re-open it – Alt+FC, Alt+F,1 (or in 2010 its Alt+FR1) 5. Select column and remove/highlight duplicates – CTRL+Space,Remove:Alt+AM or Highlight: Alt+HLHD
EXCEL KEYBOARD SHORTCUTS – CONTROL WITH SYMBOLS
CTRL + ____ ` – Toggles between displaying cell values and formulas in the current worksheet. ~ – Cell format is General. ! – Cell format is Number with two decimal places, commas, and minus symbol for negative values. @ – Cell format is Time hour and minute, and AM or PM. # – Cell format is Date (dd-mmm-yy). $ – Cell format is Currency (with 2 decimal places and negatives in brackets). % – Cell format is Percentage (with no decimal places). ^ – Cell format is Exponential with two decimal places. & – Adds outline border on selected cells. * – Selects the range of cells around the active cell. ( – Unhides all hidden rows in the selection. ) – Unhides all hidden columns in the selection. – – Delete the selected cells. _ – Removes outline border on selected cells. + – Insert cells. ; – Enters the current date. : – Enters the current time. ‘ – Copies a formula from the cell above. ” – Copies the value from the cell above.
EXCEL KEYBOARD SHORTCUTS WITH FUNCTION KEYS
F1 – Excel Help F2 – Edit active cell F3 – Brings up the Paste Name box – allowing you to easily refer to any named ranges F4 – Repeats the last action (or in a formula cycles through absolute reference options) F5 – Go To F6 – Flicks through the worksheet, ribbon, task pane, and zoom controls F7 – Spell checker F8 – Toggle Extended Selection – keeps highlighting cells until you switch it off F9 – Calculate now (on active worksheet) F10 – Just like pressing Alt F11 – Create chart – uses range around the active cell F12 – Save As EXCEL KEYBOARD SHORTCUTS WITH CONTROL + Nos.(0-9)
1 – Format cells 2 – Bold 3 – Italic 4 – Underline 5 – Strikethrough 6 – Hide/Unhide Object 7 – Hides or displays the standard toolbar 8 – Hides or displays the outline symbols 9 – Hide selected row(s)
0 – Hide selected column(s)
A-Z OF EXCEL KEYBOARD SHORTCUTS WITH CONTROL + Alphabets(AZ)
A – Select All B – Bold C – Copy D – Fill down E – N/A F – Find G – Go to H – Replace I – Italic J – N/A K – Insert hyperlink L – Create table M – N/A N – New workbook O – Open P – Print Q – N/A R – Fill across S – Save T – Create table U – Underline V – Paste W – Close workbook X – Cut Y – Redo Z – Undo
CHAPTER 5
1. Vlookup, Hlookup,Lookup
LOOKUP(lookup_value, lookup_vector, [result_vector]) LOOKUP is an old version of VLOOKUP and HLOOKUP It looks in a one-row or one-column range. Values in lookup_vector must be sorted in ascending order else it may not give exact match.
LOOKUP is not used now and Office introduced HLOOKUP and VLOOKUP with more features.
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
TRUE/1 - Assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default parameter in vlookup. FALSE/0 - Search for the exact value in the first column(or lookup value, not the exact
match of value you are fetching by giving column index no.)
Best Practices for Vlookup and Hlookup 1. Use absolute references in lookup range to fill-down a formula so that it always looks at the same exact lookup range. 2. Don’t store number or date values as text, Otherwise, VLOOKUP will not work. 3. Sort the first column of table_array when using match type as 1. 4. Make sure the data in the first column doesn’t have leading spaces, trailing spaces, inconsistent use of straight ( ‘ or ” ) and curly ( ‘ or “) quotation marks, or nonprinting characters Use CLEAN() and TRIM() to correct data. 5. IF lookup value is text use wildcard character ? And *. A ? matches any single character and * matches any sequence of characters
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Searches for a value in the top row of a table or an array of values Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows
5
Cost of “File Folders” using HLOOKUP.
=HLOOKUP($C$32,$C$32:$C$40,7,0)
Product Table
2. Index and Match
=INDEX(range,row_num,[col_num]) For e.g =INDEX($A$32:$D$40,2,3) will return 13 product table
as 13 is at 2nd row and 3rd column in
E.g – Have a 2X2 matrix {1,2;3,4} =INDEX({1,2;3,4},2,2) will return 4, as 4 is at 2nd row and 2nd col in matrix given in range =MATCH(lookup_value,range,[match_type])
MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. Works both horizontally and vertically
Pens
42
3
Returned 3 as Pen is at 3rd no. vertically
2
Returned 2 as 42 is at 2 no. horizontally
In other words,It gives row no. when applied to vertical range and column no. when applied to horizontal range. Hence match can be used in second parameter in Index function to return row number. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself INDEX functions grabs a cell from an array, and the MATCH function contributes the row and/or column position hence they can be used together to perform vlookup task MATCH does not distinguish between uppercase and lowercase letters when matching text values.
If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Match_type Behavior
0
MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_arrayargument can be in any order.
1 or omitted
-1
MATCH finds the value that is less than or equal to lookup_value. The values in thelookup_array argument must be placed in ascending order. MATCH finds the smallest value that is greater than or equal tolookup_value. The values in thelookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.
Example –
=MATCH(39,B71:B74,1)
=MATCH(41,B71:B74,0)
2
Match Type 1
4
Match Type 0
Match Type =MATCH(40,B71:B74,-1) #N/A (-1)
3. Name Manager
By using names, you can make your formulas much easier to understand and maintain. You can define a name for a cell range, function, constant, or table in Name Manager It is available under Formula menu or you can use CTRL + F3 to open Name Manager
Goto New - Give Name and select range for that name e.g - Name - Disc , Range = Cell containing discount value and OK Quick Tip - Name Manager is not just for defining range only, you can define a single constant value also which is frequently used in your workbook and value will change in future. For e.g - In a shop items 10% discount is applied on items and after 1 month shopkeeper decided to sell them on 5% so it ll be hard to update 5% in every item value,so define it in name manager or by giving cell reference Use these Name range directly in your all formulas instead of selecting ranges separately
4. Offset and Indirect
Offset( BaseRef, rows, columns, [height], [width] ) Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
Note - For array, select whole range first write formula in formula bar and press CTRL+SHIFT+ENTER
Sum using Offset function
=SUM(OFFSET(D94,1,-2,8))
Starting from Amt column it moves two column left for 274 column value = -2
=SUM(OFFSET(A94,1,1,8,1))
274 Default width taken as 1
INDIRECT(ref_text, [a1]) Returns the reference specified by a text string
=INDIRECT(“B”&A1) Combines “B” with the value in A1, which is 10. This, in turn, refers to cell B10 value. 208.05 5. GetPivotData
Returns data stored in a PivotTable table according to specified field and you can put filter on other Field’s Item as well. =GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)
Data_field -
Field for which the data you want to retrieve.
Pivot_table - A reference to any cell, range of cells, or named range of cells in a PivotTable report.
Field1, Item1, Field2, Item2 - Optional.
Row Labels
Sum of Amt
File Folders
40
Paper
390
Pens
338
Staplers
1072
Grand Total
1840
Getpivotdata to fetch Amt for Paper
=GETPIVOTDATA(“Amt”,F131,“Product”,“Paper”) Where F131 is refers to any cell in pivot 390
CHAPTER 6
1. Sumif, Sumifs & Countif, Countifs
=sumif(range,criteria,[sum_range])
Add the cells specified by given condition or criteria
Add the cells specified by given conditions or =sumifs(sum_range,criteria_range1,criteria1….) criterias
=countif(range,criteria)
Count the cells that meet given condition or criteria
=countifs(criteria_range1,criteria1,,,)
Count the cells that meet given conditions or criterias
Add example 2. Paste Special Command
Shortcut to paste special window - Copy the text and then press Alt + E + S
A- All, F - Formula, V - Values, C - Comment, N - Validation, W - Column Width, X - All except borders S - Substract, D -Add, M - Multiply Skip blank - It will not paste blank value and old values will be there.
3. AutoFill
AutoFill can be a handy tool to use, when you want to complete a known sequence like days of the week, months, and even formulas. Position your mouse pointer to the bottom right of the B3 cell When you can see the AutoFill cursor(right corner in black), hold down your left mouse button and drag to the right or down.
E.g - Drag Jan cell to right it will autofill rest months in sequence.
Jan
Feb
Mar
Apr
May
4. Hyperlinking (CTRL+K)
Hyperlinking is used for quick navigation in Excel application and other program also like webpage, word file, outlook mail, image etc. It can be used with shapes also e.g arrow. E.g- Click below link to goto Index sheet and screenTip of “Click to goto Home” is also given here. Contact US is used to send mail to Admin. In screentip, by default path is stored, edit it and give custom message.
Index
Contact US
5. Sorting
Sort below data according to ranking and then by Name if rank is same (Hint: Add first level of filter as Ranking and another level as Name)
Ranking
Name
1
Albert
4
Alice
5
Kabir
3
Madonna
3
Parker
4
Kaira
To Sort using Formula only
6. Filter and Advance Filter
As a good practice, always try to use filter after sorting data. It will avoid paste of other data. Advance filter is available under data menu, shortcut is Alt+A+Q It gives facility to filter according to criteria range. Refer the box below Write all criteria in a row and apply it like we use criteria in database function.
How many parameters in advance filter? Ans - 5 (location, list range,criteria range, copy to, unique records only)
CHAPTER 7
1. Logical Functions (AND, OR, NOT, IF)
=AND(condition1, [condition2],….)
Return True if all confition are true
=OR(condition1,[condition2] …..)
Return True if any of the condition is true
=NOT(logical)
Returns the reversed logical value of condition.
=IF(condition, [value_if_true], [value_if_false])
Check condition and if True return one value and if False return other value.
Function Description
AND
OR
Returns TRUE if all of the arguments evaluate to TRUE.
Returns TRUE if any argument evaluates to TRUE.
Formula Example
Formula Description
=AND(A2>=10, B2<5)
The formula returns TRUE if a value in cell A2 is greater than or equal to 10, and a value in B2 is less than 5, FALSE otherwise.
=OR(A2>=10, B2<5)
The formula returns TRUE if A2 is greater than or equal to 10 or B2 is less than 5, or both conditions are met. If neither of the conditions it met, the formula returns FALSE.
=XOR(A2>=10, B2<5)
The formula returns TRUE if either A2 is greater than or equal to 10 or B2 is less than 5. If neither of the conditions is met or both conditions are met, the formula returns FALSE.
XOR
Returns a logical Exclusive Or of all arguments.
NOT
Returns the reversed logical value of its argument. I.e. If the argument is FALSE, then TRUE is returned and vice versa. =NOT(A2>=10)
The formula returns FALSE if a value in cell A1 is greater than or equal to 10; TRUE otherwise.
Arguments of logical function excludes text values and empty cells. If an argument of a logical function contains numbers, then zero evaluates to FALSE, and all other numbers including negative numbers evaluate to TRUE Logical function are used with IF,,function to check multiple conditions and hence is the alternative of Nested If. XOR works in Excel 2013 and above, e.g - Player winning 2 or more match can play final hence check for Game 3 first
Player
Roger Federer Andy Murray
Game1
WON
WON
Rafel Nadal LOST Novak Djokovic
LOST
Game2
Play Game 3 ?
WON
=XOR(B24=“WON”, C24=“WON”)
LOST
=XOR(B24=“WON”, C24=“WON”)
WON
=XOR(B24=“WON”, C24=“WON”)
LOST
=XOR(B24=“WON”, C24=“WON”)
Play Final ?
If logical evaluates to FALSE, the NOT function returns TRUE and vice versa. e.g - Exclude resources who are from North and include all others for Hiring Interview
Name
Region
Eligible for Hiring?
Andy
South
TRUE
Sumit
East
TRUE
=NOT(B33=“North”)
Raj
Middle
TRUE
Jenny
North
FALSE
Mark
West
TRUE
Iqbal
North
FALSE
IFNA function (Excel 2013+) - Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression IFERROR Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
2. Array Function (SumProduct, Sum, Nested If, Max, Min)
=SUMPRODUCT(ARRAY1,[ARRAY2],…….) To find the count of nos. greater than 10 and less than 15 using SUMPRODUCT
=SUMPRODUCT(($E$57:$H$65>10)* 8 ($E$57:$H$65<15)*1)
To find the 2 word names in given range of data
3 =SUMPRODUCT((LEN($E$43:$G$46)=2)*1) OR Using Countif- 2 question marks for 2 letter 3 =COUNTIF(E43:G46,”??”)
Nested If =if(logical_test,if(logical_test,[valueIfTrue],[valueIfFalse]))…………….,[valueIfFalse]))
To find the count of nos. greater than 10 and less than 15 using nested if 8 {=SUM(IF($E$57:$H$65>10,IF($E$57:$H$65<15,1,0)))}
To find the 2 word names in given range of data 3 {=SUM(IF(LEN($E$43:$G$46)=2,1,0))}
=MIN(number1, [number2], …)
MIN salary where department is HHS and hiring is after year 2000
{=MIN(IF(Data!$C$2:$C$101=“HHS”,IF(YEAR(Data!$I$2:$I$101)>2000,Data!$B$2:$B
=MAX(number1, [number2], …)
MAX salary where department is HHS and hiring is after year 2000
{=MAX(IF(Data!$C$2:$C$101=“HHS”,IF(YEAR(Data!$I$2:$I$101)>2000,Data!$B$2:$
=AVERAGE(number1, [number2], …)
AVERAGE salary where department is HHS and hiring is after year 2000
{=AVERAGE(IF(Data!$C$2:$C$101=“HHS”,IF(YEAR(Data!$I$2:$I$101)>2000, Data!$B$2:$B$101))) } Data Table -
3. Database Function (Dsum, Dcount, Dcounta, Dmax, Dmin, Daverage)
=Dfunction(Database,Field,Criteria) For department HHS and Assignment Category as Fulltime-Regular, D functions for salary are -
DSUM
=DSUM(Scrapbook!$A$1:$I$101,“Salary”,$A$96:$B$97)
18424
DCOUNT
=DCOUNT(Scrapbook!$A$1:$I$101,“Salary”,$A$96:$B$97)
DCOUNTA
=DCOUNTA(Scrapbook!$A$1:$I$101,“Salary”,$A$96:$B$97)
DMAX
=DMAX(Scrapbook!$A$1:$I$101,“Salary”,$A$96:$B$97)
20
DMIN
=DMIN(Scrapbook!$A$1:$I$101,“Salary”,$A$96:$B$97)
3
DAVERAGE =DAVERAGE(Scrapbook!$A$1:$I$101,“Salary”,$A$96:$B$97) 73697.
CHAPTER 8
1. SparkLines (Line, Column and Win/Loss)
Sparklines are mini graphs in the form of Line, Column and Win/Loss that you can add to cells
2. Charts and Its Types(Column, Line, Pie, Bar, Area, Scatter etc.)
Types of Charts –
1). Column charts Data that is arranged in columns or rows on a worksheet can be plotted in a column chart. Column charts are useful for showing data changes over a period of time or for illustrating comparisons among items. Used to represent nos.
2). Line Chart Data that is arranged in columns or rows on a worksheet can be plotted in a line chart. Line charts can display continuous data over time, set against a common scale, and are therefore ideal for showing trends in data at equal intervals. In a line chart, category data is distributed evenly along the horizontal axis, and all value data is distributed evenly along the vertical axis. You should use a line chart if your category labels are text, and are representing evenly spaced values such as months, quarters, or fiscal years. This is especially true if there are multiple series—for one series, you should consider using a category chart. You should also use a line chart if you have several evenly spaced numeric labels, especially years. If you have more than ten numeric labels, use a scatter chart instead. To represent % and trend 3). Pie Chart Data that is arranged in one column or row only on a worksheet can be plotted in a pie chart. Pie charts show the size of items in one data series, proportional to the sum of the items. The data points in a pie chart are displayed as a percentage of the whole pie. Consider using a pie chart when: You only have one data series that you want to plot. None of the values that you want to plot are negative. Almost none of the values that you want to plot are zero values. You do not have more than seven categories. The categories represent parts of the whole pie. Leader lines are lines connecting Data labels with chart area
4). Bar charts Data that is arranged in columns or rows on a worksheet can be plotted in a bar chart. Bar charts illustrate comparisons among individual items. Creating and Applying a Custom Style Ctrl + 1 - Format Box To select a object if it is not visible on graphs properly goto Layout and select from dropdown(leftmost side) the object to select For Date representation, convert to text axis by going to right click format data cell 3. Design & Layout Design allow you to select Chart Layouts and Styles. In Layout you have the option of Chart Title Axis Title Legend Data Labels Data Table Axes Gridlines Trend Lines
To avoid zero values from Data labels select Number formatting as custom and put #”” 4. Format
It allows you to do formatting with text and shape. Colors and text effects
5. Dashboard & KPI(Key Performing Indicators)
Dashboard reports allow managers to get high-level overview of the business and help them make quick decisions. Dashboards are often called as management dashboards or information dashboards or dashboard reports.
6. Other Important Derived Charts (Pareto, Speedometer, Thermometer)
1). Pareto Chart (Derived from Column and Line Chart) Pareto diagram is an advanced type of chart in Excel. Consists of both a column chart and line chart. It presents a graphical incidence of certain factors (eg, causes complaints or defects). It is used mainly for quality control, to show what the focus mainly to get the best results (eg, elimination of one defect may reduce the number of complaints by half.) Pareto Diagram was the originator of the Italian economist Vilfredo Pareto. During his research he concluded that 80% of the country’s wealth is held by only 20% of the people. In this way, the so-called. The Pareto Principle, which is based on the proportion of 20 / 80. According to the Pareto Principle: 80% of the complaints come from 20% of complaint types.
Most common damages in TV Sets
Damage
Cumulative Count %
Internal Circuits
41
41%
Screen Damage
20
61%
IC Damage
14
75%
Remote Set
11
86%
Speakers
9
95%
Others
5
100%
Steps for Pareto Chart 1. First, sort your data in descending order. 2. Calculate the cumulative %. Enter the formula (=SUM($B$103:B103)/SUM($B$103:$B$108)) shown below into cell C103 and drag the formula down. 3. Select the data in column A, B and C. 4. On the Insert tab, click Column and select Clustered Column. 5. Right click on the red bars (cumulative %) and click Change Series Chart Type and Select Line with Markers. 6. Right click on the red line and click Format Data Series. 7. Select Secondary Axis. 8. Right click the percentages of secondary Axis on the chart, click Format Axis, set the Maximum to 1 and click Close.
2). Speedometer (Derived from Donut and Pie Chart) Speedometer chart are used to show the progress of any task. It can be made using combination of Donut and PIE chart.
3). Thermometer (Derived from 2 column chart) Thermometer chart can be made in excel using 2 column charts. It is used to represent a state or position. 7. Camera Tool
Camera tool is your way of creating visual reference in an excel sheet. It enables you to place a live picture anywhere in the worksheet. Whenever the contents of original rectangular area changes (charts, drawings or cell values) the mirror image changes too.
