Microsoft Excel Formulas and Shortcuts Cheat Sheet, Cheat Sheet of Accounting

Download Microsoft Excel Formulas and Shortcuts Cheat Sheet and more Cheat Sheet Accounting in PDF only on Docsity! MICROSOFT EXCEL SHORTCUT KEYS THE MICROSOFT EXCEL FORMULAS CHEAT SHEET DATE AND TIME FORMULAS Show today’s date in a cell Show the date and time=NOW Show the current date without the time=TODAY() Show current month in a cell=MONTH(TODAY()) Add 10 days to current date=TODAY()+10 =DAY(TODAY()) COUNTING AND ROUNDING FORMULAS Calculates the sum of a group of values=SUM Calculates the mean of a group of values=AVERAGE Counts the number of cells in a range that contains numbers=COUNT Removes the decimal portion of a number=INT Rounds a number to a specified number of decimal places=ROUND Returns the date, without the time=NOW Tests for a true or false condition=IF Rounds 1.45 to one decimal place=ROUND(1.45, 1) Count the number of non-blank cells in a range=COUNTA(A1:A5) Returns the date, without the time=TODAY Calculates the mean of a group of values=AVERAGE Returns the logical value TRUE=TRUE Rounds -1.457 to two decimal places=ROUND(-1.457, 2) Returns the logical value FALSE=FALSE MICROSOFT EXCEL SHORTCUT KEYS COUNTING AND ROUNDING FORMULAS (CONT.) Calculates the sum of a group of values=COUNTIF Calculates a sum from a group of values in which a condition has been met` =SUMIF Returns TRUE if all of its arguments are TRUE=AND Returns TRUE if any argument is TRUE=OR UNIT CONVERSION FORMULAS Converts value of A1 from days to hours =CONVERT(A1,”DAY”,”HR”) Converts value of A1 from Celsius to Fahrenheit =CONVERT(A1,”C”,”F”) Converts value of A1 from hours to minutes =CONVERT(A1,”HR”,”MN”) Converts value of A1 from teaspoons to tablespoons =CONVERT(A1,”TSP”,”TBS”) Converts value of A1 from years to days =CONVERT(A1,”YR”, “DAY”) Converts value of A1 from gallons to liters !ERROR! A1 does not contain a number or expression Converts value of A1 from miles to kilometers =CONVERT(A1,”MI”,”KM”) Converts value of A1 from centimeters to inches =CONVERT(A1,”CM”,”IN”) Converts value of A1 from kilometers to miles =CONVERT(A1,”KM”,”MI”) Converts binary 1100100 to decimal (100) =BIN2DEC(1100100) Converts a number into a Roman numeral =ROMAN Converts value of A1 from inches to feet =CONVERT(A1,”IN”,”FT”) MICROSOFT EXCEL SHORTCUT KEYS FINANCE FORMULAS Calculates the interest rate for a fully invested security=INTRATE Calculates the future value of an initial principal after applying a series of compound interest rates =FVSCHEDULE Calculates the net present value of cash flows based on a discount rate =NPV Calculates the future value of an investment=FV Calculates the depreciation for each accounting period=AMORLINC Calculates the interest payment for an investment for a given period =IPMT Calculates the price per $100 face value of a periodic coupon bond =PRICE Calculates the effective annual interest rate=EFFECT Calculates the total payment (debt and interest) on a debt security =PMT Calculates the yield of a security based on maturity, face value, and interest rate =YIELD Calculates the accrued interest for a security that pays interest at maturity =ACCRINTM Calculates the accrued interest for a security that pays periodic interest =ACCRINT © 2021 CustomGuide, Inc. Click the topic links for free lessons! Contact Us: sales@customguide.com Columns Microsoft® Excel Cheat Sheet Basic Skills The Excel Program Screen Keyboard Shortcuts Getting Started Create a Workbook: Click the File tab and select New or press Ctrl + N. Double-click a workbook. Open a Workbook: Click the File tab and select Open or press Ctrl + O. Select a recent file or navigate to the location where the file is saved. Preview and Print a Workbook: Click the File tab and select Print. Undo: Click the Undo button on the Quick Access Toolbar. Redo or Repeat: Click the Redo button on the Quick Access Toolbar. The button turns to Repeat once everything has been re-done. Use Zoom: Click and drag the zoom slider to the left or right. Select a Cell: Click a cell or use the keyboard arrow keys to select it. Select a Cell Range: Click and drag to select a range of cells. Or, press and hold down the Shift key while using the arrow keys to move the selection to the last cell of the range. Select an Entire Worksheet: Click the Select All button where the column and row headings meet. Select Non-Adjacent Cells: Click the first cell or cell range, hold down the Ctrl key, and select any non-adjacent cell or cell range. Cell Address: Cells are referenced by the coordinates made from their column letter and row number, such as cell A1, B2, etc. Jump to a Cell: Click in the Name Box, type the cell address you want to go to, and press Enter. Change Views: Click a View button in the status bar. Or, click the View tab and select a view. Recover an Unsaved Workbook: Restart Excel. If a workbook can be recovered, it will appear in the Document Recovery pane. Or, click the File tab, click Recover unsaved workbooks to open the pane, and select a workbook from the pane. General Open a workbook ................ Ctrl + O Create a new workbook ....... Ctrl + N Save a workbook ................. Ctrl + S Print a workbook ................. Ctrl + P Close a workbook ................ Ctrl + W Help .................................... F1 Activate Tell Me field ............ Alt + Q Spell check ......................... F7 Calculate worksheets .............. F9 Create absolute reference ... F4 Navigation Move between cells ............. , , , → Right one cell ...................... Tab Left one cell ........................ Shift + Tab Down one cell ..................... Enter Up one cell .......................... Shift + Enter Down one screen ................ Page Down To first cell of active row ...... Home Enable End mode ................ End To cell A1 ............................ Ctrl + Home To last cell ........................... Ctrl + End Editing Cut ..................................... Ctrl + X Copy ................................... Ctrl + C Paste .................................. Ctrl + V Undo .................................. Ctrl + Z Redo ................................... Ctrl + Y Find .................................... Ctrl + F Replace .............................. Ctrl + H Edit active cell ..................... F2 Clear cell contents ............... Delete Formatting Bold .................................... Ctrl + B Italics .................................. Ctrl + I Underline ............................ Ctrl + U Open Format Cells Ctrl + Shift dialog box ........................... + F Select All ............................. Ctrl + A Select entire row ................. Shift + Space Select entire column ............ Ctrl + Space Hide selected rows .............. Ctrl + 9 Hide selected columns......... Ctrl + 0 Quick Access Toolbar Title Bar Formula Bar Close Button Ribbon File Tab Name Box Rows Scroll Bars Active Cell Views Zoom Slider Worksheet Tab Free Cheat Sheets Visit ref.customguide.com © 2021 CustomGuide, Inc. Click the topic links for free lessons! Contact Us: sales@customguide.com Edit a Workbook Edit a Cell’s Contents: Select a cell and click in the Formula Bar or double-click the cell. Edit the cell’s contents and press Enter. Clear a Cell’s Contents: Select the cell(s) and press the Delete key. Or, click the Clear button on the Home tab and select Clear Contents. Cut or Copy Data: Select cell(s) and click the Cut or Copy button on the Home tab. Paste Data: Select the cell where you want to paste the data and click the Paste button in the Clipboard group on the Home tab. Preview an Item Before Pasting: Place the insertion point where you want to paste, click the Paste button list arrow in the Clipboard group on the Home tab, and hold the mouse over a paste option to preview. Paste Special: Select the destination cell(s), click the Paste button list arrow in the Clipboard group on the Home tab, and select Paste Special. Select an option and click OK. Move or Copy Cells Using Drag and Drop: Select the cell(s) you want to move or copy, position the pointer over any border of the selected cell(s), then drag to the destination cells. To copy, hold down the Ctrl key before starting to drag. Find and Replace Text: Click the Find & Select button, select Replace. Type the text you want to find in the Find what box. Type the replacement text in the Replace with box. Click the Replace All or Replace button. Check Spelling: Click the Review tab and click the Spelling button. For each result, select a suggestion and click the Change/Change All button. Or, click the Ignore/Ignore All button. Insert a Column or Row: Right-click to the right of the column or below the row you want to insert. Select Insert in the menu, or click the Insert button on the Home tab. Delete a Column or Row: Select the row or column heading(s) you want to remove. Right- click and select Delete from the contextual menu, or click the Delete button in the Cells group on the Home tab. Hide Rows or Columns: Select the rows or columns you want to hide, click the Format button on the Home tab, select Hide & Unhide, and select Hide Rows or Hide Columns. Basic Formatting Change Cell Alignment: Select the cell(s) you want to align and click a vertical alignment , , button or a horizontal alignment , , button in the Alignment group on the Home tab. Format Text: Use the commands in the Font group on the Home tab or click the dialog box launcher in the Font group to open the dialog box. Format Values: Use the commands in the Number group on the Home tab or click the dialog box launcher in the Number group to open the Format Cells dialog box. Wrap Text in a Cell: Select the cell(s) that contain text you want to wrap and click the Wrap Text button on the Home tab. Merge Cells: Select the cells you want to merge. Click the Merge & Center button list arrow on the Home tab and select a merge option. Cell Borders and Shading: Select the cell(s) you want to format. Click the Borders button and/or the Fill Color button and select an option to apply to the selected cell. Copy Formatting with the Format Painter: Select the cell(s) with the formatting you want to copy. Click the Format Painter button in the Clipboard group on the Home tab. Then, select the cell(s) you want to apply the copied formatting to. Adjust Column Width or Row Height: Click and drag the right border of the column header or the bottom border of the row header. Double- click the border to AutoFit the column or row according to its contents. Basic Formulas Enter a Formula: Select the cell where you want to insert the formula. Type = and enter the formula using values, cell references, operators, and functions. Press Enter. Insert a Function: Select the cell where you want to enter the function and click the Insert Function button next to the formula bar. Reference a Cell in a Formula: Type the cell reference (for example, B5) in the formula or click the cell you want to reference. SUM Function: Click the cell where you want to insert the total and click the Sum button in the Editing group on the Home tab. Enter the cells you want to total, and press Enter. MIN and MAX Functions: Click the cell where you want to place a minimum or maximum value for a given range. Click the Sum button list arrow on the Home tab and select either Min or Max. Enter the cell range you want to reference, and press Enter. COUNT Function: Click the cell where you want to place a count of the number of cells in a range that contain numbers. Click the Sum button list arrow on the Home tab and select Count Numbers. Enter the cell range you want to reference, and press Enter. Complete a Series Using AutoFill: Select the cells that define the pattern, i.e. a series of months or years. Click and drag the fill handle to adjacent blank cells to complete the series. Insert an Image: Click the Insert tab on the ribbon, click either the Pictures or Online Pictures button in the Illustrations group, select the image you want to insert, and click Insert. Insert a Shape: Click the Insert tab on the ribbon, click the Shapes button in the Illustrations group, and select the shape you wish to insert. Hyperlink: Text or Images: Select the text or graphic you want to use as a hyperlink. Click the Insert tab, then click the Link button. Choose a type of hyperlink in the left pane of the Insert Hyperlink dialog box. Fill in the necessary informational fields in the right pane, then click OK. Modify Object Properties and Alternative Text: Right-click an object. Select Edit Alt Text in the menu and make the necessary modifications under the Properties and Alt Text headings. View and Manage Worksheets Insert a New Worksheet: Click the Insert Worksheet button next to the sheet tabs below the active sheet. Or, press Shift + F11. Delete a Worksheet: Right-click the sheet tab and select Delete from the menu. Hide a Worksheet: Right-click the sheet tab and select Hide from the menu. Rename a Worksheet: Double-click the sheet tab, enter a new name for the worksheet, and press Enter. Change a Worksheet’s Tab Color: Right-click the sheet tab, select Tab Color, and choose the color you want to apply. Move or Copy a Worksheet: Click and drag a worksheet tab left or right to move it to a new location. Hold down the Ctrl key while clicking and dragging to copy the worksheet. Switch Between Excel Windows: Click the View tab, click the Switch Windows button, and select the window you want to make active. Freeze Panes: Activate the cell where you want to freeze the window, click the View tab on the ribbon, click the Freeze Panes button in the Window group, and select an option from the list. Select a Print Area: Select the cell range you want to print, click the Page Layout tab on the ribbon, click the Print Area button, and select Set Print Area. Adjust Page Margins, Orientation, Size, and Breaks: Click the Page Layout tab on the ribbon and use the commands in the Page Setup group, or click the dialog box launcher in the Page Setup group to open the Page Setup dialog box. Basic Formatting Insert Objects © 2021 CustomGuide, Inc. Click the topic links for free lessons! Contact Us: sales@customguide.com Microsoft® Excel Cheat Sheet Advanced Skills PivotTable Elements PivotTables Create a PivotTable: Select the data range to be used by the PivotTable. Click the Insert tab on the ribbon and click the PivotTable button in the Tables group. Verify the range and then click OK. Add Multiple PivotTable Fields: Click a field in the field list and drag it to one of the four PivotTable areas that contains one or more fields. Filter PivotTables: Click and drag a field from the field list into the Filters area. Click the field’s list arrow above the PivotTable and select the value(s) you want to filter. Group PivotTable Values: Select a cell in the PivotTable that contains a value you want to group by. Click the Analyze tab on the ribbon and click the Group Field button. Specify how the PivotTable should be grouped and then click OK. Refresh a PivotTable: With the PivotTable selected, click the Analyze tab on the ribbon. Click the Refresh button in the Data group. Format a PivotTable: With the PivotTable selected, click the Design tab. Then, select desired formatting options from the PivotTable Options group and the PivotTable Styles group PivotCharts Create a PivotChart: Click any cell in a PivotTable and click the Analyze tab on the ribbon. Click the PivotChart button in the Tools group. Select a PivotChart type and click OK. Modify PivotChart Data: Drag fields into and out of the field areas in the task pane. Refresh a PivotChart: With the PivotChart selected, click the Analyze tab on the ribbon. Click the Refresh button in the Data group. Modify PivotChart Elements: With the PivotChart selected, click the Design tab on the ribbon. Click the Add Chart Element button in the Chart Elements group and select the item(s) you want to add to the chart. Apply a PivotChart Style: Select the PivotChart and click the Design tab on the ribbon. Select a style from the gallery in the Chart Styles group. Update Chart Type: With the PivotChart selected, click the Design tab on the ribbon. Click the Change Chart Type button in the Type group. Select a new chart type and click OK. Enable PivotChart Drill Down: Click the Analyze tab. Click the Field Buttons list arrow in the Show/Hide group and select Show Expand/Collapse Entire Field Buttons. The PivotTable Fields pane controls how data is represented in the PivotTable. Click anywhere in the PivotTable to activate the pane. It includes a Search field, a scrolling list of fields (these are the column headings in the data range used to create the PivotTable), and four areas in which fields are placed. These four areas include: Filters: If a field is placed in the Filters area, a menu appears above the PivotTable. Each unique value from the field is an item in the menu, which can be used to filter PivotTable data. Column Labels: The unique values for the fields placed in the Columns area appear as column headings along the top of the PivotTable. Row Labels: The unique values for the fields placed in the Rows area appear as row headings along the left side of the PivotTable. Values: The values are the “meat” of the PivotTable, or the actual data that’s calculated for the fields placed in the rows and/or columns area. Values are most often numeric calculations. Not all PivotTables will have a field in each area, and sometimes there will be multiple fields in a single area. PivotTable Layout PivotTable Fields Pane The Layout Group Subtotals: Show or hide subtotals and specify their location in the PivotTable. Grand Totals: Add or remove grand total rows for columns and/or rows. Report Layout: Adjust the report layout to show in compact, outline, or tabular form. Blank Rows: Emphasize groups of data by manually adding blank rows between grouped items. Free Cheat Sheets Visit ref.customguide.com Field List PivotTable Field Areas PivotTable Fields Pane Fields Pane Options Tools Menu Search PivotTable Fields Active PivotTable © 2021 CustomGuide, Inc. Click the topic links for free lessons! Contact Us: sales@customguide.com Macros Enable the Developer Tab: Click the File tab and select Options. Select Customize Ribbon at the left. Check the Developer check box and click OK. Record a Macro: Click the Developer tab on the ribbon and click the Record Macro button. Type a name and description then specify where to save it. Click OK. Complete the steps to be recorded. Click the Stop Recording button on the Developer tab. Run a Macro: Click the Developer tab on the ribbon and click the Macros button. Select the macro and click Run. Edit a Macro: Click the Developer tab on the ribbon and click the Macros button. Select a macro and click the Edit button. Make the necessary changes to the Visual Basic code and click the Save button. Delete a Macro: Click the Developer tab on the ribbon and click the Macros button. Select a macro and click the Delete button. Macro Security: Click the Developer tab on the ribbon and click the Macro Security button. Select a security level and click OK. Troubleshoot Formulas Common Formula Errors: • ####### - The column isn’t wide enough to display all cell data. • #NAME? - The text in the formula isn’t recognized. • #VALUE! - There is an error with one or more formula arguments. • #DIV/0 - The formula is trying to divide a value by 0. • #REF! - The formula references a cell that no longer exists. Trace Precedents: Click the cell containing the value you want to trace and click the Formulas tab on the ribbon. Click the Trace Precedents button to see which cells affect the value in the selected cell. Error Checking: Select a cell containing an error. Click the Formulas tab on the ribbon and click the Error Checking button in the Formula Auditing group. Use the dialog to locate and fix the error. The Watch Window: Select the cell you want to watch. Click the Formulas tab on the ribbon and click the Watch Window button. Click the Add Watch button. Ensure the correct cell is identified and click Add. Evaluate a Formula: Select a cell with a formula. Click the Formulas tab on the ribbon and click the Evaluate Formula button. Advanced Formatting Customize Conditional Formatting: Click the Conditional Formatting button on the Home tab and select New Rule. Select a rule type, then edit the styles and values. Click OK. Edit a Conditional Formatting Rule: Click the Conditional Formatting button on the Home tab and select Manage Rules. Select the rule you want to edit and click Edit Rule. Make your changes to the rule. Click OK. Change the Order of Conditional Formatting Rules: Click the Conditional Formatting button on the Home tab and select Manage Rules. Select the rule you want to re-sequence. Click the Move Up or Move Down arrow until the rule is positioned correctly. Click OK. Analyze Data Goal Seek: Click the Data tab on the ribbon. Click the What-If Analysis button and select Goal Seek. Specify the desired value for the given cell and which cell can be changed to reach the desired result. Click OK. Advanced Formulas Nested Functions: A nested function is when one function is tucked inside another function as one of its arguments, like this: IF: Performs a logical test to return one value for a true result, and another for a false result. AND, OR, NOT: Often used with IF to support multiple conditions. • AND requires multiple conditions. • OR accepts several different conditions. • NOT returns the opposite of the condition. SUMIF and AVERAGEIF: Calculates cells that meet a condition. • SUMIF finds the total. • AVERAGEIF finds the average. Advanced Formulas VLOOKUP: Looks for and retrieves data from a specific column in a table. HLOOKUP: Looks for and retrieves data from a specific row in a table. UPPER, LOWER, and PROPER: Changes how text is capitalized. UPPER Case | lower case | Proper Case LEFT and RIGHT: Extracts a given number of characters from the left or right. MID: Extracts a given number of characters from the middle of text; the example below would return “day”. MATCH: Locates the position of a lookup value in a row or column. INDEX: Returns a value or the reference to a value from within a range. Jan Feb Total 13,020 7,0106,010