How to Select the Right Chart Type in Excel 2010

Chart Types in Excel 2010

When we display your data visually in Excel 2010, choosing the right type of chart is just as important as deciding to use a chart at all. Different charts display the data in very different ways. Using the best chart type and format will help you display your data visually in the most meaningful way.

In the Insert Chart dialog box provides access to all the available chart types and subtypes.

Following is a description of the major chart types available in Excel, with some simple guidelines on when to use each type chart.

• Column charts: A column chart, unlike a bar chart to which it is often compared, emphasizes variation over a period of time. In a column chart, categories appear horizontally and values appear vertically, whereas in a bar chart, categories appear vertically. Variations include the cylinder, cone, and pyramid chart subtypes.
• Line charts: A line chart shows the relationship of the changes in the data over a period of time. Although similar to an area chart, which shows the relative importance of values, the line chart emphasizes trends rather than the amount of change.
• Pie charts: Pie charts contain just one chart data series. A pie chart shows the relationship of the parts to the whole. To emphasize the importance of one slice of the pie, choose one of the exploded 2-D or 3-D pie charts.
• Bar charts: A bar chart (horizontal bars) emphasizes the comparison between items at a fixed period of time. This chart type also includes cylinder, cone, and pyramid subtypes.
• Area charts: An area chart shows the relative importance of values over time. An area chart is similar to a line chart, but because the area between lines is filled in, the area chart emphasizes the magnitude of values more than the line chart does.
• XY (Scatter) charts: Scatter charts are useful for showing a correlation among the data points that may not be easy to see from data alone. An XY (Scatter) chart uses numeric values along both axes instead of values along the vertical axis and categories along the horizontal axis.
• Other Charts: All the other types of charts are lumped together on the drop-down gallery that appears when you click the Other Charts command button on the Ribbon's Insert tab:
  • Stock charts: Stock charts are used to plot stock quotes over a certain time period, such as a single business day or week. Stock charts show nearly any combination of a stock's highest and lowest values, its open and close values, and the volume of trade for that stock.
  • Surface charts: Surface charts plot trends in values across two dimensions in a continuous curve. In order to use a surface chart, you need at least two data series, both of which are numeric as with an XY (Scatter) chart.
  • Doughnut charts: A doughnut chart is similar to a pie chart except for its ability to display more than one data series (pie charts always graph just a single data series).
  • Bubble charts: Bubble charts compare sets of three values as kind of a combination of an XY (Scatter) chart with an Area chart. When you build a bubble chart, the size of each bubble represented on the x-y grid represents the third set of values being charted.
  • Radar charts: A radar chart shows changes in data relative both to a center point and to each other. This chart type is useful for making relative comparisons among items.

Read more »

How to Add a Filter in Excel 2007-2010

To Filtering is a convenient and reliable way to locate and manage spreadsheet data. We can filter data using the AutoFilter feature in Excel 2007-2010 to display only the data that meets specified criteria. Filtered data can be copied, manipulated, and printed without having to move it to a new spreadsheet. Using AutoFilter, we can filter data by selecting criteria from a list, by numerical conditions, or by color. Here's how to use the AutoFilter feature in Excel 2007-2010.

In Excel 2007 or Excel 2010, if you're using an Excel Table, it has built-in filtering features. If your data is not in an Excel Table, follow these steps to add an Excel 2007 AutoFilter.

1. Select a cell in the database.
2. On the Excel Ribbon, click the Data tab, and then click Filter.
   

A dropdown arrow appears at the right side of each column heading.

Filter One Column

With the Excel Table, or Excel 2007 AutoFilter, in place, you can start filtering the individual columns. You can filter on just one column, such as Customer, or two or more columns.
For example, to filter for a specific customer, follow these steps:

1. Click the drop down arrow at the right of the Customer heading
2. In the list of Customers, click Select All, to remove all the check marks
3. Click the check box for the customer that you want to filter for, such as MegaStore.
4. Click OK, to see the filtered list.

In the filtered worksheet, you'll see some changes:

• only the rows for the selected customer are visible -- the other customer rows have been hidden
• the row numbers for the filtered rows are shown in blue font.
• the drop down arrow in the Customer column has changed to a filter symbol, with a small arrow

Filter Two Columns

After you have filtered one column, you can refine the filtered results, by filtering two or more columns.
For example, after you filter for a specific customer, follow these steps to add a product filter:

1. Click the drop down arrow at the right of the Product heading.
   Note: When another column is filtered, you might not see the full list of products -- you'll only see the products for the filtered records.
2. In the list of Products, click Select All, to remove all the check marks
3. Click the check box, or multiple check boxes, for the product(s) that you want to filter for, such as Bananas and Grapes.
4. Click OK, to see the filtered list.

Clear an Excel 2007 or Excel 2010 AutoFilter

After you have applied one or more filters, you can clear a specific filter, or all the filters in the Excel Table.

Clear a Specific Filter

To clear a specific filter, and leave other filters turned on:

1. Click the drop down arrow in the column heading where you want to clear the filter
2. Click on Clear Filter From [column name]
   

Clear All Filters

Instead of clearing a specific filter, you can clear all the filters from the Excel Table, in one step:

1. Select a cell in the table.
2. On the Excel Ribbon, click the Data tab, and in the Sort & Filter group, click Clear.
   Note: This will also clear the Sort options that you have applied.
   

Filter for Blank Cells

If there are any blank cells in a column, the drop down list in the heading cell will show one additional item -- (Blanks) -- at the end of the item list.
To show only the rows with blank cells in that column, remove the check marks from the other items in the list.

Filter for Highest and Lowest Values

If a column contains numbers, you can filter to find the highest or lowest numbers that column. In this example, you'll filter for the 5 lowest values in the Total column.

1. Click the drop down arrow in the column heading, click Number Filters, and then click Top 10.
   Note: If fewer than half of the values in the column are numbers, the Number Filters option will change to Text Filters

   
   

2. In the Top 10 AutoFilter window, select Bottom from the first drop down list.
3. In the second box, type 5, or use the arrows to change the number to 5
4. In the third drop down box, select Items, then click OK.

   
   

Note: The results are the highest or lowest values for the entire list, not the currently filtered list. If other columns are also filtered, you may see fewer than the specified number of items.

Filter for a Specific Date Range

If a column contains dates, you can filter to find a specific date or date range, or a dynamic date range. In this example, you'll filter for the orders received in January 2011.

1. Click the drop down arrow in the column heading, click Date Filters, and then click Between.

   
   

2. In the Custom AutoFilter dialog box, enter the starting date for the date range -- 01-Jan-2011 -- in the first date box
3. Enter the ending date for the date range -- 31-Jan-2011 -- in the second date box, and click OK
   

Filter for a Dynamic Date Range

If a column contains dates, instead of finding a specific date range, you can filter for a dynamic date range, such as Last Week, Next Month, or Tomorrow. In this example, you'll filter for the orders received in January 2011.

1. Click the drop down arrow in the column heading, click Date Filters, and then click Yesterday.

   
   

2. The filtered table shows only the orders that were received yesterday.

   
   

Update an Excel 2007 AutoFilter

When you open the workbook at a later date, the Excel 2007 AutoFilters don't refresh automatically. For example, if you used a dynamic date filter, such as Yesterday, the correct day's results won't show in the filtered table when you open the file the next day. (Note: You could create a macro to update the filter, and set it to run when the file opens.)
To see the updated filtered results, you can reapply the filter.

1. Select any cell in the filtered table
2. On the Excel Ribbon, click the Data tab, and in the Sort & Filter group, click Reapply.
   
   

This will update any dynamic filters in the table, and will also revise the results for any new or changed data.

Create a Custom Filter

Some of the Excel 2007 AutoFilter options in the drop down lists end with three dots, such as Begins With...

These filters, as well as the Custom Filter option, open the Custom AutoFilter window. In there, you can enter one or two criteria, to create a customized filter.

To filter for one criterion:

1. From the first dropdown list, select an option, such as "begins with".
2. In the text box, type a value, then Click OK.

In the screen shot below, the Custom Filter will find records where the customer name begins with "M".

To filter for two criteria:

1. From the first dropdown list, select an option, such as "begins with".
2. In the text box, type a value, then Click OK.
3. Select "And" or "Or" as the operator
4. From the first dropdown list, select an option, such as "ends with".
5. In the text box, type a value, then Click OK.

In the screen shot below, the Custom Filter will find records where:

• the customer name begins with "M"
• OR the customer name ends with "Shop".

Read more »

Convert text URLs into active hyperlinks

Convert a column of text URLs into active hyperlinks in Excel

Hope you don't want to make a macro and as long as you don't mind an additional column, then just create a new column alongside your column of URLs.

Now in the new column type in the formula =HYPERLINK(A1) (replacing A1 with whatever cell you are interested in).

After that, copy the formula down the rest of the 200 entries.

Read more »