EXCEL

Converting Tables to Ranges

Although Excel tables are extremely useful, there are times when you do not want your data formatted as a table. Certain data analysis tools, including creating subtotals and grouping outlines, are not available if data are formatted as a table. STEPS: On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Excel displays a message box asking if you want to convert the table to a normal range. Click Yes.

Adding Markers to Sparklines

Markers are visual representations of data points on a Sparkline. Adding markers can help identify the data points or emphasize high or low points. To add markers to Sparklines, first click anywhere in the Sparklines, and then check the options you want in the Sparkline Tools Design tab, Show group: To add data point markers for every data point, click the Markers check box. To remove data point markers, click the Markers check box again to remove the checkmark. To add a marker for just the highest value, click the High Point check box.

Adding Total Rows to Tables

If you have data formatted as a table, you can add a Total row to quickly calculate an aggregate function such as the sum, average, minimum, or maximum of all the values in the column. STEPS On the Table Tools Design tab, in the Table Style Options group, click the Total Row check box. In the Total row at the bottom of the table, click the cell where you want to add the calculation. Click the arrow, and select a function. Excel inserts the formula for you.

Importing Data from Access

Microsoft Access is a relational database program. Data are stored in multiple tables that connect to one another via related fields. Through queries based on table relationships, the database can create a specific view of data combining fields from multiple tables. Organizations often use a database to input and store data and then import those data into Excel to perform statistical and what-if analyses. STEP: On the Data tab, in the Get & Transform group, click the Get Data button. Point to From Data base and click From Microsoft Access Database. In the Import Data dialog, if necessary, navigate to the location of the Access database. Click the Access database, and then click the Import button. In the Navigator window, select the database object you want to import, and then click the Load button. By default, the data will be imported as a table in a new worksheet. To update u can press refresh in query then to disconnect press unlink

Applying Quick Styles and Other Formatting to Shapes

Shape Quick Styles allow you to apply a combination of formatting such as borders, rounded corners, and effects with a single command. When you insert a shape, Excel applies the default Quick Style for the workbook's theme to the shape. To adjust the formatting of the shape you can choose a new Quick Style from the Shapes Styles gallery. To apply a Quick Style to a shape: Select the shape you want to change. On the Drawing Tools Format tab, in the Shape Styles group, click a Quick Style in the collapsed gallery on the Ribbon. If the Quick Style you want to use does not appear on the Ribbon, click the More button to display the gallery and click a Quick Style in the gallery to apply it to the shape. ---- You can further adjust the look of a shape by changing the following: +New Note to Section Shape fill—The color that fills the object. You can choose a solid color from the color palette or fill the color with a gradient or texture. Shape outline—The line that surrounds the shape. You can further adjust the outline of shapes by changing the color of the outline, width of the outline, and style of the outline. Shape effects—Graphic effects you can apply to shapes including drop shadows, reflections, glows, and bevels. +New Note to Section To further adjust the formatting of a shape: +New Note to Section Select the shape you want to change. Click the Drawing Tools Format tab. To change the fill of a shape, in the Shape Styles group, click the Shape Fill button and select an option from the color palette. To make the shape appear transparent, click the Shape Fill button and select No Fill. To change the outline of a shape, in the Shape Styles group, click the Shape Outline button.Select an option from the color palette to change the color of the outline.Point to Weight and select a thickness option for the outline.Point to Dashes and select a dash style for the outline. To apply a shape effect, in the Shape Styles group, click the Shape Effects button, point to a shape effect, and select an option.

Refreshing Data in a PivotTable

The skill Creating PivotTables Using Recommended PivotTables introduces the concept of PivotTables, how to create them, add fields, and change the function used to summarize the data. Recall that PivotTables do not contain any data themselves—they summarize data from another part of your workbook. After you create a PivotTable, if the underlying data change, you must update or refresh the PivotTable to see the changes. STEPS: Select any part of the PivotTable. On the PivotTable Tools Analyze tab, in the Data group, click the Refresh button.

Applying Quick Styles and Colors to Charts

You can change the style of a chart using the predefined Quick Styles. Quick Styles apply combinations of fonts, line styles, fills, and shape effects. You can also change the color scheme from a preset list of colors that coordinate with the workbook theme. STEPS Select the chart. On the Chart Tools Design tab, in the Chart Styles group, click the style you want to use, or click the More button to see all of the Quick Styles available To change the color scheme, on the Chart Tools Design tab, in the Chart Styles group, click the Change Colors button and select the color scheme you want.

Applying a Quick Style to a Pivot Table

You can customize PivotTables in almost any way imaginable. As with other tables, you can apply a Quick Style to a PivotTable to give it a professional, polished appearance. STEPS: Click anywhere in the PivotTable. On the PivotTable Tools Design tab, in the PivotTable Styles group, click a style to apply it. To expand the PivotTable Quick Styles gallery, click the More button.

Creating a PivotChart from a PivotTable

A PivotChart is a graphic representation of a PivotTable. In a column chart, the category fields are represented along the x (horizontal) axis while the data values are represented along the y (vertical) axis. Select any cell in the PivotTable. On the PivotTable Tools Analyze tab, in the Tools group, click the PivotChart Select a chart type from the Insert Chart press OK ----- The Insert Chart dialog used when creating a PivotChart is very similar to the one used when creating regular charts. Click a chart type at the left side of the dialog, and then click an option from the examples along the top of the right hand pane. Excel displays a preview of the chart. There are no recommended charts for PivotCharts. If you need more room to display the PivotChart, you may want to hide the PivotTable Fields pane. On the PivotChart Tools Analyze tab, in the Show/Hide group, click the Field List button to hide or display the PivotTable Fields pane.

Creating PivotTables Using Recommended PivotTables

A PivotTable is a special report view that summarizes data and calculates the intersecting totals. PivotTables do not contain any data themselves—they summarize data from a cell range or a table in another part of your workbook. steps Select any cell within the table or cell range you want to use for your PivotTable. On the Insert tab, in the Tables group, click the Recommended PivotTables button. Preview each of the options in the Recommended PivotTables dialog. Click the one that is closest to how you want your final PivotTable to look. Click OK to create the PivotTable in a new worksheet. --- In the PivotTable Fields pane, check the boxes in from of each column name to add or remove fields from the PivotTable. +New Note to Section Fields that contain text or date data are added to the Rows section automatically. If there are multiple rows, Excel will attempt to group them in a logical way. Fields that contain numeric data are added to the Values section automatically. For each row in the PivotTable, Excel summarizes the values in each of the fields listed in the Values section. In Figure EX 5.36, adding the Order Cost field to the PivotTable adds it to the Values section and calculates the total value ordered for each PO #. To change the calculation type for a field: Click anywhere in the field you want to change. On the PivotTable Tools Analyze tab, in the Active Field group, click the Field Settings button. In the Value Field Settings dialog, Summarize Values By tab, select the function for the type of calculation you want. Click OK.

Creating a Combination Chart

A combination chart, or combo chart, allows you to specify different chart types for individual data series. The most common combination is a column chart combined with a line chart. To create a combination chart: Select the data you want to include in the chart. On the Insert tab, in the Charts group, click the Combo Chart button. Click the chart type you want. To add a secondary axis to a combo chart: If necessary, click the chart to select it. On the Chart Tools Design tab, in the Type group, click the Change Chart Type button. Click the Secondary Axis check box next to the series that you want to plot along the secondary axis.

Inserting a Shape

A shape is a drawing object that you can add to your spreadsheet to call attention to a cell or group of cells. Excel comes with a number of shapes for you to choose from, including lines, block arrows, callouts, and basic shapes such as rectangles and circles. When you insert a shape into a worksheet, it is not inserted into a specific cell. You can move it to another place in the worksheet without affecting the underlying data or worksheet structure. To add a shape: +New Note to Section On the Insert tab, if necessary, click the Illustrations button to expand the Illustrationsgroup. Click the Shapes button and select an option from the Shapes gallery. The cursor changes to a crosshair. Click on the spreadsheet to add the shape. To control the size of the shape more precisely, you can click and drag to draw the shape. +New Note to Section All shapes, except lines, allow you to add text. To add text to a shape: +New Note to Section If necessary, click the shape once to select it. Type the text. Click anywhere outside the shape to accept the text. +New Note to Section If you need to edit the shape text, right-click the shape and select Edit Text. When you are finished editing the text, you can click outside the shape or right-click the shape again and select Exit Edit Text.

Filtering Table Data with Slicers

A slicer is a visual representation of filtering options. You can display multiple slicers and filter the table by multiple values from each. Slicers are available only when the data have been formatted as a table. STEPS: Click anywhere in the table to activate the Table Tools Design tab. On the Table Tools Design tab, in the Tools group, click the Insert Slicer button. In the Insert Slicers dialog, click the check boxes for the column(s) you want in the slicers. Click OK. In the slicer, click the option you want to use as the data filter. If you click another option in the slicer, the first option you selected will toggle off and the new selection will be used as the filter. To use more than one filtering option in a slicer, click the Multi-Select button at the upper right corner of the slicer box. When you enabled multi-select, all the slicer options remain on by default. Click the options you do not want included in the filter to turn them off. Notice that as you make selections in one slicer, options in the other slicer may become unavailable—indicating that there are no data available for that option under the current filtering conditions.

Adding and Removing Trendlines

A trendline is a line that overlays the chart and shows the expected data points based on mathematical analysis of the data. A simple linear trendline calculates the difference between the first two data points and can be used to predict future data points based on that difference. STEPS Click the chart once to select it. Click the Chart Elements button that appears near the upper right corner of the chart. Click the Trendline check box to add a checkmark. If the chart has more than one data series, Excel will display a dialog and prompt you to select the series to which the trendline should be added. Select the data series for the trendline and then click OK. Excel adds the trendline to the chart using the default trendline type and formatting. --- To remove a trendline: Click the chart once to select it. Click the Chart Elements button that appears near the upper right corner of the chart. Click the Trendline check box to remove the checkmark . If the chart has more than one trendline, Excel will display a dialog and prompt you to select the series from which the trendline should be removed.

Working with Waterfall Charts

A waterfall chart begins with the first value anchored on the x axis. Subsequent positive and negative values are shown as floating bars adding to or subtracting from a running total. Increases and decreases are color coded so you can clearly see how each data point contributes to the running total. You can set any value as a "total" to anchor that data point on the x axis. To insert a waterfall chart: Select the data you want to include in the chart. On the Insert tab, in the Charts group, click the Waterfall or Stock Chart button. Select Waterfall. If your chart includes a final data point that represents a total, click any data point to select the data series, and then double-click the data point you want to set as a total. In the Format Data Point task pane, click the Set as total check box.

Adding Subtotals

Adding subtotals is an easy way to outline large amounts of data. In order to use subtotals, the data range must be arranged in columns with the first row containing column headings. The columns must be sorted so the rows you want totaled are grouped together. When you add a subtotal, Excel automatically calculates subtotals based on the grouping you selected. Excel inserts formulas using the SUBTOTAL function in a subtotal row for each group and in a grand total row to summarize all the data in the list. STEPS: Ensure that your data range is organized in rows, with the first row containing column headings. Sort by the column you want to organize into groups. Once the range is sorted into groups, on the Data tab, in the Outline group, click the Subtotal button. In the Subtotal dialog, click the At each change in arrow, and select the column for which you want to create subtotals—the same column you just sorted by. Excel will create a new subtotal line each time it finds a new value in this column. (That's why you need to sort the data first—so matching values in this column are grouped together before you add the subtotals.) Select the function you want to use from the Use function drop-down list. Excel can create subtotals with a variety of functions, but SUM and AVERAGE are probably the most common. In the Add subtotal to box, check the box(es) for the column(s) containing the data to add subtotals to. Verify that the Summary below data check box is selected to place the new subtotal rows below each group. Uncheck this box only if you want the summary row above the details instead. Click OK to add the subtotals and grouping to your data. When rows are outlined with subtotals, outline symbols appear to the left of the column headings and row numbers.

Applying Quick Styles and Other Formatting to Sparklines

Apply a Sparkline Quick Style to change the Sparkline color and marker color(s) at once. You can also specify color for just the Sparklines or individual markers. To change the Quick Style applied to the Sparklines: Click one of the cells containing Sparklines to activate the Sparkline Tools Design tab. If necessary, click the tab. In the Style group, if the Quick Style you want is not visible on the Ribbon, click the More button to expand the gallery. Click a style to apply it to the Sparklines. When you create Sparklines, the cells containing the Sparklines are grouped together so any formatting changes you make affect them all. You can remove a single Sparkline from the group to make formatting changes to only that Sparkline. To change the Sparkline color or the marker color for a single Sparkline: Click the individual Sparkline you want to format separately from the group. On the Sparkline Tools Design tab, in the Group group, click the Ungroup button. Now when you make formatting changes, only the single Sparkline is affected. To change the Sparkline color, on the Sparkline Tools Design tab, in the Style group, click the Sparkline Color button arrow and select the color you want from the color palette. To change the marker color, on the Sparkline Tools Design tab, in the Style group, click the Marker Color button. Point to the marker you want to change (such as Negative Points) and then select the color you want from the color palette.

Inserting a Column Chart or a Bar Chart

Column charts work best with data that are organized into rows and columns like a table. Data point values are transformed into vertical columns, with the values plotted along the vertical (y) axis. Each row in the table is grouped as a data series. Excel uses the column labels as the categories along the horizontal (x) axis. In the clustered column chart shown in Figure EX 5.1, each column represents a single data point. The data points are grouped (clustered) together by category. Each data series is represented by a single color as shown in the legend below the chart. STEPS Select the data you want to include in the column chart. On the Insert tab, in the Charts group, click the Insert Column or Bar Chart button. Click the chart type you want to insert the chart into the worksheet. ---- Bar charts are like column charts turned on their side. The categories are displayed on the vertical axis, and the data point values are plotted along the horizontal axis. Select the data you want to include in the bar chart. On the Insert tab, in the Charts group, click the Insert Column or Bar Chart button. Click the chart type you want to insert the chart into the worksheet.

Applying Conditional Formatting Using the Quick Analysis Tool

Conditional formatting provides a visual analysis of data by applying formatting to cells based on their values. Excel offers a wide variety of conditional formatting options from the Conditional Formatting menu (available on the Home tab, in the Styles group). Data bars, color scales, and icon sets display a visual representation comparing all cell values in the selection. Highlight Cells Rules highlight only cells that meet specific criteria (conditions). Top/Bottom Rules highlight cells that have the highest or lowest values compared to other cells in the selection. If you're not sure which conditional formatting option is best for the selected data, use the Quick Analysis tool. The Quick Analysis tool detects whether you've selected numerical or text data and displays only the conditional formatting options appropriate for each type of data. From the Quick Analysis tool, you can also preview conditional formatting before applying it to your data. STEPS: Select the cells to which you want to apply conditional formatting. Click the Quick Analysis tool button at the lower right corner of the selection. Notice that Live Preview displays the effect of the conditional formatting on your data as you hover the mouse pointer over each of the formatting options. Review each formatting option before making your selection. Click the button for the type of conditional formatting you want to apply.

Applying Conditional Formatting with Highlight Cells Rules

Conditional formatting with Highlight Cells Rules allows you to define formatting for cells that meet specific numerical or text criteria (e.g., greater than a specific value or containing a specific text string). Use this type of conditional formatting when you want to highlight cells based on criteria you define STEPS: Select the data you want to apply conditional formatting to. On the Home tab, in the Styles group, click the Conditional Formatting button. From the menu, point to Highlight Cells Rules, and click the option you want. Each option opens a dialog where you can enter a value or date to which the value of each cell will be compared. The default formatting is light red fill with dark red text. To change the formatting, expand the dropdown list in the dialog and select another option. Click OK to apply the conditional formatting.

Applying Conditional Formatting with Data Bars, Color Scales, and Icon Sets

Data Bars—Display a color bar (gradient or solid) representing the cell value in comparison to other values (cells with higher values have longer data bars). Color Scales—Color the cells according to one of the color scales (e.g., red to green [bad/low to good/high] or blue to red [cold/low to hot/high]). Icon Sets—Display a graphic in the cell representing the cell value in relation to other values. STEPS: Select the data you want to apply conditional formatting to. On the Home tab, in the Styles group, click the Conditional Formatting button. From the menu, point to Data Bars, Colors Scales, or Icon Sets, and then click the specific style of formatting you want. Notice that Live Preview displays the effect of the conditional formatting on your data as you hover the mouse pointer over each of the formatting options.

Filtering Chart Data

Excel includes a feature that allows you to hide and show data in a chart without having to modify the chart data source. STEPS: SELECT CHART Click the Chart Filters button that appears near the upper right corner of the chart. Notice that when you hover the mouse pointer over the chart filter options, the live preview does not take into account the options that are checked or unchecked. The preview shows only what the chart would look like if the highlighted option were the only one checked. All other series or categories are dimmed in the preview. In Figure EX 5.15, the Annual series is highlighted, so the chart preview shows the Annual series in full color and the other series faded. Click the check boxes to add or remove data series or categories from the chart. Note that the live preview does not update to reflect these changes. Click the Apply button to apply the changes.

Using Advanced Filter

Excel's Advanced Filter feature allows you to filter data for criteria in multiple columns using multiple criteria with logical AND and OR operators. If you are familiar with using Excel database functions or creating queries in Access, using Advanced Filter will seem familiar. Before using Advanced Filter, you must set up the criteria range. The criteria range can be on the same worksheet or another worksheet in the same workbook. It must include a row of data labels that match the data labels in the data set you are going to filter and include at least one row below the data labels where you will enter the criteria for the filter. In the criteria range, type the criteria for which you want to filter. If you type criteria in the same row, Excel will use the logical operator AND in the filter—data must meet both criteria. If you type criteria in separate rows, Excel will apply the logical operator OR—data must meet either criterion. TO USE ADVANCE FILTER STEPS:" Click anywhere in the data set you want to filter. On the Data tab, in the Sort & Filter group, click the Advanced button. If necessary, enter the appropriate cell references in the List range box. If you begin with a cell in the data set selected, Excel will enter the cell range in the List range box for you. Click in the Criteria range box and enter the cell references or the name for the criteria range, including the label row. Be careful that your criteria range does not include blank rows. Click OK.

Inserting Data Using Flash Fill

Flash Fill detects patterns in your data and auto fills the remaining cells for you. This feature is useful when you've imported data that aren't formatted quite as you would like. Flash Fill can be used to combine data from contiguous cells into a single cell or to split data from a single cell into multiple cells. STEPS: Insert a column where you want the new data to appear. In the first cell of the new column below the header row, type an example of how you'd like the data filled. If Flash Fill detects a pattern while you're typing, it will automatically make an autofill suggestion. Sometimes Flash Fill will make a suggestion after you type data in just one cell. Other times, you will need to type two or three examples before Flash Fill recognizes the pattern. Press Enter to accept the suggestion. In Figure EX 9.13, Flash Fill made a suggestion as soon as Fwas typed in cell F3. ---- There will be cases when Flash Fill does not recognize the pattern automatically. However, you can invoke the Flash Fill command manually. Select the first cell below the cell containing the example you want Flash Fill to copy. In Figure EX 9.14, you would begin by selecting cell C3 as cell C2 contains the pattern you want to use to autofill the rest of the column. On the Datatab, in the Data Tools group, click the Flash Fill button to see if Flash Fill can find a pattern to autofill.

Working with Hierarchy Charts

Hierarchy charts visualize data that are organized into categories (called branches) and related subcategories. Excel includes two types of hierarchy charts—treemap and sunburst. In a treemap chart, each category (branch) is represented as a rectangle and the values for each subcategory are represented as rectangles within the category rectangle. Use a treemap chart when you want to compare the relative size of each branch and then compare the subcategories proportionally within each branch. A sunburst chart can show more hierarchy levels than a treemap chart because it visualizes the data as a concentric series of rings with the top-level categories as segments of the inner ring. A sunburst chart can include multiple levels of outer rings. Each branch of the sunburst represents all the levels of a category. To insert a treemap or sunburst hierarchy chart: Select the data you want to include in the chart. On the Insert tab, in the Charts group, click the Hierarchy Chart button. Select the specific chart type you want: Treemap or Sunburst.

Showing and Hiding Chart Elements

If there is not a Quick Layout with exactly the look you want, you can hide or show chart elements individually. Showing and hiding chart elements such as the chart title, axes, and gridlines can make a chart easier to read. STEPS Click any empty area of the chart area to select the chart. Click the Chart Elements button that appears near the upper right corner of the chart. Click the check boxes to show or hide chart elements. To select a specific option for a chart element, click the arrow that appears at the right side of the element name, and then click an option.

Changing the Chart Type

If you find that your initial chart selection wasn't quite right, you can change the chart type from the Change Chart Type dialog without having to delete and recreate the chart. STEPS On the Chart Tools Design tab, in the Type group, click the Change Chart Type button. In the Change Chart Type dialog, click a chart type category to display that category in the right pane. Click one of the chart types along the top of the right pane to see previews of the options available, and then click the chart type you want. Click OK.

Removing Duplicate Rows from Tables

If you have a large table, it may be difficult to identify rows with duplicate data. Excel includes a tool to find and remove duplicate rows. A "duplicate" can be an exact match, where every cell in the row contains the same data, or you can specify matching data for certain columns only. STEPS: Select any cell in the table. On Table Tools Design tab, in the Tools group, click the Remove Duplicates button. By default, all the columns are selected in the Remove Duplicates dialog and Excel will remove duplicates only where the data in the rows are 100 percent identical. To identify duplicate rows where only some of the columns have duplicate data, click the check boxes to uncheck column names. Now Excel will identify rows as duplicates only when the checked columns have the same data. Click OK to remove duplicate rows from the table. Excel displays a message box, telling you how many duplicate rows were found and removed and how many unique values remain in the table. Click OK to dismiss the message box.

Importing Data from a Text File

If you work with data from external sources such as vendors or customers, those data may be available only in plain text format. Text files are common for passing data between organizations because they can be read by many different application programs. A text file that includes multiple data fields may separate the fields with a specific character (a delimiter). Common delimiters are commas, tabs, and spaces. When you import data from a text file using a delimiter, each data field is imported into a separate column. STEPS: On the Data tab, in the Get & Transform Data group, click the From Text/CSVbutton. In the Import Data dialog, navigate to find the file you want to import, select it, and click the Import button Review the data to be imported. If the data include a header row, verify that Excel has detected this and formatted the preview appropriately. Click the Load button to import the data. By default, the data will be imported as a table in a new worksheet. ------- If the data include a header row and Excel does not detect it, you will need to edit the query before completing the import. Review the import preview carefully. Click the Edit button to open the Query Editor. On the Home tab, in the Transformgroup, click the Use First Row as Headers button. On the Home tab, in the Close group, click the Close & Load button.

Creating an Outline

If your workbook includes a data range organized with summary rows that include formulas, you can use the Auto Outline feature to automatically create groups and subgroups of rows that you can expand and collapse. The summary row for each group must include a formula in a consistent location. Auto Outline automatically groups together the rows above each row that includes the formula. STEPS: Click any cell in the data range. On the Data tab, in the Outline group, click the Group button arrow, and click Auto Outline. When rows are outlined, outline symbols appear to the left of the column headings and row numbers. To collapse the outline to show just the subtotals, click the numbered outline symbols at the left of the column headings to collapse or expand all data at that outline level. To show the details for a specific outline group, click the + symbol next to the row you want to expand. Click the − sign symbol to collapse the group. To remove the entire outline: Select any cell in the outline. On the Data tab, in the Outline group, click the Ungroup button arrow, and select Clear Outline. To ungroup a single group only: Select any cell in the group. On the Data tab, in the Outline group, click the Ungroup button arrow, and select Ungroup. On the Data tab, in the Outline group, click the Ungroup button arrow, and select Ungroup.

Adding a Data Series to a Chart

If your worksheet already includes a chart, you can manipulate the data represented without deleting and recreating the chart. The chart in Figure EX 7.14 includes monthly sales data for services. You can add another data series, in this case for gift certificate sales, through the Select Data Source dialog. STEPS If necessary, select the chart. On the Chart Tools Design tab, in the Data group, click the Select Data button The Select Data Source dialog opens showing the current data series. Click the Add button. The Edit Series dialog opens. Enter the appropriate cell reference for the new series name in the Series name box. You can type or click a cell reference or enter text. Click in the Series values box and delete the default entry. Click and drag the new cell range to enter it in the Series values box or type the new cell range. Click OK. The Select Data Source dialog opens again with the new data series added. Click OK to add the data series to your chart.

Filtering Data

If your worksheet has many rows of data, you may want to filter the data to show only rows that meet criteria you specify. If your data are formatted as a table, filtering is enabled automatically. If your data are not formatted as a table, you must first enable filtering: STEPS: filter On the Home tab, in the Editing group, click the Sort & Filter button. Click the Filter button to enable filtering. An arrow will appear in each cell of the heading row just as if the data were formatted as a table. -Once filtering is enabled, it works the same whether or not the data have been formatted as a table. to filter data: STEPS: Click the arrow at the top of the column that contains the data you want to filter for. At first, all the filter options are checked. Click the (Select All) check box to remove all the checkmarks. Click the check box or check boxes in front of the values you want to filter by. Click OK. Excel displays only the rows that include the values you specified.

Converting Data into Tables

In Excel, you can define a series of adjacent cells as a table. When you define data as a table, Excel provides a robust tool set for formatting and analyzing the data. In the table, the header row automatically includes filtering and sorting. When you add data to the right of the table, Excel includes the new column in the table automatically. To define data as a table: Click any cell in the data range. You do not need to select the entire range. On the Home tab, in the Styles group, click the Format as Table button to display the Table Stylesgallery. Click the style you want to use for your table. Excel will automatically populate the Format As Table dialog with the entire data range. If the data range is not correct, you can manually enter the cell range in the dialog. Be sure to check the My table has headers check box if appropriate. Click OK to create the table.

Creating a Custom Filter

In addition to filtering by matching exact values or cell attributes, you can filter for values that meet broader criteria. Columns with different data types have different filtering criteria options available. If the data are formatted as a table, filtering is enabled automatically. If your data are not formatted as a table, you must first enable filtering. On the Data tab, in the Sort & Filter group, click the Filter button. STEPS: Click the AutoFilter arrow at the top of the column that contains the data you want to filter for, and point to Date Filters. Click the filter type you want. There is an extensive list of custom date filter options as shown in Figure EX 8.16. The first box in the Custom AutoFilter dialog shows the custom filter option you selected. Type the date criteria in the box next to it. You can further refine the filter by expanding the second list and selecting a filter option and then entering the criteria in the box next to it. The filter options available are the same as those in the Date Filters menu. The And radio button requires that data meet both criteria in order to be included in the filter .The Or radio button requires that data meet either of the criteria. Click OK to apply the filter

Using the Recommended Charts Feature

One trick to working with charts is to select the correct chart type. Excel makes this easier by recommending specific chart types based on the data you have selected in the worksheet. The recommended charts are available from both the Quick Analysis tool and the Chart Options dialog. STEPS Select the data you want to visualize as a chart. The Quick Analysis tool button appears near the lower right corner of the selected range. Click the Quick Analysis tool button, and then click the Charts tab in the Quick Analysis Tool. Hover the mouse cursor over each chart type to see a live preview of the chart. Click the button for the chart type you want. Recommended charts may include multiple versions of the same chart type. Check the live preview carefully to ensure that the data display exactly as you want. ----------------- If none of the recommended charts presented in the Quick Analysis tool are precisely what you want, use the Recommended Charts button to open the Insert Chart dialog: Select the data for the chart. On the Insert tab, in the Charts group, click the Recommended Charts button. The first tab in the Insert Chart dialog, Recommended Charts, displays the same chart options as the Charts tab in the Quick Analysis tool, plus a few more. When you select a chart type, a preview of the chart appears in the right pane of the dialog. Click OK to insert the selected chart into the worksheet.

Applying Conditional Formatting with Top/Bottom Rules

One way to analyze worksheet data is to compare cell values to other cell values. When analyzing a worksheet, you may want to highlight the highest or lowest values or values that are above or below the average. In these cases, use conditional formatting Top/Bottom Rules. When you use Top/Bottom Rules, Excel automatically finds the highest, lowest, and average values to compare values to, rather than asking you to enter criteria (as you do when using Highlight Cells Rules). STEPS: Select the data you want to apply conditional formatting to. On the Home tab, in the Styles group, click the Conditional Formatting button. From the menu, point to Top/Bottom Rules, and click the option you want. Each option opens a dialog where you can select the formatting to apply when cells meet the condition. The top and bottom options allow you to modify the threshold to a value other than 10 (top/bottom 10 items and top/bottom 10%). Click OK to apply the conditional formatting.

Inserting a Pie Chart

Pie charts represent data as parts of a whole. They do not have x and y axes like column charts. Instead, each value is a visual "slice" of the pie. Pie charts work best when you want to evaluate values as they relate to a total value—for example, departmental budgets in relation to the entire budget, or each employee's bonus in relation to the entire bonus pool. To add a pie chart: Select the data you want to include in the pie chart. On the Insert tab, in the Charts group, click the Insert Pie Chart button. Click the chart type you want to insert the chart into the worksheet.

Sorting Data on Multiple Critera

Recall that sorting rearranges the rows in your worksheet by the data in a column. If you want to create a multilevel sort so the data are sorted first by one criteria and then sorted again within each group, use the Sort dialog. For each sort level, set the column to sort by, the type of sort, and the order in which you want the data sorted. STEPS: Select any cell in the data range. On the Data tab, in the Sort & Filter group, click the Sort button. If your data include a header row, Excel will usually identify that for you and automatically check the My data has headers check box. In the Sort dialog, expand the Sort by list, and select the column you want to use for the first sort level. The Sort On list defaults to Value. If you want to sort by a cell attribute instead, expand the Sort On list and select Cell Color, Font Color, or Cell Icon. The Order list detects what type of data are in the column and displays appropriate options such as A-Z for text, Smallest to Largest for numbers, and Oldest to Newest for dates. To change the sort order, expand the list and select another option. Click the Add Level button. Expand the Then by list, and select the column you want for the next sort level. Continue adding levels and building the sort criteria until you are finished. To rearrange the order of sort criteria, click the level you want to move, and then use the Move Up or Move Down arrow. To delete a level from the sort, click the level you want to delete, and then click the Delete Level button. When you have built all the levels for the sort, click OK.

Emphasizing a Data Point in a Pie Chart

Remember that pie charts work best when you want to evaluate data as part of a whole. Each "slice" represents a data point. To emphasize a single data point, you can rotate the pie chart to place the slice where you want it and separate ("explode") that slice from the rest of the pie. To rotate the pie chart: Click the pie once to select the data series, and then click the data point (slice) you want to emphasize. On the Chart Tools Format tab, in the Current Selection group, click the Format Selection button. The Format Data Point task pane opens to the Series Options panel. In the Angle of first slice box, type the degree of rotation for the chart (from 0 to 360) and press Enter. You can also use the Angle of first slice slider to change the rotation, but it can be difficult to select a precise angle. To explode a single data point: If it is not already open, open the Format Data Point task pane. In the Series Options panel, type a percentage in the Point Explosion box and press Enter. The higher the percentage of explosion, the further away the slice will appear. You can use the Point Explosionslider to change the degree of explosion, but it can be difficult to select a precise degree of explosion.

Sorting Data

Sorting rearranges the rows in your worksheet by the data in a column or columns. You can sort alphabetically by text, by date, or by values. Sorting works the same whether or not the data have been formatted as a table. STEPS: Click any cell in the column you want to sort by. On the Data tab, in the Sort & Filter group, click the button for the sort order you want: sorts text in alphabetical order from A to Z, numbers from smallest to largest, and dates from oldest to newest. sorts text in reverse alphabetical order from Z to A, numbers from largest to smallest, and dates from newest to oldest.

Inserting Sparklines

Sparklines represent a series of data values as an individual graphic within a single cell. As you update the underlying data series, the Sparklines update immediately. To add Sparklines to your worksheet using the Quick Analysis tool: Select the data you want to visualize as Sparklines. If you want the Sparklines to appear in empty cells, ensure that there are empty cells to the right of your selection. The Quick Analysis Tool button appears near the lower right corner of the selected range. Click the Quick Analysis Tool button, and then click the Sparklines tab. Click the button for the Sparkline type you want. If you do not want Excel to place the Sparklines automatically, or if you are adding Sparklines in a situation where the Quick Analysis tool is not available, use theCreate Sparklinesdialog to specify their location: Select the data range with the data points for the Sparklines. On the Insert tab, in the Sparklines group, click the button for the type of Sparkline you want to insert: Line, Column, or Win/Loss. The Create Sparklines dialog opens with the selected range added to the Data Range box. In the Location Range box, enter the cell range where you want the Sparklines to appear. You can type the cell range or click and drag in the worksheet to select the cells. Click OK to insert the Sparklines.

Exporting to Text Formats

Text file formats allow Excel data to be read by a variety of applications. The most common text file formats are Text (Tab delimited) (.txt) and CSV (comma delimited) (.csv). When you save in one of the text formats, only the current worksheet will be saved. (Text formats do not support multiple worksheets in the same file.) You will also lose all formatting, and cells with functions will be converted to their current values. STEPS: Click the File tab Click Save As. On the Save As page, expand the list of file types and select the text format you want. Click Save. If your workbook includes more than one worksheet, Excel displays a message warning that the text file format does not support multiple worksheets. Click OK to dismiss the message Excel may show yet another warning message at the top of the worksheet that some features might be lost if you save in the selected format. Click the X to close the warning message and save the file.

Formatting Other Chart Elements

Text-specific formatting options are available for chart elements that include text (the title, legend, data labels, and data table). WordArt styles are a preset combination of fill, outline, and text effects that can be applied to any text element. STEPS Click the chart element to select it. (Always double-check the Chart Elements box on the Chart Tools Format tab, Current Selectiongroup to ensure that the correct chart element is selected.) On the Chart Tools Format tab, in the WordArt Styles group, click the WordArt Quick Styles button to expand the gallery. Click the style you want. -=---=-=-=-=-=-=-=-=-=-= You can apply individual formatting options to text elements using the other buttons in the WordArt Styles group: To change the font color, click the Text Fill button and select a color. To apply a border around each text letter or number, click the Text Outline button and select a color. To change the outline line width, point to Width and select an option. To change the line style, point to Dashes and select an option. To apply a text effect, click the Text Effects button, point to the type of effect you want (Shadow, Reflection, or Glow), and select an option. -0=-0=-=-=-=-= If you want to change the font, font size, or other font formatting characteristics, use the options available on the Home tab, in the Font group. To add visual interest to the chart, try applying a gradient to the chart background. A gradient transitions the background from a dark shade to light shade of a single color. If necessary, click an empty area of the chart to select the chart area. On the Chart Tools Format tab, in the Shape Styles group, click the Shape Fill button, point to Gradient, and select the gradient you want. Be careful to choose a gradient that doesn't overwhelm the chart data. ---------------- Chart gridlines help the user estimate the values represented by the data markers. They can be an important element in the chart, but they should not be the primary visual focus. Selecting the gridlines can be tricky; it may be easier to use the Chart Elements list. In the Chart Elements list, the vertical axis gridlines are the lines that run horizontally parallel to the x axis, and the horizontal axis gridlines run up-and-down parallel to the y axis. Pie charts do not have gridlines. On the Chart Tools Formattab, in the Current Selectiongroup, expand the Chart Elements box and select the gridlines. On the Chart Tools Formattab, in the Shape Styles group, click the Shape Outline button and click the color you want. You can also change the style of the lines. Click the Shape Outline button again, point to the type of style you want to change (Width, Dashes, or Arrows), and select an option.

Managing Conditional Formatting Rules

When more than one conditional formatting rule is applied to a group of cells, the rules are applied in the order in which they are created. This can sometimes result in conflicts or unexpected results. The Conditional Formatting Rules Manager allows you to delete, edit, reorder, and control the way in which conditional formatting rules are applied to your worksheet. On the Home tab, in the Styles group, click the Conditional Formatting button. Click Manage Rules... If necessary, expand the Show formatting rule for list, and select the part of the workbook you want to manage rules for. From the box next to each formatting rule, you can edit the range of cells to which the rule is applied. If the data in your worksheet have changed since you first created the conditional formatting rule, this is an easy way to update the cell range. When there are multiple formatting rules applied to the same set of cells, click the Stop If Truecheck box to prevent later conditional formats from being applied to the cells when the selected condition is true. To change the order in which rules are applied, click the rule you want to be applied first, and click the Move Up button until that rule is at the top of the list. To delete a rule, click the rule you want to delete, and then click the Delete Rule button. To modify one of the rules, click the rule you want to change, and then click the Edit Rule button. Make the changes you want, and then click OK. You can click the Apply button to see the effect of the changes on your worksheet before saving them. Click OK to save the changes, or click Cancel to close the Conditional Formatting Rules Manager without making any changes. *To change formatting for a data bar rule* Select the rule in the Conditional Formatting Rules Manager. Click the Edit Rule... button. If you do not want the data bars applied to every cell, expand the Type list under Minimum and Maximum and make a different selection. When you change the type to something other than Automatic, you can set specific values to use in the Value boxes. To change the fill type for the data bars, expand the Fill list and select Gradient Fill or Solid Fill. To change the color, click the Color arrow to display the color palette and select a color. To show only the data bars and hide the cell values, click the Show Bar Onlycheck box. Click OK in the Edit Formatting Rule dialog, and then click OK again in the Conditional Formatting Rules Manager to save the changes.

Formatting a Data Point or a Data Series

When you apply a chart style or color from the Chart Tools Design tab, Chart Styles group, it is applied to the entire chart. If you want to override the formatting for an individual data point or data series, select the data point or data series, and then use the options from the Chart Tools Formattab, Shape Stylesgroup instead. To select a data series, click any data point in the series. To select a specific data point, click the data point once to select the entire data series, and then click the data point again to select just that data point. Do not double-click. Double-clicking a data point opens the Data Series task pane for the entire data series. Before applying formatting changes, always verify that you have the correct data series or data point selected by checking the Chart Elements box on the Chart Tools Format tab, Current Selection group. Shape Quick Styles allow you to apply a combination of formatting such as borders, rounded corners, and effects with a single command. STEPS Select the data point or data series you want to change. On the Chart Tools Format tab, in theShape Styles group, click the More button to expand the Shape Quick Styles gallery. Click the style you want. ---- To apply a fill color to a data point or data series: Select the data point or data series you want to change. On the Chart Tools Format tab, in the Shape Styles group, click the Shape Fill button and then click the color you want. From the Shape Fill menu, you can also apply a picture, gradient, or texture to a data series or data point. To apply an outline to a data point or data series: Select the data point or data series you want to change. On the Chart Tools Formattab, in the Shape Stylesgroup, click the Shape Outlinebutton, and click the color you want. To change the outline line width, click the Shape Outlinebutton, point to Weight, and select the width you want. To undo changes and return the selected element to the default chart style: Select the data point or data series you want to revert to the original chart style. On the Chart Tools Formattab, in the Current Selectiongroup, click the Reset to Match Style button.

Applying Quick Styles to Tables

When you are working with a table, use the Table Tools Design tab to apply formatting. This contextual tab is available when you select any cell in the table. From the Table Styles gallery, you can select a Quick Style to apply a combination of font colors, fill colors, and banding options. Banding applies a fill color to alternating rows or columns, making the table easier to read. STEPS Click anywhere in the table. On the Table Tools Design tab, in the Table Styles group, click the Quick Styles button to display the full Table Styles gallery. Depending on the width of your Excel window, you may see part of the Quick Styles gallery displayed in the Ribbon. In this case, click the More button to expand the entire gallery. Click a style to apply it to the table.

Resizing and Moving Charts

When you first create a chart, Excel places the chart in the middle of the worksheet. Often, you will need to resize and move the chart so it doesn't cover the worksheet data. With the chart selected, resize the chart using the resize handles located at the four corners and the middle of each side of the chart area. Click and drag toward the center of the chart to make it smaller or outward to make it larger. STEPS: Select the chart. Be careful not to click in the plot area or you will move just the plot area instead of the entire chart. Your mouse cursor will change to the move cursor . With your left mouse button depressed, drag the chart to the new location on the worksheet, and then release the mouse button to "drop" the chart at the new location. --- If your chart is large or complex, you may want the chart to appear on its own worksheet. To move a chart to a new sheet: If necessary, select the chart. If you just created the chart, it will still be selected. On the Chart Tools Design tab, in the Location group, click the Move Chart button. In the Move Chart dialog, click the New sheet radio button to move the chart to its own worksheet. If you want to specify a name for the new sheet, type it in the New sheet box. To move the chart to an existing worksheet, click the Object in radio button, and then select the name of the sheet you want to move the chart to. Click OK.

Applying Quick Layouts to Charts

When you insert a chart, Excel displays the Chart Tools Design contextual tab. From this tab, you can change the chart layout. Quick Layouts apply combinations of data labels, titles, and data tables. Data labels—Display data values for each data marker. Data table—Displays a table of the data point values below the chart. StEPS Select the chart. On the Chart Tools Design tab, in the Chart Layouts group, click the Quick Layout button. Notice that as you move over each Quick Layout option, Excel shows a preview of the layout. Click the layout you want to apply it to the chart.

Filtering and Sorting Using Cell Attributes

You can sort or filter data based on cell color, font color, or icons that were applied with conditional formatting or cell styles. If you need a refresher on sorting and filtering data, refer to the skills Sorting Data and Filtering Data. STEPS: If necessary, on the Data tab, in the Sort & Filter group, click the Filter button to display the AutoFilter arrows at the top of each column. If the data are formatted as a table, these arrows will be available automatically. Click the AutoFilter arrow at the top of the column that includes the cell format you want to sort by. Point to Sort by Color, and click the option you want to sort by. Excel automatically displays the options available for that particular column: Sort by Cell Color, Sort by Font Color, and Sort by Cell Icon. To filter data by cell attribute: If necessary, on the Data tab, in the Sort & Filter group, click the Filter button to display the AutoFilter arrows at the top of each column. If the data are formatted as a table, these arrows are available automatically. Click the AutoFilter arrow at the top of the column that includes the cell format for which you want to filter. Point to Filter by Color, and click the option for which you want to filter. Excel automatically displays the options available for that particular column: Filter by Cell Color, Filter by Font Color, and Filter by Cell Icon.