Excel and VBA are powerful tools for data analysis, reporting, and automation. Whether you’re a beginner or an experienced user, having a cheat sheet handy can greatly enhance your productivity. In this comprehensive Excel and VBA cheat sheet, we’ve compiled essential formulas, functions, and shortcuts to help you work more efficiently and effectively in Excel. Don’t miss this valuable resource that can simplify your workflow and make you a more proficient Excel user.

Description	VBA Code
Activate by Tab Name	Sheets(“Input”).Activate
Activate by VBA Code Name	Sheet1.Activate
Activate by Index Position	Sheets(1).Activate
Next Sheet	ActiveSheet.Next.Activate
Get ActiveSheet	MsgBox ActiveSheet.Name
Select Sheet	Sheets(“Input”).Select
Set to Variable	Dim ws as Worksheet Set ws = ActiveSheet
Name / Rename	ActiveSheet.Name = “NewName”
Add Sheet	Sheets.Add
Add Sheet and Name	Sheets.Add.Name = “NewSheet”
Add Sheet to Variable	Dim ws As Worksheet Set ws = Sheets.Add
Copy Sheet	Sheets(“Sheet1”).Copy Before:=Sheets(“Sheet2”)
Unhide Sheet	Sheets(“Sheet1”).Visible = True or Sheets(“Sheet1”).Visible = xlSheetVisible
Hide Sheet	Sheets(“Sheet1”).visible = False or Sheets(“Sheet1”).visible = xlSheetHidden
Very Hide Sheet	Sheets(“Sheet1”).Visible = xlSheetVeryHidden
Delete Sheet	Sheets(“Sheet1”).Delete
Clear Sheet	Sheets(“Sheet1”).Cells.Clear
Unprotect (No Password)	Sheets(“Sheet1”).Unprotect
Unprotect (Password)	Sheets(“Sheet1”).Unprotect “Password”
Protect (No Password)	Sheets(“Sheet1”).Protect
Protect (Password)	Sheets(“Sheet1”).Protect “Password”
Protect but Allow VBA Access	Sheets(“Sheet1”).Protect UserInterfaceOnly:=True
Activate Cell	Range(“B3”).Activate Cells(3,2).Activate
Select Range	Range(“a1:a3”).Select Range(Range(“a1”), Range(“a3”)).Select Range(Cells(1, 1), Cells(3, 1)).Select

Cells & Ranges:

Description	VBA Code
Activate Cell	Range(“B3”).Activate
Select Range	Range(“a1:a3”).Select
Resize	Range(“B3”).Resize(2, 2).Select
Offset	Range(“B3”).Offset(2, 2).Select
Copy	Range(“A1:B3”).Copy Range(“D1”)
Cut	Range(“A1:B3”).Cut Range(“D1”)
Delete	Range(“A1:B3”).Delete
Clear	Range(“A1:A3”).Clear
Count	Range(“A1:A3”).Count
Set to Variable	Dim rng as Range Set rng = Range(“A1”)
Merge/UnMerge	Range(“A1:A3”).Merge Range(“A1:A3”).UnMerge
Loop Through Cells	Dim cell As Range For Each cell In Range(“A1:C3”) MsgBox cell.Value Next cell

Columns Cheat Sheet:

Description	VBA Code
Activate	Columns(1).Activate Columns(“a:a”).Activate Range(“a1”).EntireColumn.Activate
Height / Width	Range(“A1”).EntireColumn.ColumnWidth = 30
Delete	Range(“A1”).EntireColumn.Delete
Count	Range(“A1”).Columns.Count
Insert	Range(“A1”).EntireColumn.Insert
Last	Dim lCol as Long lCol = Cells(1, Columns.Count).End(xlToLeft).Column
Copy	Range(“A:A”).Copy Range(“E:E”)
Insert	Range(“A:A”).Copy Range(“E:E”).Insert

Arrays:

Description	VBA Code
Create	Dim arr(1 To 3) As Variant arr(1) = “one” arr(2) = “two” arr(3) = “three”
Create From Excel	Dim arr(1 To 3) As Variant Dim cell As Range, i As Integer i = LBound(arr) For Each cell In Range(“A1:A3”) i = i + 1 arr(i) = cell.Value Next cell
Read All Items	Dim i As Long For i = LBound(arr) To UBound(arr) MsgBox arr(i) Next i
Erase	Erase arr
Array to String	Dim sName As String sName = Join(arr, “:”)
Increase Size	ReDim Preserve arr(0 To 100)
Set Value	arr(1) = 22

Data & Time Excel Cheat Sheet

Description	Excel Formula
=EDATE	Adds a specified number of months to a date in Excel
=EOMONTH	Converts a date to the last day of the month (e.g., 7/18/2018 to 7/31/2018)
=DATE	Returns a number that represents the date (yyyy/mm/dd) in Excel. Useful when working with functions that have a date as an argument.
=TODAY	Inserts and displays today’s date in a cell
=NETWORKDAYS	Returns the number of whole workdays between two specified dates
=YEAR	Extracts and displays the year from a date (e.g., 7/18/2018 to 2018)
=YEARFRAC	Expresses the fraction of a year between two dates (e.g., 1/1/2018 – 3/31/2018 = 0.25)
Convert Time to Seconds	Converts an amount of time to seconds (e.g., 5 minutes to 300 seconds)

Lookup Excel Formulas:

Description	Excel Formula
INDEX MATCH	A combination of lookup functions that are more powerful than VLOOKUP
=VLOOKUP	A lookup function that searches vertically in a table
=HLOOKUP	A lookup function that searches horizontally in a table
=INDEX	A lookup function that searches vertically and horizontally in a table
=MATCH	Returns the position of a value in a series
=OFFSET	Moves the reference of a cell by the number of rows and/or columns specified

Mathematical Functions Formulas:

Description	Excel Formula
=SUM	Add the total of a series of numbers
=AVERAGE	Calculates the average of a series of numbers
=MEDIAN	Returns the median average number of a series
=SUMPRODUCT	Calculates the weighted average, very useful for financial analysis
=PRODUCT	Multiplies all of a series of numbers
=ROUNDDOWN	Rounds a number to the specified number of digits
=ROUNDUP	Rounds a number to the specific number of digits
AutoSum	A shortcut to quickly sum a series of numbers
=ABS	Returns the absolute value of a number
=PI	Returns the value of pi, accurate to 15 digits
=SUMIF	Sum values in a range that are specified by a condition
=SUMQ	Returns the sum of the squares of the arguments

Let me know to help you with some financial formulas:

Description	Excel Formula
=NPV	Calculates the net present value of cash flows based on a discount rate
=XNPV	Calculates the NPV of cash flows based on a discount rate and specific dates
=IRR	Calculates the internal rate of return (discount rate that sets the NPV to zero)
=XIRR	Calculates the internal rate of return (discount rate that sets the NPV to zero) with specified dates
=YIELD	Returns the yield of a security based on maturity, face value, and interest rate
=FV	Calculates the future value of an investment with constant periodic payments and a constant interest rate
=PV	Calculates the present value of an investment
=INTRATE	The interest rate on a fully invested security
=IPMT	Returns the interest payments on a debt security
=PMT	Returns the total payment (debt and interest) on a debt security
=PRICE	Calculates the price per $100 face value of a periodic coupon bond
=DB	Calculates depreciation based on the fixed-declining balance method
=DDB	Calculates depreciation based on the double-declining balance method
=SLN	Calculates depreciation based on the straight-line method

Condition Formulas For Excel:

Description	Excel Formula
=IF	Checks if a condition is met and returns a value if yes and if no
=OR	Checks if any conditions are met and returns only “TRUE” or “FALSE”
=XOR	The “exclusive or” statement returns true if the number of TRUE statements is odd
=AND	Checks if all conditions are met and returns only “TRUE” or “FALSE”
=NOT	Changes “TRUE” to “FALSE”, and “FALSE” to “TRUE”
IF AND	Combine IF with AND to have multiple conditions
=IFERROR	If a cell contains an error, you can tell Excel to display an alternative result

Arithmetic Operators:

Description	Excel Code
Add two values	=A2 + A3
Subtract a value from another	=A4 – B4
Multiply two values	=A6 * B1
Divide two values	=C3 / B4
Convert a value to a percentage	=C5%
Raise a value to power	=B1 ^ C1

Datatypes:

Description	Excel Code
Checks if a cell is a number	=ISNUMBER(A1)
Checks if a cell is a text	=ISTEXT(D1)
Checks if a cell is a boolean	=ISLOGICAL(A1)
Checks if a cell is a boolean	=ISLOGICAL(A1=A1)
Converts to number	=N(E1)
Converts to number	=N(D1)
Convert to text	=VALUETOTEXT(A1)
Convert to formatted text	=TEXT(C6, “0.00E+0”)
Convert text to serial	=DATEVALUE(“1/1/2022”)

Counting Data:

Description	Excel Code
Returns the number of cells in the range containing numbers, dates, and currencies	=COUNT(A5:E5)
Returns the number of cells in the range that aren’t empty	=COUNTA(A5:E5)
Returns the number of cells that are empty or contain the empty string (“”)	=COUNTBLANK(A5:E5)

Data Manipulation:

Description	Excel Code
Gets a subset of the cell range in the first input that meets the condition in the second input.	=FILTER(A1:B6, C1:C6>100)
Returns the dataset with rows in alphabetical order of the fourth column. Sorts the rows of the data according to values in specified columns.	=SORT(A1:E6, 4)
Returns the same as the SORT() example. Alternate, more flexible, syntax for sorting. Rather than specifying the column number, you specify an array to sort by.	=SORTBY(A1:E6, D1:D6)
Gets a list of unique values from the specified data.	=UNIQUE(A1:A6)
Returns 5 rows and 1 column containing the values 3, 5, 7, 9, 11. Generates a sequence of numbers, starting at the specified start value and with the specified step size.	=SEQUENCE(5, 1, 3, 2)

Specifying absolute cell references with $ prefixes:

The $ symbol before the column letter and/or row number tells Excel that the reference is absolute and should not change when the formula is copied or moved to another cell. The following examples all specify column B, row 2.

Description	Excel Code
Column and row references are both absolute	=$B$2
Column reference is absolute, row reference is relative	=$B2
Column reference is relative, row reference is absolute	=B$2

In conclusion, mastering Excel and VBA can significantly boost your productivity and effectiveness in handling data-related tasks. With this cheat sheet, you have a quick reference guide to essential Excel and VBA functions, formulas, and shortcuts that can save you time and effort. Keep this cheat sheet handy, and you’ll be equipped to tackle any Excel challenge that comes your way. Don’t miss out on this valuable resource that can elevate your Excel skills to the next level.