All Fields Required

8 Great Tools to Make You an Excel Expert

8 Great Tools to Make You an Excel Expert

Posted On
August 24
By
Joe Zawada
Excel is a powerful tool for collecting and manipulating data. In the accounting world, spreadsheets are an everyday tool used to analyze financial data, review exports of inventory, or search for trends of accounts payable invoices.

Whether you are a CPA, doing accounting for your business, managing your personal finances, or just keeping track of simple data, the more you know about using Excel, the easier your tasks will seem. Here are eight specific hacks users of Excel can master and use to their benefit.

1. Pull Data from Other Cells with Absolute and Relative References

One of the cornerstones of Excel is being able to reference and pull data from another cell. A simple reference is just pointing to the cell location. “=A3” is a cell reference: Whatever cell you put that in will pull the contents of cell A3.

For example, take a spreadsheet that multiplies the daily number of sales times a sales price in cell B1.

excel-hacks-1

Each part of the formula (=B4*B1) is called a relative reference. That means each part moves as you copy the formula. If you copy this formula down to the other dates, you see an error message and some unexpected results.

excel-hacks-2

This is because the formulas were relative reference. When you copy the formula down, it becomes =B5*B2 in the next cell. The first part does need to change to B5, since that is the current day sales, but you do not want the last part to change: It needs to refer to cell B1, the sales price.

This is when you use an absolute reference. To do that, put dollar signs in front of the “B” and the “1”. Now the original formula in cell C4 should be =B4*$B$1

(A quick way to do this is put your cursor between the “B” and the “1” and press F4.)

With the absolute reference in place, you can copy the formula down and get the result you need

excel-hacks-3

2. Combine Cells with an Ampersand (&)

Sometimes you will get spreadsheets that have cells with pieces of information in them, but you need them to be combined into a larger cell. For example, maybe you have a list of names, with a column for first name, another column for middle initial, and a third column for last name.

excel-hacks-4

But you may need them combined, so you can have the full name in one cell. This is easy with the “&” symbol. Make a formula showing each part you want in the new cell and put the “&” in between each. One thing to note is that when a spreadsheet combines the cells, if there are no spaces after each word, you need to add them to the formula. Use quotation marks with a space between them in your formula to add in those spaces: =A2&” “&B2&” “&C2

excel-hacks-5

It takes the first name, which is cell A2, then puts a space, then the middle initial from B2, then another space, and finally C2, the last name. The “&” sign just combines everything.

3. Highlight Cells with Conditional formatting

Excel allows you to make cells a certain color based on the contents of the cell. This can be extremely helpful when looking at a large spreadsheet: It helps you see these numbers faster than searching for them.

Take the example of a personal budget spreadsheet, where you have several household expenses with projected and actual cost.

excel-hacks-6

When looking at a budget, many times you want to see specific numbers, such as when your actual costs are too high or greater than projected. Conditional formatting lets you do that.

For example, if you would like to highlight all the items in the “Difference” column where actual costs exceed projected, highlight column E, and then go to: Home>Conditional Formatting>Highlight Cell Rules>Less Than.

excel-hacks-7

In this menu you can choose what number to highlight: you can put “0” so it will highlight all negative numbers. The drop down on the right lets you choose the color you want use to highlight the cells.

excel-hacks-8

Click “OK,” and you get a conditionally formatted budget.

excel-hacks-9

Now you can quickly see where your expenses are more than expected.

4. Use VLOOKUP Function to Find Specific Data

One of the most powerful features in Excel is the VLOOKUP function. This function looks in a large table of data for a value you supply. When it finds it, Excel scrolls to the right the number of columns you tell it to and then brings back that value.

excel-hacks-10

Revisiting the Budget example, there’s a row for Income. We want to enter each month’s income; however, income is in a separate table over to the right in the spreadsheet.

excel-hacks-11

In the main table, in cell B4, you want to pull in June’s income. Instead of manually looking in the income table for June and copying it over, you can use the VLOOKUP function to do it.

excel-hacks-12

Insert the VLOOKUP function into cell B4. Through the wizard you get these fields:

  • Lookup value – What you are looking up. In this case it is B3, which is “June.”
  • Table array – The table you are looking in (N:O). The leftmost column must contain the data you are looking up, which is “June.”
  • Col Index num – The number of the column you want to pull back. 1 would return column N. 2 returns column O.
  • Range Lookup – False means you want an exact match. True means it pulls the next closest if it doesn’t find a match

After all that, you get your result.

excel-hacks-13

The VLOOKUP pulled 4000 for the income table, which was June’s income. To learn more about VLOOKUP and see a full tutorial, click here.

5. Use Copy – Paste Special to Paste Values, not Formulas

Performing a Copy-Paste is one of the most basic Excel functions any user will do—it’s just moving a cell to another spot. When you copy and paste a formula, however, you move that formula over, along with any cell references. Copy-Paste Special allows you to copy the actual results, not the formulas. This can be necessary when emailing out spreadsheets or when the cell references other files that aren’t always available.

excel-hacks-14

In a budget spreadsheet, categorized by month, cell B4 refers to cell C5 in another worksheet called “Conditional Formatting.” This works fine until you need to send out this monthly sheet, as it would still have the reference to another worksheet. The other worksheet won’t be available to other viewers, so this formula wouldn’t be able to pull anything.

Get around this problem using Copy-Paste Special. Highlight all the cells that would be affected. In this example, it is all of column B. Right click, go to “Copy,” and then right click again to go to “Paste Special.”

excel-hacks-15

In the Paste Special pop-up window, choose Values so Excel will replace the formula with the actual values. Now the cell has the final result instead of the formula.
excel-hacks-16

6. Reduce Frustrating Errors with IFERROR Function

Error messages like #N/A, #REF, and #DIV/0! are all common and make spreadsheets ugly. They also keep you from doing future calculations on these cells. To get rid of errors, use IFERROR.

For example, a grocer has a spreadsheet with a formula to divide column B by column C.

excel-hacks-17

Some entries in column C are “0,” which is causing some #DIV/0! errors. Get rid of this by inserting the IFERROR function in front of your formula. So instead of =B4/C4, you would have:

=IFERROR(B4/C4,0)

This tells Excel to perform the first part, which is B4/C4. Then, if there are errors, it will display the second part of this function, which is “0.” You can make that part whatever you want.

Now, after inserting the IFERROR function, you get a value instead of an error message.

excel-hacks-18

7. Control Decimals with the FIXED Function

The FIXED function is used to limit the number of decimal places in a number. When you have a function that multiplies or divides two numbers, and you may get a result like 42.33333333. That doesn’t look good and may not work correctly in future calculations.

Use the FIXED function to limit this number to the exact number of decimals you need.

excel-hacks-19

Consider this example of someone’s payroll. If you look in columns F and G, their Taxes and Net Wages have 3 and 4 decimal places. They need to have 2 decimals so they can reflect a real dollar amount.

You could format them, but the real number would still have 3 or 4 decimals. To make them 2 decimals, use the fixed function in front of the formula in the cell. In cell F4, you would make the formula =FIXED(E4*D4,2)

This limits the result from the formula E4*D4 to 2 decimal places. Now, after adding that FIXED function to all of columns F and G, the resulting value is a true dollar amount.

excel-hacks-20

8. Count Values Using the COUNTIF Function

The COUNTIF Function will count how many items reach a certain criteria you set. This can be great when you need to keep track of certain results or scores. Take a spreadsheet counting bowling scores.

excel-hacks-21

In this example there are 7 weeks of bowling scores, with 3 games each day. The average score is 180. What if you needed to find out how many games went over 200?  Or how many games were under 150?

The COUNTIF Function will do that for you. Go to cell D13 and enter in this formula: =COUNTIF(B4:D10,”>200″)

What this formula does is looks in the range of B4 to D10, which are all the bowling scores, and then looks for the criteria of >200. It is going to count all the games that have a score greater than 200.

We can also do this for under 150 with =COUNTIF(B4:D10,”<150″)

excel-hacks-22

The COUNTIF function, along with the preceding seven Excel tips and formulas, will take any novice user into more advanced usage of Excel. Using them will make your spreadsheets more efficient, easier to use, and more user friendly.

For the accountants out there, these tips will save you time everyday when you apply them to your daily Excel spreadsheets. No longer will your projects look like an amateur effort. You can apply these techniques to your financial trending spreadsheets, balance sheet analysis, or any other accounting project. These will make your time in Excel more productive!

excel-hacks-embed




























8 comments

Leave a comment
  1. bfgfngfn | Oct 17, 2016
  2. ccsdv | Oct 17, 2016
  3. vfvdfbfb | Oct 17, 2016
  4. jack sparr | Oct 17, 2016
    Download Zapya for PC to share files and folders easily from PC to Mobile
  5. joeychen | Oct 09, 2016
    The Twitter shout-out was accompanied by YSL outlet uk a photo from the campaign that shows Gomez wearing a sea green and blue dress and imposing ankle boots while toting a Louis Vuitton bag. 23-year-old pop singer Selena Gomez continues to delve into the world of high fashion with a new campaign for Louis Vuitton. Over the past few days creative director Nicholas Ghesquiere has been posting a handful of photos from the campaign on his Instagram. One particularly striking photo shows Gomez posed in a furry hat that looks appropriate for a Moscow winter. The campaign, which was shot by Bruce Weber, also features Jean Campbell, Erika Linder, Natalie Westling, and Rianne Van Rompaey. This campaign is merely the latest development in Gomez's growing relationship with the French fashion house. In May she accompanied Ghesquière to the Met Ball while decked head-to-toe in Louis Vuitton. In addition to increasing her fashion profile, lately Gomez has been busy promoting her latest album "Revival." Whenever you visit a Gucci boutique, you can't possibly miss the Dionysus Bag. They're shining on replica handbags uk the spotlight. The Dionysus has been featured almost everywhere and included in every ad campaign. We know the bag is hot right now. For the new season, Gucci did something wonderful and different. They created a completely new Dionysus Tote Bag. This one has an extra pocket on the front and beautified with the signature snake clasp. The interior features three large compartments and one zipped pocket in the center. The bag opens and closes with magnetic fastening on the top. And the shoulder strap can be detached. The Gucci Dionysus Tote Bag sale gives you more room to store your essentials. Not only is it stylish, but this one is also perfect for work or the moments after work. Available in the colors and leathers you cant, measuring 10' x 14' x 4' inches (H x W x D). Michael Kors is born in New York and it shows in the way he designs his fashionable creations. He is known for his urban style in his clothing and accessories, such as Designer Handbags. The world of fashion became aware of him and his talent as a designer in 1981 during the launch of his women's clothing line in Manhattan's most popular shops, such as Bergdorf Goodman, Bloomingdales, and other shops. This first Michael Kors collection reflects a youthful design. Over time, Kors proved to the first shops that trusted him and his collection that he is out to succeed and conquer the fashion market all over the world. Now, any customer who would love to christian louboutin outlet have a Michael Kors item can buy them online.
  6. Anjali | Aug 30, 2016
  7. Anjali | Aug 30, 2016

    Great tools. 

    These tools will help me a lot to crack my excel examination. Great i found this blog before my exams. 

  8. test | Aug 22, 2016
    test

    Leave a comment