Download a zipped workbook with Excel 2007 AutoFilter sample data.
1. Set up the Excel TableIn Excel 2007, and later versions, you should create an Excel Table from your data, and use its built in filtering features. If you are using an Excel Table, you can skip to the next section, Filter the Database.
If your data is not in an Excel Table, follow these steps to prepare your list for an Excel 2007 AutoFilter.
- Add headings: In the row directly above the data, enter a heading for each column. In the example shown here, the data starts in row 2 of the worksheet, and the headings are in row 1.
- No blank rows or columns: You can leave blank cells in a row or column but make sure that there are no completely blank rows or columns within the database.
- Separate: The ideal setup is to store your database on a worksheet where there is no other data. If that's not possible, keep the database separated from other data on the worksheet, with at least one blank row at the end of the database, and a blank column at the right. If the database doesn't start in cell A1, ensure that there is a blank row above, and a blank column to the left.
Wednesday, March 23, 2011 | 2 Comments
With new Excel 2007 and 2010, Microsoft has put many functionality in conditional formatting which allow you instance format and conditional rules. You just simply use the pre-ruled set and choose the existing format to make your data look professional.
There are 5 pre-ruled already set for you to choose:
- Highlight Cells Rules: which allow you to choose whether when to highlight the cell. E.g you choose to highlight the cell when the data is smaller than 100.
- Top/Bottom Rules: this rule is used to identify the top/buttom values with the format specified.
- Data Bars: is used to show the bar within the data you format.
- Color Scales: is used to show different color highlighted for different scales of data.
- Icon Sets: is used to put the different icon for different data set.
1. Highlight Cell Rules:
For those rules under this rules work the same. When you click any rules (let's say Greater Than) then you input the value that you want to put the conditional format and choose the format your want on the right drop box. Then you cells will be formatted accordingly.
- Greater Than/Less Than: to use when you want to highlight the cell when the value is greater/less than the specified value
- Between: when the value is somewhat between the specified values.
- Equal to: when the value is exactly the same as the value you specified.
- Text that Contains: used to format the text.
- Date Occurring: used to format the date
- Duplicate Values: used to highlight the duplicate data.
2. Top/Bottom Rule
For top/bottom rule, it is used to highlight or format the cell with the top/bottom values such as:
- Top 10 items: it is not necessary to 10, you can specify how many top value to be formatted.
- Top 10%..: you can specify how many percentage on the top value to be formatted
- Bottom 10 items and button 10% is similar to the above options
- Above Average/Below Average: it will be formatted if the value is above/Below the average of the total value.
3. Data Bar
With data bar, you can put the bar chart in your cell directly. Just select the whole data, then select the format from data bars as shown in the picture, then you will have the embed data automatically.
4. Icon sets:
The icon set allow you to place the icon embeded in the cells directly according to the data you input. It works very perfect to what you need.
Friday, March 18, 2011 | 0 Comments
Using conditional formatting, you can quickly identify differences between two lists using an expression in the form:
=COUNTIF(otherlist,firstcellincurrentlist) = 0
This technique is simpler if you work with ranges. For purposes of this example, the list in column A is named ListOne and the list in column B is ListTwo.
Now, let’s identify the differences between the two lists, starting with the list in column A:
- Select ListOne (A1:A10).
- Click the Home tab, click Conditional Formatting in the Styles group, and choose New Rule from the dropdown list. In Excel 2003, Conditional Formatting is on the Format menu.
- From the Select A Rule Type list, choose the Use A Formula to Determine Which Cells To Format option. (This step isn’t necessary in Word 2003.)
- Enter =COUNTIF(ListTwo,A1)=0 in the Formula field.
- Click Format, click the Fill tab, choose a color, and click OK.
- Click OK. Any cell in ListOne that contains a value that is not in ListTwo (column B) is quickly identified by its contrasting fill color.
Now, let’s use the same technique to format ListTwo. First, select ListTwo (B1:B10). Repeats steps 2 through 6. In step 4, enter the expression =COUNTIF(ListOne,B1)=0. In step 5, you can choose the same color or a different color. This time, the conditional format identifies entries in ListTwo that aren’t in ListOne.
This technique works with values as well as text entries. The list don’t have to be the same number of entries either. In this example they do, but the technique will work with different-sized lists.
Tuesday, March 08, 2011 | 0 Comments
To find duplicate records, use Excel’s easy-to-use Filter feature as follows:
- Select any cell inside the recordset.
- From the Data menu, choose Filter and then select Advanced Filter to open the Advanced Filter dialog box.
- Select Copy To Another Location in the Action section.
- Enter a copy range in the Copy To control.
- Check Unique Records Only and click OK.
Excel will copy a filtered list of unique records to the range you specified in Copy To. At this point, you can replace the original recordset with the filtered list (the copied list) if you want to delete the duplicates.
Finding duplicates in a single column or across multiple columns is a bit more difficult. Use conditional formatting to highlight duplicates in a single column as follows:
- Using the example worksheet, select cell A2. When applying this to your own worksheet, select the first data cell in the list (column).
- Choose Conditional Formatting from the Format menu.
- Choose Formula Is from the first control’s drop-down list.
- In the formula control, enter =COUNTIF(A:A,A2)>1.
- Click the Format button and specify the appropriate format. For instance, click the Font tab and choose Red from the Color control and click OK. At this point, the Conditional Formatting dialog box should resemble the following figure:
- Click OK to return to the worksheet.
- With cell A2 still selected, click Format Painter.
- Select the remaining cells in the list (cells A3:A5 in the example worksheet).
The conditional format will highlight any value in column A that’s repeated. If you want Excel to highlight only the copies, leaving the first occurrence of the value unaltered, enter the formula =COUNTIF($A$2:$A2, A2)>1 in step 4.
The conditional format works great for a single column. To find duplicates across multiple columns, use two expressions: One to concatenate the columns you’re comparing; a second to count the duplicates. For example, if you wanted to find duplicates of both first and last names in the example worksheet, you’d enter the following formula in cell D2 to concatenate the first and last name values:
You could insert a space character between the two names if you liked, but it isn’t necessary. Copy the formula to accommodate the remaining list items.
Next, in cell E2 enter the following formula and copy it to accommodate the remaining list:
Notice that the worksheet has a new record (row 6). This record duplicates the first name, Susan, but not the last name. The conditional format highlights the first name because it’s a duplicate in column A. However, the formula in column E doesn’t identify the combined values across columns A and B as a duplicate because the first and last names together aren’t duplicated.
Saturday, March 05, 2011 | 1 Comments
Source – Google Maps In Excel
Here’s my first attempt at using Google maps in excel (download below). Currently I can input an address and have a Google map of the address displayed in Excel, with most of the cool google functionality.
So how does it work?
1. The address is sent to Geocoder.us to be converted to Latitude and Longitude (required to map a location on google maps), and the result is returned to the spreadsheet.
2. Excel sends this geocoded information to the automateexcel.com server, where I have a google map that receives latitude and longitude as variables and displays the respective map via the Google Map API.
Read more at its sources >>
Wednesday, March 02, 2011 | 0 Comments