More "Database" Functionality in Excel

Continually trying to expand on what I've already covered, I want to discuss some features of Excel that you can use in place of Pivot Tables or VLOOKUP's for simple "queries". In this post, I would like to discuss the "SUMIF" formula and the "COUNTIF" formula. These allow you to get subtotals for a given range without having to manually parse the data. In one of my recent posts, I discussed Pivot Tables. These are extremely powerful tools, and I suggest you learn to use them whenever possible. Even so, sometimes you may not want to go through the motions of creating a pivot table for a quick and dirty analysis. In these instances, you can use one of these two formulas.

The simpler of the two is COUNTIF. You simply type "=COUNTIF(" and then highlight the range you want to evaluate, then a comma followed by the evaluation criteria (typically in quotes). For example, to count the number of cells that equal the word January, you might type "=COUNTIF(A1:A10, "January")".

The SUMIF is slightly more complicated, but not much. You can keep it just as simple as COUNTIF by using the same methodology. For example, "=SUMIF(A1:A10, "<100")" will sum the values in cells A1 - A10 that are less than $100. But what if you wanted the sales totals for the month of January? In this case, you'll have to get a little more complicated by using 2 ranges: 1 for the criteria and 1 for the values. Following our original example, your formula would be: "=SUMIF(A1:A10, "January", B1:B10)" (assuming month names are in column A and the corresponding sales figures are in column B). Simple, eh?

You can get more complicated by combining the two. For example, to get the average sale in January, you would use SUMIF followed by COUNTIF, then divide the first result by the second. You get the idea. Good luck!

"Queries" in Excel

In my one of my last articles, I discussed how and why you might want to use named ranges. In that post, however, I did not discuss one of the main reasons I use named ranges: lookup tables. The heading of this post does not relate to the "Data | Import External Data | New ? Query...", but rather to the possibilities relating to quickly looking up a data item in a table. For example, perhaps you have a listing of states such as:

PA, Pennsylvania
CA, California
FL, Florida
NY, New York
...

If you (for whatever reason) wanted to type PA somewhere in your spreadsheet and have another cell determine what the state name is, you could use a VLOOKUP formula to do just that. If your data were horizontal, such as:

PA, CA, FL, NY...
Pennsylvania, California, Florida, New York...

You could instead use HLOOKUP (H for horizontal, V for vertical). We'll discuss VLOOKUP, but HLOOKUP is exactly the same except for the transposition of the table. To use VLOOKUP, three arguments are required and one argument is optional:

1. The value you want to look up (PA in our example).
2. The table where Excel should look for the value.
3. The OFFSET from the beginning of the table where the value is located*.
4. Does the value to lookup need to be exact, or can it be as close as possible?
* This requires a little more explaining. Essentially, you are looking up a value in a given table. When you highlight the table, you will have a certain number of rows (in our example, 4 rows) and you will have a certain number of columns (in our example, 2 columns). This argument is asking for the column in which it should return the value if a match is found (in our example, we wanted the 2nd column so that the lookup would return the name of the state).

Therefore, to execute the formula we would write "=VLOOKUP(A1, States!$A$1:$B$4, 2 FALSE)". This assumes that we want to look up the name of the state for the abbreviation located in cell A1 from the table on the States sheet, cells A1 - B4, we want it to return the 2nd column (the state names) and the match as to be EXACT. A close enough match would not be appropriate; for example, NY might be closest to NJ (if NY were missing in the table) and that would return New Jersey for NY, where an error would be more appropriate.

The reason I mention named ranges is because this formula would be much more readable if you changed the "States!$A$1:$B$4" part of the formula to (instead): "State_Table". To do this, you could name the range on the states tab by highlighting cells A1 to B4, hitting Ctrl-F3, and then naming the range "State_Table". Again, the 2 main reasons to use named ranges are: 1) Clarity and 2) Ease of verification. The formula would now read "=VLOOKUP(A1, State_Table, 2, FALSE)".

This example is fairly simplistic, but a more realistic example might include a listing of stock tickers along with the company name as well as the exchange that the stock is traded on, etc. You could type the ticker (or perhaps the company name is easier to remember), and then have a formula look everything else up for you.

The few main things to remember are:

1. The value you want to look up MUST be in the first column of the table.
2. You can only look up values in columns that are included in the table. For example, you could not look up a third column in our first example as we only included two columns in the formula (recall: "States!A1:B4" references 2 columns - A & B).
3. The last argument of the formula is very important if you want exact values. You might not want exact values, perhaps, if you were looking up bond ratings and ratios between x and y were in this particular rating, and those between a and b are in another rating. Exact lookups, on the other hand, would be required if you were looking up ticker symbols; a "close-enough" match wouldn't really make sense in that case.
4. If you're using an HLOOKUP formula, the 3rd argument refers to which row to return (not which column as in a VLOOKUP).

Anyway, hope this gives you a little insight into how you can get "mini-queries" in Excel!

Conditional Formatting

Conditional formatting can help you extract information from a spreadsheet visually in more powerful ways that normal "messages". For example, lets say that you are manually updating a balance sheet and you have a formula at the end that compares total assets to total liabilities + owners equity. Normally you will look to this formula and see if you need to make changes (that is, check if the number is -0- or not). Conditional formatting, however, lets you go much further by adding criteria that says "If the value of this cell does not equal -0-, then color the background yellow, the font bold and red, and put a border around the outside." Now it becomes much more clear that something needs to be done to fix the error.

To use conditional formatting, lets follow along our example. I'll assume you know how to create the balance sheet and parity check. Now all you have to do is go to the cell where the parity check is located (i.e., click on that cell with your mouse). Once the cell is the active cell (note that you should not enter the cell, just sit on top of it), go to Format | Conditional Formatting.... Now, in the "Condition 1" (you can add more for more complicated examples), you'll say "Cell Value Is" ... "not equal to" ... 0. This is the check that is performed by the conditional formatter. To change the format when this occurs, hit the "Format..." button and change to your hearts desire. Add and delete scenarios on queue. Now, the cell looks normal except when a non-zero value occurs!

Named Ranges

A quick, but powerful tip that I wanted to offer revolves around named ranges in Excel. Whenever you're creating a complicated spreadsheet, keep in mind that you may not always remember exactly what you were doing when you first created your masterpiece. Named ranges can be of tremendous use when you're later trying to rekindle that flame in a last minute effort to get your product out the door. For example, "=Sales-COGS" makes much more sense than "=B4-B7". While this example is clearly contrived and trivial, when you have a 2MB Excel file with 15 sheets, it will not seem so trivial.

To use named ranges, simply select the cell (or group of cells) that you want to refer to by name, and hit Ctrl-F3. This will bring up a dialog allowing you to name your range. Once saved, you can refer to the name later either by explicitly typing it in a cell, or by hitting F3 and selecting it from the list. This is also useful when creating VLOOKUP or HLOOKUP formulas (examples to be provided later).

Pivot Tables

A friend of mine has asked me to post on Pivot Tables as they just recently learned about them, and I thought it was a great idea. So you might be asking, "What are Pivot Tables?" Essentially, pivot tables allow you to quickly summarize large data sets (well, semi-large as Excel only allows you to a little over 65,000 records) in 1 or 2 or ... etc. dimensional ways. For example, if you have a set of detailed sales records with dates in one column, sales region in another column, and sales dollars in a third column you could create a report to summarize sales by Quarter by Sales Region to see how your sales staff is doing. You might have months down the left hand side and Sales Regions across the top, with the intersection of these two items representing the sales for that month, that region. To help enhance this post, I've posted a sample file that you can download here that contains daily stock prices for 3 stocks (Yahoo, Comcast and Google) for all of 2004. You'll have to download the file as a comma-separated file (csv) and then open it in Excel. Or, you could just copy and paste into Excel and then do a "Data | Text to Columns..." and use a comma as the delimiter.

Now that we have a baseline to work with, we can start to think about analysis. For example, what if we were analyzing the above three companies (I'm not sure why we would compare THESE 3, but you can use your imagination), and we would like a monthly analysis on the high price for the month, low price for the month, and average price for the month for all 3 stocks. We don't need each separate analysis at once, but we want it to be very simple to see whichever one we want at a moments notice. Pivot tables make this very easy.

To begin, we need to know what month the prices fall in. It just so happens that I recently wrote an article on extracting information from dates, so we know this will be easy. First, note that the stock prices each have a date in column 2. To figure out what month it's in programatically, we'll use Excel's 'MONTH' formula. Begin by typing in cell D2 the formula =MONTH(B2), and then copy this down to cell D599 (w/ quick keys: Ctrl-C cell D2, Ctrl-G D599 , Ctrl-Shift-Up, ). As one last step, let's give the month field a header in cell D1 (we'll call it "Month"). This is necessary for the pivot table. Now that we have whatever month the stock price is in, we'll move on to the real work: the pivot table.

To create a pivot table, simply highlight the data you would like to include in the new table, and then go to Data | Pivot Table. You will be asked where to select the data from, so we obviously want to select it from an Excel list. You can leave the default on for what kind of report to create (a Pivot table, NOT a pivot chart). Hit next, and on the next screen make sure that the data highlighted is the entire list of data that we created (should be from cell A1:D599). After you ensure that the entire list is selected, hit next and then hit finish. By default, a new worksheet should have been created with a placeholder for data items. This is where we define what kind of analysis we would like to perform.

For this example, I would like to see each company's stock by month. The easiest way to do this is to have the companies across the top, and the months down the left hand side. We'll start with the companies: drag the "Company" field from the "Pivot Table Field List" (see picture below).



Now that you have the companies across the top, we'll drag the "Month" field to the left where it says "Drop Row Fields Here". We can now add the numbers by dragging the field "Adj. Close*" to the "Drop Data Items here" section. By default, the data will represent the totals for the cross sections - but we want to see the average or the high price, etc. so we have to change this ... the sum of prices for the month of May doesn't really make sense. To do this, double click on the "button" (for lack of a better word - it looks like a button) in the upper left corner of the pivot table called "Sum of Adj. Close*". This brings up a box where you can change the name, but more importantly the function that is performed on the data. For now, we'll choose "Average" but feel free to experiment with "Max", "Min" or anything else you're interested in exploring.

That's it! If you've gotten this far, you should have a working pivot table as well as a pretty good understanding of how they work. If you wanted to get a little fancy, click on the "Format Report" button on the Pivot Table toolbar and choose a formatting option so it looks a little nicer. I also eliminated the "Grand Totals" for the columns and rows (go to "Pivot Table | Table Options" on the Pivot Table toolbar and uncheck the first two boxes) as totals don't make sense in this instance.

One last thought: click on the "down arrows" in the table and check and uncheck items at your whim. It will eliminate (or add) items from (to) the pivot table like a filter might.

As you can see, pivot tables can be a very powerful tool once you master the basics. Read the help menu or search Google if you want to learn more. Hope you liked the article!

Excel Shortcut Keys

Well, I've tried several times to get a "table-like" structure for the shortcut keys I promised, but can't seem to get it to look right. So, unfortunately, I have to provide it in a list instead:

• Ctrl-F3: Define Names
• Ctrl-X, Ctrl-C, Ctrl-V: Cut, Copy, Paste (respectively)
• Ctrl-R: Copy contents to the RIGHT
• Ctrl-D: Copy contents DOWN
• Ctrl-P: Print
• Ctrl-F, Ctrl-H: Find / Find-Replace (respectively)
• Alt-': Select style (e.g., number styles, border styles, etc.)
• Ctrl-G: GoTo
• F2: Edit the current cell
• Alt-F11: Bring up the Visual Basic Editor
• Alt-F8: Bring up available macros
• Ctrl-N: New workbook
• Shift-F11: Insert a new worksheet
• Ctrl-W: Close the current workbook
• Ctrl-Z, Ctrl-Y: Undo, Redo (respectively)
• Ctrl-1: Format cells
• F3: Enter named range into a formula
• Alt-Tab: Switch between programs / windows
• Ctrl-Tab: Switch between workbooks
• Ctrl-S: Save the current workbook
• Ctrl-PageUp, Ctrl-PageDown: Go one worksheet UP or one worksheet DOWN (*)
• Ctrl-Left, Ctrl-Right, Ctrl-Up, Ctrl-Down: Move to the left, right, top-most, or bottom-most cell in a range
• Ctrl-Shift-Left, Ctrl-Shift-Right, Ctrl-Shift-Up, Ctrl-Shift-Down: SELECT from the current cell to the left-most, right-most, top-most, or bottom-most of a continuous range.
• Ctrl-A: Select all cells in the current sheet
• Ctrl-Home: Move to cell A1 in the current sheet
• Shift-Spacebar: Select Row
• Ctrl-Spacebar: Select Column
• Ctrl-(, Ctrl-Shift-(: Hide, Unhide Row (respectively)
• Ctrl-), Ctrl-Shift-): Hide, Unhide Column (respectively)
• Ctrl--, Ctrl-+: Delete or add cells / rows / columns

I hope you find the above shortcuts useful. You can use them to become VERY proficient in Excel by combining commands. For example, to delete a row you could combine keys above as follows: select the row by using Shift-Spacebar and then delete the selection by using Ctrl-- ... that's a minus sign.

You can also learn to quickly access other commands that don't have shortcuts by looking for underlines in the menu bar. For example, I ALWAYS use the "Paste-Special" command, but it has no short-cut key (at least, none that I'm aware of). By looking in the menu bar, you'll see that the E in Edit is underlined. So you can quickly access that with Alt-E. Within the Edit menu, you'll see that the s in Paste Special... is underlined, so you can access that (after hitting Alt-E) by hitting the S. Once the dialog shows up, you'll notice there that many items are underlined; for example, the t in Formats. So to copy a cell and then paste only the formats, the key combinations would be Ctrl-C (to copy), move to the new cell, and then Alt-E-S-T-Enter. That's it. I also found one other site w/ some useful info: ASAP-Utilities. I've used the add in utility before (when I was at Deloitte), and found it useful but I don't use now.

Anyway, good luck!

Managing Dates

Simply put, dates are an everyday part of our life (pun intended). Even so, I've come across examples where more trouble was used than was necessary to extract or manipulate dates. In this post, I'd like to give an example on how to effectively use dates to create headings (possibly for memos, report headings, etc.).

To begin, we need a date to work with. We'll start by simply typing in a date (such as "06/11/2004" or "June 11, 2004"); but since this post is about using dates dynamically, we'll get a little more creative as well. Instead, let's say that we want to create a spreadsheet of dates where we start with a particular date (e.g., 12/31/2004) and then we want each subsequent cell to be the end of the following month (following our example, 1/31/2005).

You could manually do this or get creative with concatenation (which we'll do later in this article), but the simplest way to do this is with Excel's EOMONTH() function. This function might not be available to you right away; if you can't find the function or it gives you an error, you'll have to go to Tools | Add-Ins... and then click on the Analysis ToolPak add in to make it available. Now, we can actually write the formula.

1. Start by typing "12/31/2004" (or one of it's many variants) into cell A1.
2. Now, in cell A2, type =EOMONTH(A1, 1). This tells Excel that we want the end of the month following the value found in cell A1 (1/31/2004). If you wanted quarters (instead of months), you would simply change the 1 to a 3 (i.e., =EOMONTH(A1, 3)).
   
3. Copy the formula from A2 down through A10.

Note how each date is appropriately 1 month after the previous, and the days are the end of such months. If you copied this down through cell A39, you would even notice that this works for Leap Years as well!

Now that we have that squared away, we should move on to create headers from all our dates. For example, let's say that we wanted to create text of the form "For the 3 months ended March, 2005", but change the "3", and "March, 2005" to whatever date we happen to be on. This is fairly straight forward once you understand concatenation (i.e., connecting various text items) and the TEXT() function. Concatenation can be accomplished with the "CONCATENATE" function, or by simply using an ampersand ("&"). We'll use the ampersand since it's faster to type.

1. To begin, go into cell B1 and begin by typing =TEXT(A1, "M"). This will give you the month extracted from the date (e.g., 12/31/2004 would give you 12) in a number format.
2. Move into cell C1 and type =TEXT(A1, "MMMM, YYYY"). This is similar to the last example, but gives you the "long" version of the month (e.g., December) followed by a comma and finally ended by the "long" form of the year (2004).
3. Now, move into cell D1 and type =IF(MONTH(A1)=1,"month", "months"). This allows us to appropriately use "month" instead of "months" for January. It works by testing whether or not the month in cell A1 equals 1. If it does, it uses "month"; otherwise, it uses "months".
4. Finally, move into cell E1 and type ="For the " & B1 &amp;amp; " " & D1 & " ended " & C1. This one is a little more convoluted, but not bad. It simply concatenates the words "For the " to the value found in cell B1 followed by concatenating a space (" "), and so on to the end.

That's it! Copy the formulas in cells B1:E1 down as far as we have dates and each title will be appropriately updated. Experimenting with the "TEXT" function as well as with the "MONTH, YEAR, DAY" functions will help you develop more skills to appropriately extract the information you need. You no longer have to manually update ANY heading that can be extracted from dates already present in your spreadsheets!

Excel Macros

Well, I've finally gotten into the swing of things at work ... it seems like it's been a long road getting here! I've somewhat forgotten how manual things can be when there's no automation. For example, entering financial information into a workpaper or a model by hand after you've printed out an SEC document. Presumably, it should be straight forward enough where the worker-bee would not have to put too much effort into data entry, but moreso on the analysis of such data. Unfortunately, my thought is that this is the exception rather than the norm (later we'll discuss XBRL which hopes to change this ... so do I!). This is where VBA and macros can help (amongst other tools that I will get into in later posts). In this article, I would like to give a basic example of how to use a macro to your benefit (nothing fancy).

The thought of this came to mind when I was reading the JoA from this past month. A subscriber had sent in a question with respect to how could he easily put the file name and file path on most of his worksheets. The author astutely noted that since the person wanted this custom footer on most of his worksheets, he should modify Excel's default template (i.e., the empty window that is automatically openned when you hit "File | New" or hit "Ctrl-N"). This was a great answer for someone who wants EVERY new workbook that they create to have this footer - but what if you only wanted it on (for example) half of your worksheets? Then this answer might actually cause more trouble than it's worth; hence, a macro would be much more appropriate.

To create a new macro, you simply go to "Tools | Macro | Record New Macro...". You will be prompted for a name for the macro, a place to store it, an optional shortcut key, and finally a brief description (if you so choose). Typically, you're going to want to store new macros in a "Personal Macro Workbook", which creates a new file that is available whenever Excel is open. You would choose "This Workbook" if you only needed the macro for that particular file (no other files).

So, now that we know how to create macros, what exactly are they? To put it simply, they record keystrokes that you type after you enter all the above information. So, just so that we can finally get to the end of this article and actually learn something useful, let's create our "add_footer" macro (notice there are various naming rules that you MUST follow or Excel will get very angry). The following are the steps you'll need to follow:

1. Tools | Macro | Record New Macro...
2. Name it "add_footer" (without the quotes) and store it in your "Personal Macro Workbook". We won't add a shortcut key yet (and you'll find out why later), but you can add a simple description if you want.
• NOTE: remember that everything you type after this point will be recorded in the macro!
3. Now, to add the footer: simply proceed as normal (using ONLY the keys you would to create the header). That is, go to "File | Page Setup" and then go to the "Header/Footer" tab and add your custom footer. You can add "&[Path]&[File]" to get the pathname and file in your current sheet.
4. Finally, you want to hit the little box in the macro toolbar that came up to "Stop" recording the macro. (Alternatively, you could stop it by going to "Tools | Macro | Stop Recording").

Now that we've created our macro, we want to test it out. Open up a new workbook and then go to "Tools | Macro | Macros..." (or just hit Alt-F8 for faster access) and pick your macro from the list. Then hit the run button, and voila, the footer is there! Note that I didn't add a shortcut key (although feel free to do so if you choose). The reason for this is simple: I use virtually every shortcut key that Excel offers, and I rarely find a macro that is worth overwriting. For example, you might choose Ctrl-Y as a shortcut key - but that is for the "Redo" command (after you've "undone" something). If you don't use shortcut keys provided by Excel, you should definitely start; that alone might double you're productivity (complete guess, but might very well be accurate over the long term). Anyway, that's why I usually don't assign shortcut keys to macros.

If you want to add it to multiple sheets at once, simply click on the starting sheet and then hit the Ctrl key before selecting any additional sheets you want to add. It's that easy!

Macros can be used for a variety of things, and this is only a VERY simple example. Anything that you do on a repeated basis (e.g., change numbers to Currency with the $ symbol and 3 decimals and then change the font to blue 8pts) can be automated with macros. Once you get used to using macros, you can then move on to more advanced usage via VBA (see the "Visual Basic Editor" under "Tools | Macro"). We will get to such examples later on ... good luck for now!

Just Starting

Amazing - I've read others blogs and thought about creating one myself, but never took the time to move forward. If I had known that it would be this easy, I would have started a long time ago!

Anyway, I'll tell you what I intend to blog about - technology. Primarily related to the accounting and/or finance world. I'm a CPA (hence the title), and just finished reading that CPA's have not moved forward with blogging ... so I thought I would help change that.

A brief bio - my name is Kevin T. Ryan and I'm 26 years old. I just finished my first year at Carnegie Mellon University's MBA program - what a year! (More on that later). I worked for Deloitte & Touche for 4 years before coming here - 3 years in audit, 1 year in "Dispute Litigation" (fraud investigations and such). During my brief life, I've taught myself several computer languages (about 7 or so), mostly for fun some for work. I have 2 dogs (Jackson and Hunter) and am getting married August 13th of this year!

I expect that much of my time on this blog will be devoted to tricks that I use either in school (this upcoming fall) or at work. I'll be interning at PNC this summer, so we'll see how often "technology issues" come up that I feel are worthy to post. Hopefully I'll also post whenever I hear something interesting to talk about.

Anyway, sorry for such a long post - look forward to meeting you!