Excel Cheat Sheet

Introduction

Excel is a powerful tool that has been widely used for data analysis, financial modeling, and project management. It is a spreadsheet software that allows users to organize, manipulate, and analyze data in various ways. However, with its extensive functionalities, it can be overwhelming and confusing for new users. That's where a cheat sheet comes in handy. An Excel cheat sheet is a reference guide that provides quick tips, shortcuts, and formulas to help users navigate the software efficiently. As the software is continuously evolving, it's important to keep up-to-date with the latest version's features and functions. Therefore, an Excel Cheat Sheet 2022 would be a valuable resource for users to maximize their productivity and optimize their workflow.

Excel Cheat Sheet

Basic ̌Definitions

Here are some basic definitions that every Excel user should know:

• Workbook: An Excel file is called a Workbook. It can contain one or more worksheets.
• Worksheet: A worksheet is a single page within a Workbook. It is also called a spreadsheet.
• Cell: A cell is the intersection of a row and a column in a worksheet. It is where you enter data or formulas.
• Row: A row is a horizontal line of cells in a worksheet. Each row is identified by a number.
• Column: A column is a vertical line of cells in a worksheet. Each column is identified by a letter.
• Range: A range is a group of cells in a worksheet. You can select a range of cells to apply formatting, formulas, or other operations.
• Formula: A formula is an equation that performs a calculation on one or more values in a worksheet. It always starts with an equal sign (=).
• Function: A function is a predefined formula that performs a specific calculation. Excel has a wide range of built-in functions, such as SUM, AVERAGE, and COUNT.
• Cell Reference: A cell reference is a unique identifier for a cell in a worksheet. It consists of the column letter and row number, such as A1 or B3.
• Autofill: Autofill is a feature that automatically fills a series of cells with a pattern or sequence. You can use it to quickly enter dates, numbers, or text.

Knowing these basic definitions will help you understand Excel better and use it more effectively. Now let us read about the next heading:

Cells and Ranges

Cells and ranges are the basic building blocks of a spreadsheet in Excel. Here are some key tips and shortcuts related to cells and ranges that can help you work more efficiently:

• Select a range of cells: Click on the first cell, hold down the Shift key, and click on the last cell in the range.
• Select a non-adjacent range of cells: Click on the first cell, hold down the Ctrl key, and click on the other cells you want to include in the range.
• Move to the last cell in a range: Press Ctrl+Shift+End.
• Insert a new cell: Right-click on the cell and select "Insert" or use the shortcut Ctrl+Shift+=.
• Delete a cell: Right-click on the cell and select "Delete" or use the shortcut Ctrl+-.
• Merge cells: Select the cells you want to merge and click on the "Merge & Center" button in the Home tab or use the shortcut Alt+H+M+C.
• Split merged cells: Select the merged cell, click on the "Merge & Center" button, and then click on the "Split Cells" option or use the shortcut Alt+H+M+S.
• Freeze rows or columns: Click on the "View" tab and select "Freeze Panes" to choose which rows or columns to freeze.
• Autofill a range: Click on the cell with the content you want to copy, drag the fill handle across the range you want to fill, and release the mouse button.
• Copy a formula or value to a range: Select the cell with the formula or value, press Ctrl+C, select the destination range, and press Ctrl+V.

Example dataset

Let's assume we have a dataset` containing information on the sales performance of a company for the past year. The dataset includes the following columns:

• Date: the date when the sale was made
• Product: the name of the product sold
• Category: the category of the product (e.g., electronics, clothing, home goods)
• Quantity: the number of units sold
• Price per unit: the price per unit of the product
• Total sales: the total amount of money earned from the sale (calculated as Quantity multiplied by Price per unit)

For example, the dataset might include a row with the following information:

• Date: 2022-01-05
• Product: iPhone 13
• Category: Electronics
• Quantity: 10
• Price per unit: $1,000
• Total sales: $10,000 With this information, we can analyze the sales performance of the company by category, product, and date, among other factors. This can help us identify trends, make forecasts, and optimize our sales strategy.

Operators

Here are some commonly used operators in Excel that you might find useful while working with Excel:

1. Arithmetic Operators:

• Addition (+)
• Subtraction (-)
• Multiplication (*)
• Division (/)
• Exponentiation (^)
• Modulus (%)

2. Comparison Operators:

• Equal to (=)
• Not equal to (<>)
• Greater than (>)
• Less than (<)
• Greater than or equal to (>=)
• Less than or equal to (<=)

3. Concatenation Operator:

• Ampersand (&)
• Reference Operators:
• Colon (:)
• Comma (,)
• Semi-colon (;)

4. Logical Operators:

• AND
• OR
• NOT

These operators can be used in combination with functions and formulas to perform various calculations and` operations on data in Excel.

Logical Functions

Logical functions are an essential part of Excel's formula language, allowing users to make decisions based on specific conditions. Here are some of the most commonly used logical functions in Excel:

• IF Function: The IF function tests a condition and returns one value if the condition is true and another value if it's false. Its syntax is =IF(logical_test, value_if_true, value_if_false).
• AND Function: The AND function returns TRUE if all of its arguments are true, and FALSE if any of them are false. Its syntax is =AND(logical1, logical2, ...).
• OR Function: The OR function returns TRUE if any of its arguments are true, and FALSE if all of them are false. Its syntax is =OR(logical1, logical2, ...).
• NOT Function: The NOT function reverses the logical value of its argument. If the argument is true, it returns false, and vice versa. Its syntax is =NOT(logical).
• IFERROR Function: The IFERROR function tests a formula for an error value and returns a specified value if the formula returns an error. Its syntax is =IFERROR(value, value_if_error).
• IFNA Function: The IFNA function tests a formula for a #N/A error value and returns a specified value if the formula returns #N/A. Its syntax is =IFNA(value, value_if_na).

Data types

In Excel Excel formulas cheat sheet, there are several data types that you can work with, each with its unique characteristics and uses. Here are some of the most common data types in Excel:

• Text: This data type is used for any type of text-based information, such as names, addresses, or notes.
• Number: This data type is used for any type of numerical data, including whole numbers, decimals, and fractions.
• Date/Time: This data type is used for any type of date or time information, including dates, times, and date/time combinations.
• Currency: This data type is used for any type of financial data, including currency values and monetary amounts.
• Percentage: This data type is used for any type of percentage value, such as interest rates or discounts.
• Boolean: This data type is used for any type of true/false or yes/no information.

Excel Formulas Cheat Sheet

1. Shortcuts

Excel Shortcuts:

• Ctrl + C - Copy
• Ctrl + X - Cut
• Ctrl + V - Paste
• Ctrl + Z - Undo
• Ctrl + Y - Redo
• Ctrl + F - Find
• Ctrl + H - Replace
• Ctrl + B - Bold
• Ctrl + I - Italic
• Ctrl + U - Underline
• Ctrl + A - Select all
• Ctrl + 1 - Format Cells
• Ctrl + ; - Insert current date
• Ctrl + Shift + ; - Insert current time
• Ctrl + Shift + # - Format number as date
• Ctrl + Shift + @ - Format number as time
• F2 - Edit cell
• F4 - Repeat the last action
• F7 - Spell check
• Alt + = - Autosum

2. Dates and time Excel formulas cheat sheet

• TODAY(): This formula returns the current date.
• NOW(): This formula returns the current date and time.
• DATE(year, month, day): This formula returns the date value for a given year, month, and day.
• YEAR(date): This formula returns the year value for a given date.
• MONTH(date): This formula returns the month value for a given date.
• DAY(date): This formula returns the day value for a given date.
• HOUR(time): This formula returns the hour value for a given time.
• MINUTE(time): This formula returns the minute value for a given time.
• SECOND(time): This formula returns the second value for a given time.
• DATEDIF(start_date, end_date, unit): This formula returns the difference between two dates in a specified unit, such as days, months, or years.
• EOMONTH(start_date, months): This formula returns the last day of the month, a specified number of months after a given start date.
• WORKDAY(start_date, days, holidays): This formula returns the date that is a specified number of workdays after a given start date, excluding weekends and holidays.
• NETWORKDAYS(start_date, end_date, holidays): This formula returns the number of workdays between two dates, excluding weekends and holidays.

3. Navigation Excel Formulas Cheat Sheet

• Go To: This formula allows users to quickly jump to a specific cell, range, or object within the worksheet or workbook.
• End: This formula helps users move to the last cell of the data in a specific column or row.
• Ctrl + Arrow Keys: This formula allows users to move quickly to the last cell in a column or row with data, in the direction of the arrow key pressed.
• Ctrl + Home: This formula takes users to the top left corner of the worksheet or workbook.
• Ctrl + End: This formula takes users to the last cell of the worksheet or workbook.
• Ctrl + Page Up/Page Down: This formula allows users to move between different worksheets in a workbook.
• Ctrl + Tab: This formula allows users to move between different open workbooks.

4. Lookup formulas (15 characters)

• VLOOKUP: This formula searches for a specific value in the first column of a range or table and returns a corresponding value in the same row from a specified column.
• HLOOKUP: This formula searches for a specific value in the first row of a range or table and returns a corresponding value in the same column from a specified row.
• INDEX: This formula returns a value from a specific row and column in a range or table.
• MATCH: This formula searches for a specific value in a range or table and returns the position of the value in the range.
• CHOOSE: This formula returns a value from a list of values based on the position specified.
• LOOKUP: This formula searches for a specific value in a range or table and returns a corresponding value in the same position from a specified row or column.
• OFFSET: This formula returns a reference to a range of cells based on a starting point and the number of rows and columns specified.
• INDIRECT: This formula returns a reference to a range of cells based on a text string representing the cell reference.

5. Math functions Excel Formulas Cheat Sheet

• SUM: Adds up a range of cells =SUM(cell range)
• AVERAGE: Calculates the average of a range of cells =AVERAGE(cell range)
• MAX: Returns the maximum value in a range of cells =MAX(cell range)
• MIN: Returns the minimum value in a range of cells =MIN(cell range)
• COUNT: Counts the number of cells that contain numbers in a range =COUNT(cell range)
• COUNTIF: Counts the number of cells that meet a specific criterion =COUNTIF(cell range, criterion)
• ROUND: Rounds a number to a specified number of decimal places =ROUND(number, num_digits)
• TRUNC: Truncates a number to a specified number of decimal places (cuts off extra digits) =TRUNC(number, num_digits)
• SQRT: Returns the square root of a number =SQRT(number)
• POWER: Raises a number to a specified power =POWER(number, power)
• MOD: Returns the remainder when one number is divided by another =MOD(dividend, divisor)
• ABS: Returns the absolute value of a number (always positive) =ABS(number)

6. Financial formulas

• Simple Interest: Calculates the interest on a loan or investment at a fixed rate over a set period. Formula: =P * r * n
• Compound Interest: Calculates the interest on a loan or investment where the interest earned is reinvested back into the principal, so the interest earned in subsequent periods grows. Formula: =P * (1 + r/n)^(n*t)
• Present Value: Calculates the current value of a future payment or investment, based on an assumed interest rate. Formula: =PV(r,n,pmt,fv)
• Future Value: Calculates the value of an investment at a future point in time, based on an assumed interest rate. Formula: =FV(r,n,pmt,pv)
• Net Present Value (NPV): Calculates the current value of a series of cash flows, discounted by an assumed interest rate. Formula: =NPV(r,cf1,cf2,...)
• Internal Rate of Return (IRR): Calculates the interest rate at which the net present value of a series of cash flows is equal to zero. Formula: =IRR(cf1, cf2,...)
• Amortization: Calculates the gradual reduction of a loan balance over time, based on an assumed interest rate and fixed payments. Formula: =PMT(r,n,pv)
• Debt Service Coverage Ratio (DSCR): Calculates the ability of a company to service its debt obligations. Formula: =Net Operating Income/Total Debt Service

7. Conditional functions

• IF: This function allows you to test a condition and return one value if the condition is true, and another value if the condition is false. The syntax is: =IF(logical_test, value_if_true, value_if_false).
• SUMIF: This function allows you to sum up a range of cells based on a condition. The syntax is: =SUMIF(range, criteria, [sum_range]).
• COUNTIF: This function allows you to count the number of cells in a range that meet a certain condition. The syntax is: =COUNTIF(range, criteria).
• AVERAGEIF: This function allows you to calculate the average of a range of cells that meet a certain condition. The syntax is: =AVERAGEIF(range, criteria, [average_range]).
• IFERROR: This function allows you to display a specific value if an error occurs in a formula. The syntax is: =IFERROR(value, value_if_error).