Best Microsoft Excel Bloggers

Friday, September 17, 2010

Pin Your Spreadsheet

GET PINNED!!!!

I actually got this tip idea from Francis Hays, "The Excel Addict" but I really liked it so I thought I would share it.  I learned how to pin my files awhile ago and of course promptly forgot it since I did not use it. (Let that be a lesson to you.)

Anyway, I have a couple of spreadsheets that I open almost on a daily basis so usually I can just go to File>Open or look in Recent Documents and easily find them. Sometimes, though I have opened a lot of other files that day and of course, then I have to go search for these files- which can be a pain.
What you can do is PIN the file and although it will move down the most recently used list, it will not disappear until you unpin it.

  • Click on the Quick Office Button.

  • Click on the gray pin (located to the right of the file name)


  • The pin will turn green to indicate that the file is 'pinned' to the list.
    Click on it again and it will turn back to gray and be unpinned. 
    How easy is that? Now that I have been reminded of this trick, I plan to use it.
    Have a great weekend.

    Monday, September 13, 2010

    Subtotal Example with Filtering

    Using the Subtotal Function
    I wanted to spend a minute and show a subtotal example because of some emails I received. A lot of people seem to be only familiar with the Subtotal command on the Data tab or Data menu that automatically creates the subtotal for you . Obviously that is the fastest way but typically it requires sorting your data and sometimes you don't want to do that.

    In those cases, you would want to use the SUBTOTAL function .
    =SUBTOTAL(function_num, ref1, ref2, ...).

    The beauty of the Subtotal feature is that it does not have to be at the bottom of the data- it can be anywhere on the sheet. In addition, you do not have to manipulate your data.
    Below is a simple example, where I filtered sales by State and I wanted only the total for Massachusetts' sales. When I use =SUM(C6:C9) and select the filtered range, it added up all the sales of all states which was 1800.
    When I used the Subtotal function on the filtered data =SUBTOTAL(109,c6:69), Excel returnd the total sales of the filtered data which was 900.
    Enjoy the week. Fall may actually be here - crisp weather and the leaves are just starting to turn.

    Tuesday, September 7, 2010

    Excel Arrays - Audit Plan Uses

    I hope that everyone had a great Labor Day.
    We went up to Chicago and had a great time at the Field Museum and shopping as well as eating at some great places. The weather was perfect.
    I've been busy redesigning and reformatting CPASelfstudy.com to make it easier to navigate so I have not put a lot of time into this blog lately however that should change shortly.

    I did want to mention that the Missouri CPA Society published our article on Incorporating Excel Arrays into Your Audit Plan as that may be of interest to you. They have an online version of  The Asset that can now be accessed  -  http://theasset.dirxion.com/WebProject.asp?BookCode=jul10flx&from=2

    Monday, August 23, 2010

    Spreadsheet Design 101

    So many people, ignore spreadsheet design but it is important if you are building a complex worksheet or one that you will be referrring back to over time.

    Spreadsheet Design


    Two things make a good Excel spreadsheet. Good data is obviously one of these things but the other that is often overlooked is good design. Good design helps to ensure a reduction in errors which is obviously key.

    A nicely formatted spreadsheet is nice and clearly it is easier to understand; however, a nicely formatted spreadsheet and well designed spreadsheet are not synonymous. A well designed spreadsheet is concerned with the organization of data and assumptions rather than appearance although obviously appearance is a factor – it is just not the primary factor.

    Good design helps to ensure:

    • Navigability
    • Ease of use
    • Reduction of repetitive input which helps to cut down on errors
    • Locate errors more easily

     Too often people dive into creating a spreadsheet without a thought as to the end product. How many of you have had to cut and paste or insert rows and columns for data that needed to be moved later or to make room for data that had not considered when you started? I would be surprised if everyone reading this failed to raise their hand!

    Before touching that keyboard consider the following:

    • Who will be using the spreadsheet?
    • What is the purpose of the spreadsheet?
    • What are the required inputs?
    • What mathematical expressions are you thinking about using?
    • What are the desired outputs?
    • How are you going to test the model?
    It is highly recommended that after considering the questions above, you sketch out mentally or on paper how the spreadsheet should be laid out. You should have a minimum of 3 separate modules or areas and usually you will have at least 4.  These days, rather than modules, think of sheets within the Excel file.

    • Input Area All the user inputs and defined variables (constants) Including assumptions and documentation
    • Work Area Calculations The raw data and formulas to process the inputs – nitty gritty stuff
    • Output Summarization and display of data
    • Reports Charts and tables -(Presentation)

      
    Clearly you could have a number of modules in the work area depending upon the complexity of the problem you are examining. You should plan on a number of modules in your work area if you anticipate a complex spreadsheet.

     Design Points


    As you begin to design a spreadsheet you need to consider the following design points


    Documentation

    Yes- that old thing. Even if you hate to document what is going on with the spreadsheet, at least put the date, your name and the title of the worksheet or project. No one ever thinks they have the time to sit down and document but at least if you set a standard design with an assumptions section you will have a starting point later.


    For everyone who says they have no time to document consider how much time you have spent staring a spreadsheet that you created 7 or 8 months before wondering what the heck that number is or what the purpose of a cell’s formula is. Admit it – we all have. I have one individual who admits every time budgets roll around he cringes because he knows that he will have to spend time figuring out what he did the previous year because he didn’t bother to document it in the worksheet and he can never find those little scraps of paper with the notes he made at the time.


    Natural Flow

    Ensure there is a natural flow of information down each worksheet instead of having to jump forwards, backwards and sidewards.


    Headings

    Excel in many cases assumes that there is a heading above the raw data. It also makes things much easier to read. Bolding the headings increases readability too as opposed to italicizing which is often difficult to read.


      
    Blank Rows and Columns

      
    • Blank rows and columns seem to be a favorite among accountants for a cleaner look; however, be aware that a lot of Excels built-in features assume a blank row or column means no more data. In other words do not break up data that you may need to sort or filter with blank rows.

    •  For design purposes some people recommend leaving at least 4 blank rows above your headings and data as these can be used for viewing Totals instead of having to scroll down the bottom of a large spreadsheet. Also blank rows at the top of a spreadsheet are useful for criteria for database functions and the advanced filter feature.

      
    Absolute Cell References

    • Use absolute cell references wherever possible. It is much easier to change a single cell and have the impact ripple through the spreadsheet than to have to find the cell, change it and then copy it across or down and then view the impact. I think that you can also see how changing just one number is more efficient and would help to reduce potential errors.

    •   If you do use absolute cell references set them up in a separate section so that they are easier to locate.

    Cell References

    • Always use cell references instead of typing in numbers. It increases readability and helps reduce errors and repetitive data entry.

    •  It is also helpful to have input cells in the same column – reduces errors in referencing them in formulas.

    Sorting

    If possible, sort your data as many Excel functions such as Vlookup assume that your data is sorted.

    Dates

    Ozgrid.com recommends using real dates for headings and to format them appropriately. By this I mean if you want the names of the months as headings type them in as 1/1/2001, 1/2/2001, 1/3/2001 etc then format them as "mmmm". This is a very simple procedure that is all too often overlooked by many. If you have real dates as headings life will be much easier further down the road if you need to use them in formulas. Iff you don't need them -nothing is lost.


    Use Single Cells

    Keep formulas simple.
    Don't put in one cell what could go in more than one cell. Say you have the names of 100 people to put into your spreadsheet; don’t put their full name in one cell. Instead, put the First name in one cell and their surname in the next cell to the right. If you need to place them into one single cell at a later stage, this can be done very easily by concatenating them; however, the same cannot always be said for the reverse.

    Break Model Down

    If the spreadsheet is complex, large or has a lot of linked cells break it down into manageable components. Errors increase exponentially as the complexity of the model increases. The frequency of use also impacts the potential error rate.

    Test your spreadsheet

    Test your model before you begin using it! 

    • Test the formulas 
    •   Use error checking formulas in adjacent cells – IF functions can be helpful
    •  Test range names if you have used them
    •  Test formulas and ensure they were copied correctly
    •  Check the effects of rounding and number formatting
    •  If using unusual functions check to make sure they were correctly applied.
    • Test the charts and reports
    • Compare results of data to previous versions or prior periods

     And test it again, after you make changes to it. Don't use the same spreadsheet year after year without testing it periodically after your have made revisions.
    This last is an important point, that many people forget about - to their regret!








    Wednesday, August 18, 2010

    Subtotal - 9 versus 109

     =SUBTOTAL(9,range) versus =SUBOTAL(109,range)

    I have been busy revamping CPASelfstudy.com's site design and working on some Excel Ebooks and unfortunately my blogging has fallen by the way side - temporarily.
    I do plan to start writing again once or twice a week starting next week.

    My husband has been showing me all the different things he has been doing with the subtotal function and filtering so that is probably what I will talk about next week. (And yes, we do need to get a life!)

    One thing that puzzled me was that he was using a function =SUBTOTAL(109,range) instead of  the function I was familiar with which is =SUBTOTAL(9,range) to sum and subtotal.

    Sorry- my original post on the differences between =SUBOTOTAL(9,range0) and SUBTOTAL(109,range()) was incorrect.

    Thanks Jan for the correction :
    SUBTOTAL(9,...) totals everything except cells with subtotals and filtered cells.

    SUBTOTAL(109,...) totals everything except cells with subtotals and filtered cells and hidden rows.

    Ms. Excel- Resident Excel Geek