Excel tips! Two-minute tips from David Carter

Here are four quick Excel tips, each of which you can master in two minutes or less! These tips are courtesy of Excel guru David Carter and our sister site, Accountingweb.co.uk.

Excel 2min Tip #1: Sort data with the =RIGHT function: by David Carter

When Excel sorts a column of items, it looks at the initial character of each item, that is, the characters to the LEFT. But suppose you want to sort according to characters on the right?

For this you need to use the =RIGHT function.

Example problem:
You have a series of account codes, where the first five digits represent the nominal code, the last three digits the cost center.

They are in cells A1:A6 and in nominal code order like this (51000-52000):

51000-100
51000-200
51000-300
52000-100
52000-200
52000-300

You want to sort them into cost center code order like this (100, 200, 300):

51000-100
52000-100
51000-200
52000-200
51000-300
52000-300

Solution

  • Create a blank column to the right of column A

  • In B1 type =RIGHT(A1,3).

    This will return the value 100.

    Copy this formula down the rows.

    Now sort the data in both columns on column B (Data, Sort, Column B).

    Now you can delete Column B.

    Note: A more robust formula that would handle the case where the reference before the "-" was of variable length would be:

    =right(A1,len(A1)-find("_",A1))

    As a by-product, this formula would also handle the case where the reference after the "-" was also of variable length.

    Excel 2min Tip #2: Use & to combine two columns into one. By David Carter

    Use the ampersand (&) when you want to combine two fields into one.

    Example Problem

    You have a column of alphabetic account codes in fields A1:A6, like this:

    Income Audit
    Income Consultancy
    Income Other
    Premises
    Marketing
    Office Admin

    How do you get them to sort into this sequence automatically?

    Solution: Put a numeric prefix at the front.

    Type these numbers into cells B1:B6

    10
    11
    12
    13
    14
    15

    In cell C1, type the formula: =B1&”-“&A1

    This returns 10-Income Audit.

    Copy the formula down column C.

    Now change the formulas to values as follows:

    Highlight column C. Choose Copy. Then -Paste Special Values, OK (or Paste, Paste Values in Excel 2007).

    From now on, use the list of account codes in column C.

    Excel 2min Tip #3. How to print just a part of your worksheet By David Carter

    Sometimes you want to print just a part of your worksheet, not all of it.

    The slow way is to highlight the area you want to print, then use the Set Print Area option.

    The quick way is to highlight the area you want to print, then:

    File - Print – at bottom left, change from Active Sheet to Selection –- Print.

    Excel 2min Tip #4. Add up rows and columns quickly with Autosum

    Just finished putting together a budget and want to add up the rows and columns?

    Suppose your budget fills cells A1 to L10.

    Highlight cells A1 to M11 so that that there is a blank row underneath and a blank column to the right.

    Then click the Autosum icon once (big Sigma; or use keyboard shortcut Alt+=)

    Excel calculates the totals and puts them into the blank row and column.

    You may like these other stories...

    In the old days, we used to tape down receipts from our travels and submit them to accounts payable. But that was before remote employees who may live in a different city from the home office. And of course, there's all...
    In 2011, electrical services and technology provider Parsons Electric in Minneapolis, Minn., decided to take its accounting to the cloud. Monica Ross, the company's director of strategic projects, talked with AWEB about...
    Event Date: July 24, 2014, 2 pm ET In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the...

    Upcoming CPE Webinars

    Jul 16
    Hand off work to others with finesse and success. Kristen Rampe, CPA will share how to ensure delegated work is properly handled from start to finish in this content-rich one hour webinar.
    Jul 17
    This webcast will cover the preparation of the statement of cash flows and focus on accounting and disclosure policies for other important issues described below.
    Jul 23
    We can’t deny a great divide exists between the expectations and workplace needs of Baby Boomers and Millennials. To create thriving organizational performance, we need to shift the way in which we groom future leaders.
    Jul 24
    In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.