Excel for Accountants
The Magic to Know
© Nick Sharrer, CPA, CMA, CFM
Table of Contents
Merge and Center
Pulldown for cell formatting
Changing text formatting
Paste Special Options
Paste Special Values
Paste Special Formulas
Paste Special Formats
Paste Special Transpose
Paste Special Formula
Calculation options, large spreadsheets, and F9
Printing, repeat rows at the top
If statements, including if (isblank):
Find, select, blank
Hyperlink for referencing
Before we talk about pivot tables, let’s talk about Charts, and Graphs
Layout and Print
Pivot Table Quick Formats
Links and Formulas from Pivot Tables
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
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
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.
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
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
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
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
To fix this, enter the following to the right of the data:
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
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.
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
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.
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
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
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
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
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.
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.
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 Beginners 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