Excel ShortCuts

Heading	Short Cut Key	Discription
The Fundamentals	Ctrl + O	Open File
The Fundamentals	Ctrl + N	New File
The Fundamentals	Ctrl + P	Print
The Fundamentals	Ctrl + S	Save File
The Fundamentals	F12	Save File As
The Fundamentals	Ctrl + F4	Close File
The Fundamentals	Alt + F4	Close Excel
The Fundamentals	Esc	Exit Dialog
The Fundamentals	Ctrl + C	Copy
The Fundamentals	Ctrl + X	Cut
The Fundamentals	Ctrl + V	Paste
The Fundamentals	Ctrl + Z	Undo
The Fundamentals	Ctrl + Y	Redo
The Fundamentals	Ctrl + A	Select All
The Fundamentals	Ctrl + F	Find
The Fundamentals	Ctrl + H	Replace
The Fundamentals	Alt + Tab	Switch Windows
The Fundamentals	Alt + T + O	Options Menu
The Fundamentals	F4	Repeat Last Action
The Fundamentals	Ctrl + F1	Show / Hide Ribbon Menu
Rows & Columns	Ctrl + Spacebar	Select Column
Rows & Columns	Shift + Spacebar	Select Row
Rows & Columns	Ctrl + Shift + +	Insert Cells / Rows / Columns
Rows & Columns	Ctrl + -	Delete Cells / Rows / Columns
Rows & Columns	Right Mouse Button + E	Insert Cut Cells and Shift Over
Rows & Columns	Alt + A + G + G	Group Rows / Columns
Rows & Columns	Shift + Alt + Right	Group Rows / Columns
Rows & Columns	Alt + A + U + U	Ungroup Rows / Columns
Rows & Columns	Shift + Alt + Left	Ungroup Rows / Columns
Rows & Columns	Alt + A + J	Show Grouped Rows / Columns
Rows & Columns	Alt + A + H	Hide Grouped Rows / Columns
Navigation & Data Selection	Arrow Keys	Move Around
Navigation & Data Selection	Ctrl + Arrows	Jump to Boundary
Navigation & Data Selection	Shift + Arrows	Select Cells
Navigation & Data Selection	Shift + Ctrl + Arrows	Select to Boundary
Navigation & Data Selection	Shift + F8	Select Multiple Areas
Editing Cells	F2	Edit Cell
Editing Cells	Del	Delete Cell Contents
Editing Cells	Ctrl + Arrows	Skip Word(s)
Editing Cells	Ctrl + Shift +Arrows	Highlight Word(s)
Editing Cells	Alt + Enter	New Line in Cell
Editing Cells	Ctrl + Enter /Tab / Shift +Tab	Edit and… Stay in Place / Go Left /Go Right
Workbooks & Worksheets	Ctrl + N	New Workbook
Workbooks & Worksheets	Ctrl + Tab	Switch Workbook
Workbooks & Worksheets	Shift + F11	New Worksheet
Workbooks & Worksheets	Alt + H + D + S	Del Worksheet
Workbooks & Worksheets	Ctrl + PgUp	Move to Left Worksheet
Workbooks & Worksheets	Ctrl + PgDn	Move to Right Worksheet
Workbooks & Worksheets	Alt + H + O + M	Move / Copy Worksheet
Workbooks & Worksheets	Shift + Ctrl + PgUp / PgDn	Select Multiple Worksheets
Workbooks & Worksheets	Alt + H + O + U + S	Hide Worksheet
Workbooks & Worksheets	Alt + H + O + U + H	Show Worksheet
Workbooks & Worksheets	Alt + H + O + R	Rename Worksheet
Workbooks & Worksheets	Alt + H + O + T	Color Tab
Date and Time	=Date(Year,month,day)	Create New Date
Date and Time	=NETWORKDAYS(Start, End Date)	Business days in between 2 dates
Date and Time	=EOMONTH (Start Date, # Months)	Last day of month after # months
Date and Time	Ctrl + Shift + ;	Current Time
Date and Time	Ctrl + ;	Current Date
Basic Formatting	Alt + H	Format Menu
Basic Formatting	Ctrl + 1	Format Dialog
Basic Formatting	Ctrl + Alt + V	Paste Special
Basic Formatting	Ctrl + Alt + V + T	Paste Formats
Basic Formatting	Ctrl + Alt + V + V	Paste Values
Basic Formatting	Ctrl + Alt + V + F	Paste Formulas
Basic Formatting	Alt + H + FC	Font Color
Basic Formatting	Alt + H + H	Fill Color
Basic Formatting	Alt + H + B	Border Options
Basic Formatting	Alt + H + A + L / C / R	Align Left / Center/ Right
Basic Formatting	Alt + H + 6	Increase Indent
Basic Formatting	Alt + H + 5	Decrease Indent
Basic Formatting	Alt + H + 0	Increase Decimal Places
Basic Formatting	Alt + H + 9	Decrease Decimal Places
Basic Formatting	Ctrl + B	Bold
Basic Formatting	Ctrl + I	Italics
Basic Formatting	Ctrl + U	Underline
Basic Formatting	Ctrl + 5	Strikethrough
Basic Formatting	Ctrl + Shift + &	Add Borders
Basic Formatting	Ctrl + Shift + –	Delete Borders
Basic Formatting	Shift + Ctrl + ~	General
Basic Formatting	Shift + Ctrl + !	Number
Basic Formatting	Shift + Ctrl + @	Time
Basic Formatting	Shift + Ctrl + #	Date
Basic Formatting	Shift + Ctrl + $	Currency
Basic Formatting	Shift + Ctrl + %	Percentage
Basic Formatting	Shift + Ctrl + ^	Scientific
Basic Formatting	=TEXT(Cell, Format)	Displays cell using custom format
Basic Formatting	Alt + H + O + I	Auto-Fit Col.
Basic Formatting	Alt + H + O + A	Auto-Fit Row
Basic Formatting	Alt + H + O + W	Column Width
Basic Formatting	Alt + H + O + H	Row Height
Basic Formatting	Alt + H + L + R	Conditional Formatting
Basic Formatting	Alt + H + T	Format as Table
Text Tools & Functions	Alt + A + FT	Text File Import
Text Tools & Functions	=LEFT	Chars from le
Text Tools & Functions	=RIGHT	Chars from right
Text Tools & Functions	=MID	Chars from…
Text Tools & Functions	=FIND	Search for text within text
Text Tools & Functions	=SEARCH	Same, but not case sensitive
Text Tools & Functions	=LEN	Length of text
Text Tools & Functions	=SUBSTITUTE	Replace text in text with search
Text Tools & Functions	=REPLACE	Same, but use position instead
Text Tools & Functions	Alt + A + E	Text to Columns
Text Tools & Functions	=TRIM	Deletes Extra Spaces
Text Tools & Functions	=PROPER	Capitalize All First Letters
Text Tools & Functions	=UPPER	Make All Caps
Text Tools & Functions	=LOWER	Make All Lower
Formulas and Calculations	=	Enter Formula
Formulas and Calculations	F9	Refresh All
Formulas and Calculations	F4	Anchor Cell
Formulas and Calculations	Ctrl + F3	Name Cell
Formulas and Calculations	F5	Jump to Cell
Formulas and Calculations	Tab	Navigating
Formulas and Calculations	Shift + F3	Enter Built-In Function
Formulas and Calculations	Ctrl + Alt + V + F	Paste Formulas
Formulas and Calculations	Ctrl + Alt + V + R	Paste Formats & Formulas
Formulas and Calculations	Ctrl + D	Copy Down
Formulas and Calculations	Ctrl + R	Copy Right
Formulas and Calculations	Ctrl + ’	Copy from Above
Formulas and Calculations	F5, Alt + S + F + X	Go to Formulas
Formulas and Calculations	F5, Alt + S + O + X	Go to Constants
Formulas and Calculations	Ctrl + ~	Show Formulas
Formulas and Calculations	=IFERROR(Value, Value If Error)	Calculates only if no error
Display & Printing	Alt + W + F + F	Freeze Panes
Display & Printing	Alt + W + Q	Zoom
Display & Printing	Ctrl + Mouse Scroll Wheel	Zoom
Display & Printing	Alt + P + S + P	Page Setup
Display & Printing	Alt + P + R + S	Set Print Range to Selected Area
Display & Printing	Ctrl + F2	Print Preview
Display & Printing	Alt + W + I	Page Break View
Display & Printing	Alt + W + L	Normal View
Display & Printing	Alt + W + VG	Toggle Gridlines
Lookups & Related Functions	=VLOOKUP(Value, Table,Column #)	Match Value in Left Column and Return from Column #
Lookups & Related Functions	=HLOOKUP(Value, Table,Row #)	Match Value in Top Row and Return from Row #
Lookups & Related Functions	=MATCH (Value,Row or Column Range)	Find Item’s Position in Row/Column
Lookups & Related Functions	=INDEX (Table,Row #, Col #)	Return Item at Row # and Column #
Lookups & Related Functions	=INDIRECT (Ref)	Returns cell at reference given by text
Lookups & Related Functions	=ADDRESS (Row #, Col #)	Creates cell reference
Common Built-In Functions	=SUM	Sum Numbers
Common Built-In Functions	Alt + =	Sum Adjacent Cells
Common Built-In Functions	=COUNT	Count # Entries
Common Built-In Functions	=AVERAGE	Average
Common Built-In Functions	=MAX	Maximum
Common Built-In Functions	=MIN	Minimum
Common Built-In Functions	=SUMIF /	Conditional
Common Built-In Functions	=SUMIFS	Sum
Common Built-In Functions	=COUNTIF /	Conditional
Common Built-In Functions	=COUNTIFS	Count
Common Built-In Functions	=SUMPRODUCT	Multiply and Sum Range
Common Built-In Functions	=ABS	Absolute Value
Common Built-In Functions	=If	Conditional
Common Built-In Functions	=OR	One Must Be TRUE
Common Built-In Functions	=AND	All Must Be TRUE
Common Built-In Functions	=NPV (Discount Rate, Cash Flows)	Net Present Value of Cash Flows
Common Built-In Functions	=XNPV (Rate,Values, Dates)	NPV with irregular dates
Common Built-In Functions	=IRR (Values)	Internal Rate of Return of Investment
Common Built-In Functions	=XIRR (Values,Dates)	IRR with irregular dates
Database and Array Functions	=DSUM (DB,Field, Criteria)	Sums records that match criteria
Database and Array Functions	=DCOUNT (DB,Field, Criteria)	Counts records that match criteria
Database and Array Functions	Ctrl + Shift + Enter	Enter Array Function
Database and Array Functions	=TRANSPOSE (Rows or Columns)	Converts rows to columns and vice versa
Auditing Formulas	Ctrl + [	Immediate Precedents
Auditing Formulas	Ctrl + ]	Immediate Dependents
Auditing Formulas	Alt + M + P	Trace Precedents
Auditing Formulas	Alt + M + D	Trace Dependents
Auditing Formulas	Alt + M + A + A	Erase Traces
Auditing Formulas	Shift + Ctrl + {	All Precedents
Auditing Formulas	Shift + Ctrl + }	All Dependents
Auditing Formulas	F5 + Enter	Jump to Original Cell
Auditing Formulas	Shift + F2	Add/Edit Comment
Auditing Formulas	Alt + R + D	Del Comment
Auditing Formulas	Alt + R + A	Show All Comments
Auditing Formulas	F5, Alt + S + C	Highlight Cells w/ Comments
Filtering, Sorting & Validating	Alt + A + SS	Sort Data
Filtering, Sorting & Validating	Alt + A + SA	Sort Ascending
Filtering, Sorting & Validating	Alt + A + SD	Sort Descending
Filtering, Sorting & Validating	Ctrl + Shift + L	Filter Data
Filtering, Sorting & Validating	Alt + A + Q	Advanced Data Filter
Filtering, Sorting & Validating	Right Mouse Button + E + V	Filter by Cell’s Properties
Filtering, Sorting & Validating	Alt + A + M	Remove Duplicates
Filtering, Sorting & Validating	Alt + A + V + V	Validate Data
Scenarios & Sensitivities	=CHOOSE(Number, Item1,Item2…)	Select from List based on Number
Scenarios & Sensitivities	=OFFSET(Cell, #Rows, # Cols)	Move # of Rows and Columns from Cell
Scenarios & Sensitivities	Alt + A + W + S	Scenario Manager
Scenarios & Sensitivities	Alt + A + W + G	Goal Seek
Scenarios & Sensitivities	Alt + A + W + T	Data Table
Macros, VBA, and Forms	Alt + F11	VBA Editor
Macros, VBA, and Forms	F5 (in VBA)	Run Macro
Macros, VBA, and Forms	F2 (in VBA)	Object Browser Ctrl + G (in VBA) Immediate Window
Macros, VBA, and Forms	Alt + L + I	Form Control
Macros, VBA, and Forms	Alt + W + M + U	Use Relative References
Macros, VBA, and Forms	Alt + W + M + R	Record Macro
Macros, VBA, and Forms	Alt + W + M + V	View Macros
Graphs & Charts	Alt + N + C	Column Chart
Graphs & Charts	Alt + N + N	Line Chart
Graphs & Charts	Alt + N + Q	Pie Chart
Graphs & Charts	Alt + N + B	Bar Chart
Graphs & Charts	Alt + N + X	Text Box
Graphs & Charts	Alt + N + SD	Combo Chart (2013+)
Graphs & Charts	Alt + N + R	Recommended Chart (2013+)
Graphs & Charts	Alt + JC + A	Add Chart Element (2013+)
Graphs & Charts	Alt + JC	Design Tab
Graphs & Charts	Alt + JA	Layout Tab (2007, 2010)
Graphs & Charts	Alt + JO	Format Tab (2007, 2010)
Graphs & Charts	Alt + JA	Format Tab -2013

Chart and VBA

Excel’s Charts

Excel’s charting feature lets you create a wide variety of charts using data that’s stored in a worksheet. You have a great deal of control over nearly every aspect of each chart. An Excel chart is simply packed with objects, each of which has its own properties and methods. Because of this, manipulating charts with Visual Basic for Applications (VBA) can be a bit of a challenge. We discuss the key concepts that you need to understand in order to write VBA code that generates or manipulates charts.

The Chart object model

When you first start exploring the object model for a Chart object, you’ll probably be very confused — which isn’t surprising; the object model is very confusing. It’s also very deep. For example, assume that you want to change the title displayed in an embedded chart. The toplevel object, of course, is the Application object (Excel). The Application object contains a Workbook object, and the Workbook object contains a Worksheet object. The Worksheet object contains a ChartObject object, which contains a Chart object. The Chart object has a ChartTitle object, and the ChartTitle object has a Text property that stores the text that’s displayed as the chart’s title. Here’s another way to look at this hierarchy for an embedded chart:

Application

Workbook

Worksheet

ChartObject

Chart

ChartTitle

Your VBA code must, of course, follow this object model precisely. For example, to set a chart’s title to Annual Sales, you can write a VBA instruction like this:

WorkSheets(“Sheet1”).ChartObjects(1).Chart.ChartTitle. Text = “YTD Sales”

Note that the preceding statement will fail if the chart doesn’t have a title. To add a default title to the chart (which displays the text Chart Title), use this statement:

Worksheets(“Sheet1”).ChartObjects(1).Chart.HasTitle = True

Creating an Embedded Chart

A ChartObject is a special type of Shape object. Therefore, it’s a member of the Shapes collection. To create a new chart, use the AddChart method of the Shapes collection. The following statement creates an empty embedded chart:

ActiveSheet.Shapes.AddChart

The AddChart method can use five arguments (all are optional):

Type: The type of chart. If omitted, the default chart type is used. Constants for all the chart types are provided (for example, xlArea, xlColumnClustered, and so on).

 Left: The left position of the chart, in points. If omitted, Excel centers the chart horizontally.

Top: The top position of the chart, in points. If omitted, Excel centers the chart vertically.

Width: The width of the chart, in points. If omitted, Excel uses 354.

Height: The height of the chart, in points. If omitted, Excel uses 210.

A chart without data isn’t very useful, so you’ll want to use the SetSourceData method to add data to a newly created chart. The procedure that follows demonstrates the SetSourceData method. This procedure creates the chart shown in below Figure.

Data for Chart

Macro Code for Chart

Sub CreateChart()

Dim MyChart As Chart

Dim DataRange As Range

Set DataRange = ActiveSheet.Range(“A1:C13”)

Set MyChart = ActiveSheet.Shapes.AddChart.Chart

MyChart.SetSourceData Source:=DataRange

End Sub

OutPut

Creating a Chart on a Chart Sheet

To create a chart on a chart sheet, use the Add method of the Charts collection. The Add method of the Charts collection uses several optional arguments, but these arguments specify the position of the chart sheet — not chart-related information.

The example that follows creates a chart on a chart sheet and specifies the data range and chart type:

Sub CreateChartSheet()

Dim MyChart As Chart

Dim DataRange As Range

Set DataRange = ActiveSheet.Range(“A1:C13”)

Set MyChart = Charts.Add

MyChart.SetSourceData Source:=DataRange

ActiveChart.ChartType = xlColumnClustered

End Sub

Exporting a Chart or Graphics

In some cases, you may need an Excel chart in the form of a graphics file. For example, you may want to post the chart on a Web site. One option is to use a screen-capture program and copy the pixels directly from the screen. Another choice is to write a simple VBA macro as given below.

Sub SaveChartAsGIF ()

Dim Fname as String

If ActiveChart Is Nothing Then Exit Sub

Fname = ThisWorkbook.Path & “\” & ActiveChart.Name & “.gif”

ActiveChart.Export FileName:=Fname, FilterName:=”GIF”

End Sub

Other choices for the FilterName argument are “JPEG” and “PNG”.

Another way to export all graphic images from a workbook is to save the file in HTML format. Doing so creates a directory that contains GIF and PNG images of the charts, shapes, clipart, and even copied range images (created with HomeàClipboardàPasteàPicture (U)).

Here’s a VBA procedure that automates the process. It works with the active workbook:

Sub SaveAllGraphics()

Dim FileName As String

Dim TempName As String

Dim DirName As String

Dim gFile As String

FileName = ActiveWorkbook.FullName

TempName = ActiveWorkbook.Path & "\" & _

ActiveWorkbook.Name & "graphics.htm"

DirName = Left(TempName, Len(TempName) - 4) & "_files"

' Save active workbookbook as HTML, then reopen original

ActiveWorkbook.Save

ActiveWorkbook.SaveAs FileName:=TempName, FileFormat:=xlHtml

Application.DisplayAlerts = False

ActiveWorkbook.Close

Workbooks.Open FileName

' Delete the HTML file

Kill TempName

' Delete all but *.PNG files in the HTML folder

gFile = Dir(DirName & "\*.*")

Do While gFile <> ""

If Right(gFile, 3) <> "png" Then Kill DirName & "\" & gFile

gFile = Dir

Loop

' Show the exported graphics

Shell "explorer.exe" & DirName, vbNormalFocus

End Sub

Changing chart data based on the active cell

Figure below shows a chart that’s based on the data in the row of the active cell. When the user moves the cell pointer, the chart is updated automatically.

This example uses an event handler for the Sheet1 object. The SelectionChange event occurs whenever the user changes the selection by moving the cell pointer. The event-handler procedure for this event (which is located in the code module for the Sheet1 object) is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target  As Excel.Range)

If CheckBox1 Then Call UpdateChart

End Sub

In other words, every time the user moves the cell cursor, the Worksheet_SelectionChange procedure is executed. If the Auto Update Chart check box (an ActiveX control on the sheet) is checked, this procedure calls the UpdateChart procedure, which follows:

Sub UpdateChart()

Dim ChtObj As ChartObject

Dim UserRow As Long

Set ChtObj = ActiveSheet.ChartObjects(1)

UserRow = ActiveCell.Row

If UserRow < 4 Or IsEmpty(Cells(UserRow, 1)) Then

ChtObj.Visible = False

Else

ChtObj.Chart.SeriesCollection(1).Values = _

Range(Cells(UserRow, 2), Cells(UserRow, 5))

ChtObj.Chart.ChartTitle.Text = Cells(UserRow, 1).Text

ChtObj.Visible = True

End If

End Sub

The UserRow variable contains the row number of the active cell. The If statement checks that the active cell is in a row that contains data. (The data starts in row 4.) If the cell cursor is in a row that doesn’t have data, the ChartObject object is hidden. Otherwise, the code sets the Values property for the Series object to the range in columns 2–5 of the active row. It also sets the ChartTitle object to correspond to the text in column A.

Animating Charts: How to Animate Chart we will show you in next post

Pivot Table and VBA

Creating a pivot table

Below Figure shows a pivot table created from the data, along with the PivotTable Field List task bar. This pivot table summarizes the Salary of Employee and month. This pivot table is set up with the following fields:

v  Department: A report filter field in the pivot table.

v  Employee Name: A row field in the pivot table.

v  Month: A column field in the pivot table.

v  Salary: A values field in the pivot table that uses the Sum function.

I turned on the macro recorder before I created this pivot table and specified a new worksheet for the pivot table location. The code that was generated follows:

Sub CreatePivot()

'This Macro is recorded and done necessary changes

    Range(“A1:E28”).Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "'Monthwise Salary Register'!R1C1:R28C5", Version:=xlPivotTableVersion12). _

        CreatePivotTable TableDestination:="", TableName:="PivotTable1", _

        DefaultVersion:=xlPivotTableVersion12

    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

    ActiveSheet.Cells(3, 1).Select

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department")

        .Orientation = xlPageField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee Name")

        .Orientation = xlRowField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")

        .Orientation = xlColumnField

        .Position = 1

    End With

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

        "PivotTable1").PivotFields("Salary"), "Sum of Salary", xlSum

End Sub

Cleaning up the recorded pivot table code

As with most recorded macros, the preceding example isn’t as efficient as it could be. And, as I noted, it’s very likely to generate an error. You can simplify the code to make it more understandable and also to prevent the error. The hand-crafted code that follows generates the same pivot table as the procedure previously listed:

Sub CreatePivotTable()

Dim PTCache As PivotCache

Dim PT As PivotTable

‘ Create the cache

Set PTCache = ActiveWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=Range(“A1”).CurrentRegion)

‘ Add a new sheet for the pivot table

Worksheets.Add

‘ Create the pivot table

Set PT = ActiveSheet.PivotTables.Add( _

PivotCache:=PTCache, _

TableDestination:=Range(“A3”))

‘ Specify the fields

With PT

.PivotFields(“Department”).Orientation = xlPageField

.PivotFields(“Month”).Orientation = xlColumnField

.PivotFields(“Employee Name”).Orientation = xlRowField

.PivotFields(“Salary”).Orientation = xlDataField

‘no field captions

.DisplayFieldCaptions = False

' Change the captions

.PivotFields("Sum of Salary").Caption = " Salary  "

.PivotFields("Sum of Incentive").Caption = " Incentive  "

End With

End Sub

The CreatePivotTable procedure is simplified and might be easier to understand, because it declares two object variables: PTCache and PT. A new PivotCache object is created by using the Create method. A worksheet is added, and it becomes the active sheet (the destination for the pivot table). Then a new PivotTable object is created by using the Add method of the PivotTables collection. The last section of the code adds the four fields to the pivot table and specifies their location within it by assigning a value to the Orientation property. The original macro hard-coded both the data range used to create the PivotCache object (‘'Monthwise Salary Register'!R1C1:R28C5’) and the pivot table location (Sheet2 or Sheet3). In the CreatePivotTable procedure, the pivot table is based on the current region surrounding cell A1. This ensures that the macro will continue to work properly if more data is added. Adding the worksheet before the pivot table is created eliminates the need to hard-code the sheet reference. Yet another difference is that the hand-written macro doesn’t specify a pivot table name. Because the PT object variable is created, your code doesn’t ever have to refer to the pivot table by name.

Note: Changes the captions displayed in the pivot table. For example, Sum of Salary is replaced by Salary. Note that the string Salary is preceded by a space. Excel doesn’t allow you to change a caption that corresponds to a field name, so adding a space gets around this restriction.