Excel for Accountants, The Magic to Know by Nick Sharrer

0758d8dc55548b1-261x361.jpg Author Nick Sharrer
File size 7.01MB
Year 2016
Pages 72
Language English
File format PDF
Category software


Excel for Accountants The Magic to Know © Nick Sharrer, CPA, CMA, CFM Table of Contents Beyond Basic Navigation Absolute Indexing Easy Formatting Wrap Text Merge and Center Pulldown for cell formatting Remove duplicates Left Right Mid Changing text formatting VLOOKUPS HLOOKUPS Paste Special Options Paste Special Values Paste Special Formulas Paste Special Formats Paste Special Transpose Paste Special Formula Text-to-Columns Width Delimited Concatenate Upper Lower Calculation options, large spreadsheets, and F9 Subtotal Group/Ungroup Sharing/Unsharing Protect workbook Freeze Panes Printing, repeat rows at the top Additional Formulas Intermediate If statements, including if (isblank): IF(AND) ISBLANK IFERROR Find, select, blank LEN function Conditional formatting SUMIF Trace Precedents/Dependents Hyperlink for referencing Before we talk about pivot tables, let’s talk about Charts, and Graphs Pivot Tables Count/Sum Report Filters Formatting Rows Layout and Print Repeating Rows Pivot Table Quick Formats Links and Formulas from Pivot Tables Calculated Fields Named Ranges Advanced Macros Recording Function Let’s start the Macro Working in Sections Attaching macros to a PERSONAL.xlsb file to create your own formulas buttons, available in each instance of Excel Attaching Macro as a function to the quick access toolbar Attaching to new ribbon, and creation of new ribbon Beyond Basic This document assumes a very basic understanding of Excel. The 97-2003 format is different than what we will be focusing on (the 2010-2013 format). The significant upgrades were the navigation through ribbons instead of pull-down menus, and the expansion of the data size limits (e.g. 1,000,000+ rows instead of a limit of 65,000 rows). Note that systems such as Yardi still export into the 97-2003 format, and to enable the newer features you must save them as an .xlsx file and reopen them for the functionality to appear. This document is also intended to focus on the functionality within Excel that can help most accountants. Excel is capable of much more, but this is designed to teach you skills that you can apply quickly in your everyday procedures. If this peaks your interest, there are even more in-depth and advanced training books on the subject, but they will require a greater time investment. Navigation: Instead of scrolling in Excel, it’s easier to use hot keys. There are many hot keys available, but the most commonly used is: End-Arrow: When you’re navigating, you can press and release the “end” key, and then choose the left, right, up, or down, arrow to move to the end of the data the selected cell is currently in. You may hold down the shift key to highlight this data if you wish: Highlight A1, hold shift, press end, release just the end key, and now press your direction arrow: Data Fill – Handlebar: When you’ve recently entered a formula to the right of a dataset and you wish to populate it for the entire dataset in its new column, simply find the handlebar and double click. This will auto fill: Now double click the handlebar, and it will autofill to the end of your dataset. Absolute Indexing: When you wish to create a reference to a cell that won’t change even if you copy it anywhere else within the spreadsheet, place dollar signs before the row and column reference. If you only desire to retain the row or the column, then you can only add the dollar sign to that specifically. To quickly perform this, you can select the value in your reference, and use the “F4” button to do this: Now you can copy the referencing cell and it will maintain the reference to the original cell, columns, or rows that you have designated. Easy Formatting: The “Home” ribbon contains many quick formatting options. Wrap Text: When you wish for the text to be visible but still contained within one cell, once the narrative has become longer than the cell width, the following button will do this for you. Consequently, if a cell is already in “wrap-text” formatting, highlighting the cell and clicking this button will change the format back to the standard. Merge and Center: Especially if you wish to place a header at the top of one report, merging and forcing the left most value to become the center within those merged cells is at the click of a button: If you receive a report that has many merged cells, it will prevent you from proceeding with many formulas that you may wish to add to the file. Simply highlight the whole sheet and then press the button to unmerge the cells just as easily. Pulldown for cell formatting: For quick formatting of cells, you no longer need to right click and choose the cell formatting through the long route. Simply highlight the cells you want, and use the quick formatting drop-down: Remove duplicates: We often receive lists with repeated attributes (this is normalized data as we will cover in the intermediate session), but let’s say that you just want to know the unique values in a particular column. First, copy what you want from anything you intend to use for something else and paste it elsewhere, such as another sheet. Then highlight the data, and choose the remove duplicates function: Trim: Some system reports add an extra space at the beginning or end of a populated cell, which causes problems especially when using vlookup formulas. To eliminate extra spaces, and to make cell values comparable to other cell values, use the following: Notice that the cell now excludes excess spaces. Left: Often we only want the first few characters of a description; perhaps the values we want are just the first 5 characters in a much longer string. Use the left function: Right: This is similar to the left function, but it allows you to pull just the characters you want from the right of the text. This is often powerful in combination between the two (you can perform a left(right()) function when you want the first X characters of the last X+ characters in a text string: Mid: Often what we want is consistently in the middle of a text string. Use the following formula: The first number is how many characters from the left you want to start pulling, the last number is how too many characters to pull in total starting with the designated character. Changing text formatting: When performing Vlookups or any other formula, you may find that your formula pulls errors, or won’t include a particular cell. This is because Excel often holds onto the formatting for entered data as text, even if the cell has been told that the data should be in general or numerical format. We can fix that. The way to recognize it is that Excel will include a green triangle telling you that it’s experiencing this problem: To fix this, enter the following to the right of the data: Then take the second column with the new formula, copy the values, and paste special values over the original cells. The green arrow will disappear, and in the example given it will just display the unrestricted number without the green triangle, allowing it to be used in formulas: . VLOOKUPS: This is one of the most important functions you will need in Excel! This is used to locate a value contained in a separate list, relating to the value in the list you’re currently working on. The following contains a table, with a lonely value that we want to find the corresponding letter from our table to complete. Enter “=VLOOKUP(“value to look for”, “where to search”, “the count of the cells to the right of the value to return as your desired value”, and then in accounting always choose “FALSE”). The reason for choosing “FALSE” is to tell Excel that if the value is not found, please return an error symbol “#N/A”, which is easily identifiable, otherwise, a “TRUE” entry for the last portion of the statement will tell Excel that it’s allowed to guess, in which case it may return what it thinks is close but not exactly what you’re looking for, and in accounting we need precision, or to know when the value was simply not found. Result: HLOOKUPS: These are rarely used in accounting, but you may find times in which they are necessary. They’re “Horizontal” lookups, as opposed to “Vertical” lookups (VLOOKUPS). Similarly, they require a value for which you’re looking to find a corresponding value for within a pre-set table, only for this table, the values exist horizontally, and not vertically: The importance of unique identifiers: When performing VLOOKUPS or HLOOKUPS, the common thread between the two data sets is called a “unique identifier”. Unique identifiers must be exactly the same between the two data sets in order for them to recognize each other and for these formulas to find and select the related data you wish to return. Paste Special Options: Paste special can be used for several useful tasks, some of the most critical follow: Paste Special Values: After copying what you intend to paste, right click where you intend to paste it, and choose the button below: Paste Special Formulas: Paste Special Formats: Paste Special Transpose: Paste Special Formula: Frequently you may find that you want to divide the numbers in one column by another, or multiply everything in a particular column by a factor (e.g. -1 to reverse the signage, 2 to double the value, etc.), and all of this is achievable through the paste special values “operation” function. First type the factor that you want or many values if you are adding or subtracting, and then perform the following: This works equally well with “subtract”. Text-to-Columns: If a column has too much information, and it’s in a pattern that you would like to separate into multiple columns, there are two ways to do this through the “text-to-columns” feature: Here’s an example we will be working with. Width: Place the cursor and click where you would like the width to break apart the text. When you’re ready, press “Next” or “Finish” and the single column will become multiple columns with data in them. Delimited: This will only work if there is a common character separating the desired data, and you want to delete the separating character through this process as well: A semicolon was previously noted as the separating character. What you see in the data preview screen is what Excel will be creating for new columns once you finish. Concatenate: This function allows you to combine fields, or to combine and add additional text to many fields in a dataset. The simple concatenate formula will combine B and C to retrieve “BC”. If you want to add something before, after, or in between, then place the value as a separate addition to the formula in quotes. The formula above will retrieve: “B I’d like to add something C” Upper: If you don’t like the formatting of the reports that you receive, you can change the case of the text that you receive. If you want everything to be upper cased, use the “upper” function: This will retrieve “ONE” as the end value. Lower: If you wish to perform the opposite, and convert something to lower case, use the “lower” function: This will retrieve “one” as the end value. Calculation options, large spreadsheets, and F9: Excel defaults to automatically update calculations as changes are being made within the spreadsheet. However, with large files this can cause Excel to run slowly or to even crash. One method to prevent this when files start to experience this is to turn off the automatic calculations, and then remember to manually update the calculations before finalizing the file: Then press the “F9” key every time that you want to run the calculations. You can always turn the automatic calculations on at a later time if the file should become smaller. Subtotal: The normal SUM function will include values within the specified range, whether or not they are visible or have been filtered. If you only want to report the sum of the visible cells, use the subtotal function: Group/Ungroup: When performing complex calculations, you usually don’t want to lose your documentation, but the individual steps may not be necessary for the initial presentation. You can group rows or columns so that they can be hidden or shown together with a button on the side of the sheet allowing anyone to know that there are cells available for viewing if desired. If you later change your mind, you may highlight the columns or rows and choose “ungroup”: Sharing/Unsharing: It’s frequently useful to allow multiple users to make changes or add information to the same workbook simultaneously; otherwise Excel will only allow one user to edit a workbook during a given time. For these types of projects, share the workbook: To unshare the workbook, simply return to this screen and uncheck the box to disallow sharing. Protect workbook: Often we don’t want others changing the information we have prepared, but want them to be allowed to see what we have prepared; this is where we can protect an entire workbook or a single sheet: Enter your password, and then only those who know the password can unlock the workbook or single sheet to allow editing. Freeze Panes: When creating long reports or lists, we will frequently need to see the headers at the top of the lists in order to track what the values continue to represent as we move down the list: Select the cell below and to the right of where you want to freeze your headers or row descriptions Now the headers or row descriptions will follow your view as you change the view location within the sheet. Printing, repeat rows at the top: When we’re printing reports that are too long to fit on just one page, but the information would be lost without the headers repeating at the top of each printed page, we can tell Excel to print them at the top of every page when it prints, no matter how long or short we make the report: Now when the sheet prints, columns one through three will appear at the top of every page. This can be done for columns as well if printing information from a landscape format. Additional Formulas: There are many more formulas that can make your life simpler, I encourage everyone to look into the following options already setup in Excel. Each comes with a “wizard” which will attempt to guide you through the formula as you insert it. Excel is capable of many complicated functions, including some primarily used by engineers and actuaries. Intermediate If statements, including if (isblank): If statements are logical statements to retrieve different values depending on the conditions for which you specify: IF(AND): If statements can be expanded for more than one criterion. Let’s say that you want two items to be true in order for the cell to represent one formula, otherwise you’d like it to show another: ISBLANK: Now, if you want Excel to identify blanks, but return an entire column that can be filtered by itself use the following: IFERROR: Now let’s say that you’re running a vlookup, and you don’t want unfound values to appear as “#N/A” you just want them to come over without a value. This function is meant to be an additional criterion for another embedded function, most commonly a vlookup. Appears as: Try the following: And this is what you’ll get:

Author Nick Sharrer Isbn File size 7.01MB Year 2016 Pages 72 Language English File format PDF Category Software Book Description: FacebookTwitterGoogle+TumblrDiggMySpaceShare In the financial field Excel isn’t just a medium, it’s your greatest tool. When you unlock its potential there’s very little that you can’t do with financial data processing and report preparation. Even if you only save 15 minutes a day, that’s 65 hours per year! Reports that used to take hours to compile and format can now be completed in a matter of seconds. You can create your own functions and add them to your instance of Excel uniquely to your computer so frequent tasks become only a click of a button . . . if you know how. And it’s easy. Once you’ve learned these formulas and methods you won’t know how you managed to operate without them. This book was written to be understood in a quick reference method, and to make power users out of beginners . . . and “Excel magicians” out of regular users.     Download (7.01MB) Excel: The Complete Beginners Guide Create and Learn Excel in One day: Learn Ms Excel in one day by creating a Dynamic Infographic Absolute Beginner’s Guide To Microsoft Office Access 2003 Excel 2016 Formulas, Charts, And PivotTable Excel Formulas: 140 Excel Formulas and Functions with usage and examples Load more posts

Leave a Reply

Your email address will not be published. Required fields are marked *