Excel Tips

March 21st, 2011 - by Angela

excel-tipsFrom time to time, I like to check out the web for new tips and tricks pages or blogs.  I am always on the lookout to find a new feature or to refresh myself on an old Excel tip that I had forgotten about.  Since I use Excel most every day, I get used to doing things a certain way and love when I remember another way to complete the same task, sometimes faster than the way I have been using.  After going through some of the Excel tips recently, I thought I would share a few that you might be interested in.

UNIQUE RECORDS

Sometimes, I have a list of items in Excel that I would like to filter through the data to get rid of the duplicates.  In many of my training classes, I have answered this question numerous times, too.  So, I know that many other people have this same task.  One way to find only unique records is to use the “Remove Duplicates” feature.  If you are in Excel 2007, you can do this by clicking on the Data tab and then choose the “Remove Duplicates” button.  You will be given a list of the columns in your Excel sheet.  Select the columns you would like to search in to find the unique records. Then click OK.  Make sure that you click the “My Data Has Headers” checkbox if your have column headings, so that they do not get filtered, too.

DATA VALIDATION

In some cases, I am creating spreadsheets where others will be entering data.  In these sheets, I want to make the data entry as automated and error proof as possible.  One of the ways I do this is to set up data validation on appropriate cells/columns.  For example, instead of having someone type in the department where someone works, I create a list for of all of the departments so that I can make sure that they have the correct, updated department names. 

To do this, select the cells that you would like to add the data validation.  Next, click on the Data tab and then the “Data Validation” button.  A dialog box will open.  On the Settings tab, under Allow, choose List.  Then, under Source, either select the cells with the department names or type them in yourself (with commas separating each department name).  If you would like to add an input message or error alert message, there are separate tabs in this same dialog box.  After you click OK, you will have an in-cell drop down list available.

SUMIF FORMULA

I am a big fan of formulas.  I happen to like math, but anyone can work with formulas, so do not be shy with them!  I really believe that the SUMIF formula is very versatile and can provide solution for many business needs.  Let’s say that you have a couple of columns.  You would like to look up items in Column A based on a certain criteria.  When the item in Column A matches that criteria, then you want Excel to SUM up the number in Column B. 

For example, you have a list of people in different office locations in column A.  In column B, you have their sales for the month.  You can have Excel add up the sales amounts for a certain location, like “New York” office location.  The formula is =SUMIF(A:A,”=New York”,B:B)  The first part “A:A” tells Excel to evaluate the entire column A.  The next part “=New York” lets Excel know to look for items in column A that match the text string “New York”.  The third part “B:B” says that Excel should sum up the New York numbers from Column B.  Try it out yourself.  Type in cities in column A and numbers in column B.  Then, use the formula to see how it works!  Whichever items in column A are New York, then its corresponding number in column B should be added.

Enjoy these tips!  Add some more of your own in the comments below if you’d like!  I can include them for a future post!

Tags: ,

2 Responses to “Excel Tips”

  1. Angela says:

    Hi Teresa,

    The SUMIF formula cannot be used on 3D ranges like the SUM function can. What you will need to do is place the SUMIF on each of the worksheets, like in cell A1. Then use a formula like =SUM(M1:S1!A1) to pull the information from the SUMIF formulas on each page.

    Hope this helps!

    Angela

  2. Teresa says:

    Can you use this formula to pull information off multiple sheets? I can not seem to get the formula to work doing this.

    =sumif(’M1:S1!L2′”=SHONEY’S”,’M1:S1!L3′)

    Sheetse M1:S1 represent daily time sheets, L2 is the drop down list for products run and L3 is the employees time in that product.

    Can you tell me what I am doing wrong?

Leave a Reply