101 excel tips to make you an excel rockstar

101 Excel Tips ToMake You An Excel

RockStar…

By: Ankit Kaul

www.exceltrick.com

2013

What you can do with this e-Book?

You have permission to post this, email this, print this and pass it along for free to anyone you like, as longas you make no changes or edits to its contents or digital format.

http://www.exceltrick.com/

Brought to you by www.exceltrick.com 1

Contents of this Book

Top 25 Keyboard Shortcuts in Excel 2

15 Tips To Make Your Excel Lightening Fast 3

10 Small Tips To Ease Your Tasks In Excel 4

25 Formulas in Excel To Give You An Edge 7

15 Macro Examples for Beginners 8

11 Secret Tricks To Make You A Superhero 11

Sharing is Caring:

Spend few minutes of your precious time by sharing this small lovely e-bookwith your friends and colleagues.


Top 25 Keyboard Shortcuts in ExcelTip 1: To format any selected cell, press CTRL + 1.

Tip 2: To sum quickly select the range and press ALT +

Tip 3: For cycling through various types of cell references (absolute & relative),press F4 key while inserting the references.

Tip 4: CTRL+' Copies a formula from the above cell

Tip 5: Press CTRL + K to open the hyperlink dialog box.

Tip 6: Use CTRL+TAB to switch between open Excel windows.

Tip 7: Press CTRL+N to opens a new workbook.

Tip 8: Use F2 to edit an active cell.

Tip 9: Select a cell and then press CTRL + Space to select the whole column.

Tip 10: Select a cell and then press CTRL + Shift to select the whole row.

Tip 11: Press F7 to run spell checker on the active sheet.

Tip 12: Use CTRL+SHIFT+F3 to insert named range for an entire tableautomatically based on the column or row headings.

Tip 13: Use CTRL+Page Up or CTRL+Page Down to scroll between worksheets.

Tip 14: Press CTRL + SHIFT + L to apply a filter to the selected range.

Tip 15: Use CTRL+ ~ to view formulas instead of values.

Tip 16: Use CTRL + D to copy the above cell.

Tip 17: Use CTRL + ; to enter todays date in the cell.

Tip 18: Use SHIFT + CTRL + ; to enter current time in the cell.


Tip 19: Use CTRL + SHIFT + ( to unhide any hidden rows in the currentselection.

Tip 20: Use CTRL + SHIFT + ) to unhide any hidden columns in the currentselection.

Tip 21: Press CTRL + SHIFT + & to outline the border of selected cells.

Tip 22: Press CTRL + SHIFT + $ to apply Currency format to the selected cells.

Tip 23: Press CTRL + SHIFT + Plus (+) to bring up the insert cells dialog.

Tip 24: Press CTRL + Minus (-) to bring up the delete selected cells dialog.

Tip 25: Press CTRL + T to displays the Create Table dialog box.

15 Tips To Make Your Excel Lightening FastTip 26: Change Calculation to Manual mode. To do this navigate to: ‘Formulas’> ‘Calculation Options’. Calculate manually using F9 only when required.

Tip 27: Delete all named ranges and un-necessary formatting to make excelblazing fast.

Tip 28: Avoid array formulae, whenever possible.

Tip 29: Use Application.ScreenUpdating = False at the start in VBA Macros andin the end write Application.ScreenUpdating =True. This reduces the resourceoverhead.

Tip 30: In excel sheets that have VlookUp’s, if the source file is not going tochange; then it is better to paste-special the VlookUp values. This is becauseeven a couple of VlookUp’s slow down the performance immensely due torecalculation of values.

Tip 32: Avoid dumping huge datasheets into a single spreadsheet.

Tip 32: Try to use SUMIF formula instead of SUMPRODUCT whenever possible.


Tip 33: Cut down the use of nested If statements.

Tip 34: Avoid the use of Volatile functions. Volatile functions are simplefunctions that will recalculate each time a change of data occurs in any cell onany worksheet. Most functions which are non-Volatile will only recalculate if acell that they are referencing has changed. Some of the volatile functions areNOW(), TODAY(), OFFSET(), CELL(),INDIRECT(), ROWS(), COLUMNS() .

Tip 35: In Excel macros use Application.EnableEvents to false and later in theend of code revert it back to its original form.

Tip 36: In Excel macros the use of “Destination:” can reduce your resourceoverhead as this statement bypasses the clipboard. For example:

Instead of using:

Sheet1.Range(“A1:A1000″).Copy

Sheet2.Range(“B1″).pasteSpecial

Application.CutCopyMode=False

Use:

Sheet1.Range(“A1:A1000″).Copy Destination:=Sheet2.Range(“B1″)

Tip 37: In Excel macros use Application.Calculation = xlCalculationManual andlater in the end of code Application.Calculation = xlCalculationAutomatic.

Tip 38: Try to use “Find” and “Search” methods rather than looping throughcells. Loops work quickly when you are using them for less than 100 cells.

Tip 39: Try to minimize cross linking of spreadsheets.

Tip 40: When using too many pivots – Disable data drilling option. Thisreduces file size, cache memory and speeds up the file.

10 Small Tips To Ease Your Tasks In ExcelTip 41: Select all your cells with a single click: Click the small triangle icon toselect all the cells at once as shown.


Tip 42: Check Sum, Average and Count of selected cells: To check the sum,average and count of cells, just select them and you can see the details in thestatus bar.

Tip 43: See what’s on your clipboard: To check the clipboard quickly just clickthe “clipboard” button on excel as shown below.


Tip 44: Convert formulas to literals: Copy the formula and then right click andselect the values icon as shown.

Tip 45: Force a carriage return in a cell: Press ALT+ENTER keys while editing.

Tip 46: Quickly Transpose Data: Select the range then Navigate to Copy data >Paste Special > Transpose.

Tip 47: Use Format Painter as many times as you like – Just double click theFormat Painter and use it multiple times.

Tip 48: Concatenate text easily: Use the ampersand operator to concatenatetext. For example: To concatenate “text1” and “text 2” use the formula=“text1”&“text2”.

Tip 49: Clear cell formats easily: Select the cells and then navigate to Hometab > Clear > Clear Formats as shown.


Tip 50: Selecting all formula cells in excel: To select all formulas, hit CTRL + G,select “special” and check “formulas”.

25 Formulas in Excel To Give You An EdgeTip 51: To generate a random number between a defined range use:=RandBetween(lower_limit , upper_limit) e.g. =RandBetween(100 ,200)

Tip 52: Use LCM formula to get the Lowest common multiple betweenmultiple numbers. e.g. =LCM(2,4,8,12)

Tip 53: Obviously there is a formula for finding Greatest Common Divisor too.e.g. =GCD(2,4,8,12)

Tip 54: To get nth largest number in a range, use the formula =Large(range, n)

Tip 55: To get nth smallest number in a range, use =Small(range, n)

Tip 56: To count the number of characters in a word, use =Len(“Some_word”)

Tip 57: To extract file name from a file path use:=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1)).

Tip 58: =NOW() formula in excel is used to fetch the current date time.

Tip 59: Convert all characters in a word to lowercase by using formula=LOWER(“some_word”)

Tip 60: Convert all characters in a word to uppercase by using formula=UPPER(“some_word”)

Tip 61: Convert a word to proper case by using the formula=PROPER(“some_word”)

Tip 62: Use TRIM function to remove unnecessary spaces from a word. e.g.:=TRIM(“ some_word ”)

Tip 63: Clean function removes all non printing characters from a text. e.g.=CLEAN(“some_word”)


Tip 64: Excel formula to get file extension=REPLACE(RIGHT(A1,5),1,SEARCH(".",RIGHT(A1,5)),"")

Tip 65: Excel formula to get the total number of worksheets in a workbooks=SHEETS()

Tip 66: =ROW() function gives the row number in which the current cell ispresent.

Tip 67: =COLUMN() function gives the column number in which the current cellis present.

Tip 68: Find total number of instances of a particular character in text:=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))

Tip 69: To debug your formulas in Excel, select the portions of formula andpress F9 to see the result of that portion.

Tip 70: USE REPT() function to repeat a character multiple times. This can helpyou to create a micro chart.

Tip 71: USE =CELL("width") function to get the width of a particular cell.

Tip 72: Find the minimum value in an array if values by using MIN() function.e.g. =MIN(112,121, 101)

Tip 73: Find the maximum value in an array if values by using MAX() function.e.g. =MAX(112,121, 101).

Tip 74: USE Substitute() function to replace a word or character with someother text or character. e.g. =SUBSTITUTE("EXCEL","E","Z")

Tip 75: USE MODE function to get most recurring or repeating value from arange.

15 Macro Examples for BeginnersTip 76: Macro for counting rows and columns in the active sheet:


Sub Count()RCount = ActiveSheet.UsedRange.Rows.CountCcount = ActiveSheet.UsedRange.Columns.CountMsgBox "Rows = " & RCount & " Columns = " & CcountEnd Sub

Tip 77: Macro to count sheets in a Workbook:Sub Count2()myCount = Application.Sheets.CountMsgBox myCountEnd Sub

Tip 78: Macro to loop all cells in the used range:Sub Loopallcells()For Each Cl In ActiveSheet.UsedRange

'Do whatever you want hereNext ClEnd Sub

Tip 79: Sometimes you may want to close all files without saving. So here is aMacro to close all files:

Sub CloseAll()Application.DisplayAlerts = FalsemyTotal = Workbooks.CountFor i = 1 To myTotal

ActiveWorkbook.CloseNext iEnd Sub

Tip 80: Macro to fetch the address of active cell:Sub MyAddress()MsgBox ActiveCell.AddressEnd Sub

Tip 81: A macro that runs automatically whenever you open the workbookfile.

Sub Auto_Open()Msgbox "Hi Welcome!"End Sub

Tip 82: A simple macro to colour the active cell.Sub ColourCell()ActiveCell.Interior.Color = rgbBlueEnd Sub

Tip 83: Kill or delete a file by using a small excel macro:Sub Killfile()Dim MyFile As String 'This line of code is optional MyFile = "c:\folder\filename.xls" kill MyFileEnd Sub


Tip 84: Password protect the active sheet.Sub ProtectSheet()Password = InputBox("Enter the password : ")If Len(Password) > 0 Then

ActiveSheet.Protect Password, True, True, TrueElseMsgBox "Invalid Password"End IfEnd Sub

Tip 85: Unprotect the active sheet.Sub UnProtectSheet()Password = InputBox("Enter the password : ")If Len(Password) > 0 ThenActiveSheet.Unprotect PasswordElseMsgBox "Invalid Password"End IfEnd Sub

Tip 86: Create a folder using excel an macro.Sub Makefolder()MkDir "C:\Dip"End Sub

Tip 87: Check if a file exists or not.Sub RetrieveFile()File = Dir("C:\Windows\CSUP.txt")If Len(File) > 0 Then

MsgBox (File & " Exists")Else

MsgBox ("File Doesn't Exists")End IfEnd Sub

Tip 88: Macro that speaks out the current time.Public Sub TalkingTime()Time = NowApplication.Speech.Speak ("The Time is" & Time)End Sub

Tip 89: Macro for deleting empty rows within the current selectionSub DelEmptyRow()Rng = Selection.Rows.CountActiveCell.Offset(0, 0).SelectApplication.ScreenUpdating = FalseFor i = 1 To RngIf ActiveCell.Value = "" Then 'You can replace "" with 0 to delete rows with 'the value zeroSelection.EntireRow.DeleteElseActiveCell.Offset(1, 0).SelectEnd IfNext iApplication.ScreenUpdating = TrueEnd Sub


Tip 90: Delete all range names present in your workbookSub DeleteNames()Dim NameX As NameFor Each NameX In NamesActiveWorkbook.Names(NameX.Name).DeleteNext NameXEnd Sub

11 Secret Tricks To Make You A Superhero

Tip 91: You know that cells can be coloured, but do you know that even sheettabs can be coloured. See the below image to know how.

Tip 92: Did you knew that double clicking the office button closes your excelfile?


Tip 93: Did you know how to make your reports more awesome. See the

image below.

To do this simply select all the columns to the right of your report (by CTRL +SHIFT + Arrow (>)) and hide them. In the similar way select all the rows belowyour report (by CTRL + SHIFT + Arrow ( )˅) and hide them too. Simple isn’t it!

Tip 94: Did you know that double clicking on any top ribbon tab in Excel makesit disappear.

Tip 95: Did you know about the xlSheetVeryHidden feature. This feature canmake your spreadsheets hidden so that they cannot be made visible againwithout opening the VBA Editor.

To make them visible you need to press ALT + F11, then select your sheet andfinally change its visibility property. See the below image


Tip 96: When you have a workbook that is very large in size you can reducethis dramatically by saving the file as "Microsoft Excel Workbook (*.xls)" asopposed to "Microsoft Excel 5/95 Workbook (*.xls)".

Tip 97: Did you know that you can navigate to any cell quickly by using theexcel name box. To do this simply type the cell address and press enter.


Tip 98: Did you know that Microsoft has also given a file repairing property toExcel. This property can help you to recover corrupted files.

To use this, with an open spreadsheet press CTRL + O select the file that youwant to repair. After this click the dropdown on the Open button and select the

“Open and Repair” option as shown.

Tip 99: Print only the selected area from a spreadsheet. For this select an areathen navigate to Page Layout > Print Area > Set Print Area. Now when you printthat worksheet only the selected area will be printed.


Tip 100: Did you know that you can make the text inside cells invisible. Thiscan be done by using a custom format. Follow the below steps to do this.

1. Type some text in any cell.

2. Next press the CTRL + 1 keys to bring the format cells dialog.

3. Here, select the Custom format and Type “;;;” (without quotes) as shown.

4. Click ‘Ok’ and the text will become invisible.

Tip 101: Break the password of any excel workbook by using this macro. Alsoapplicable to Excel 2013.Sub CrackPassword()

Dim v1 As Integer, u1 As Integer, w1 As IntegerDim v2 As Integer, u2 As Integer, w2 As IntegerDim v3 As Integer, u3 As Integer, w3 As IntegerDim v4 As Integer, u4 As Integer, w4 As IntegerOn Error Resume Next

For v1 = 65 To 66: For u1 = 65 To 66: For w1 = 65 To 66


For v2 = 65 To 66: For u2 = 65 To 66: For w2 = 65 To 66For v3 = 65 To 66: For u3 = 65 To 66: For w3 = 65 To 66For v4 = 65 To 66: For u4 = 65 To 66: For w4 = 32 To 126

ActiveSheet.Unprotect Chr(v1) & Chr(u1) & Chr(w1) & _Chr(v2) & Chr(u2) & Chr(v3) & Chr(u3) & Chr(w3) & _Chr(v4) & Chr(u4) & Chr(w4) & Chr(w2)

Next: Next: Next: Next: Next: NextNext: Next: Next: Next: Next: Next

End Sub

Steps For Running this macro-

1 Open the excel file and press Alt + F11.

2 Under Project Window -> Right-Click -> Insert Module and Paste the belowcode.

3 Now come back to Microsoft Excel, under Macro's Click View Macro.

4 Select Macro and click Run.

This tip is only included for educational purpose. We at Excel Trick do notsupport such activities. Run this code at your own risk as this may be illegal insome parts of the world.


A message from Ankit

Thank you for taking the time to read these Tips. Enjoy using your new Excelpowers and keep practicing them.

If you haven’t subscribed to my blog then do it now using the below link:http://feedburner.google.com/fb/a/mailverify?uri=ExcelTrick

Kind Regards,Ankit Kaul

You can also join my Facebook page at: www.facebook.com/ExcelTrick

Or Else you can follow me on twitter: http://twitter.com/exceltrick

Sharing is Caring:

Spend few minutes of your precious time by sharing this small lovely e-bookwith your friends and colleagues.

http://feedburner.google.com/fb/a/mailverify?uri=ExcelTrick

http://twitter.com/exceltrick

101 excel tips to make you an excel rockstar

Documents

press ctrl shift

use shift ctrl

use ctrl shift

press alt tip

press ctrl n

press ctrl t

press ctrl minus

use ctrl page