Chart Filter Excel 2016 For Mac

| 16-04-2021
[RAND-1-100] Comments



Excel 2016 for Mac review: Spreadsheet app can do the job—as long as you don’t rely on macros Microsoft's spreadsheet app is more friendly to general Mac users, but less friendly to power users. However, the chart data is entered and saved in an Excel worksheet. If you insert a chart in Word or PowerPoint, a new sheet is opened in Excel. When you save a Word document or PowerPoint presentation that contains a chart, the chart's underlying Excel data is automatically saved within the Word document or PowerPoint presentation. Nov 03, 2014 Learn how to use Chart Filters in Microsoft Excel in this video by Alicia Katz Pollock of Royalwise Solutions. 3 minute Combo Chart with Excel for Mac 2016. Showing Filters in Charts.

But some filters (top and bottom ten, above and below average) are based on the original range of cells. For example, when you filter the top ten values, you'll see the top ten values of the whole list, not the top ten values of the subset of the last filter. In Excel, you can create three kinds of filters: by values, by a format, or by criteria. This video will show you how to create a scatter plot on Excel 2016 for Mac. I provide examples for creating one line and two lines. Please let me know if you have any questions or want me to cover any other topics as I am open to suggestions! Make sure to like and subscribe for more videos in the future!

Lesson 20: Filtering Data

/en/excel2016/sorting-data/content/

Introduction

If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need.

2016

Optional: Download our practice workbook.

Watch the video below to learn more about filtering data in Excel.

Mac

To filter data:

In our example, we'll apply a filter to an equipment log worksheet to display only the laptops and projectors that are available for checkout.

  1. In order for filtering to work correctly, your worksheet should include a header row, which is used to identify the name of each column. In our example, our worksheet is organized into different columns identified by the header cells in row 1: ID#, Type, EquipmentDetail, and so on.
  2. Select the Data tab, then click the Filter command.
  3. A drop-down arrow will appear in the header cell for each column.
  4. Click the drop-down arrow for the column you want to filter. In our example, we will filter column B to view only certain types of equipment.
  5. The Filtermenu will appear.
  6. Uncheck the box next to Select All to quickly deselect all data.
  7. Check the boxes next to the data you want to filter, then click OK. In this example, we will check Laptop and Projector to view only these types of equipment.
  8. The data will be filtered, temporarily hiding any content that doesn't match the criteria. In our example, only laptops and projectors are visible.

Filtering options can also be accessed from the Sort & Filter command on the Home tab.

To apply multiple filters:

Filters are cumulative, which means you can apply multiplefilters to help narrow down your results. In this example, we've already filtered our worksheet to show laptops and projectors, and we'd like to narrow it down further to only show laptops and projectors that were checked out in August.

  1. Click the drop-down arrow for the column you want to filter. In this example, we will add a filter to column D to view information by date.
  2. The Filter menu will appear.
  3. Check or uncheck the boxes depending on the data you want to filter, then click OK. In our example, we'll uncheck everything except for August.
  4. The new filter will be applied. In our example, the worksheet is now filtered to show only laptops and projectors that were checked out in August.

To clear a filter:

After applying a filter, you may want to remove—or clear—it from your worksheet so you'll be able to filter content in different ways.

  1. Click the drop-down arrow for the filter you want to clear. In our example, we'll clear the filter in column D.
  2. The Filter menu will appear.
  3. Choose Clear Filter From [COLUMN NAME] from the Filter menu. In our example, we'll select Clear Filter From 'Checked Out'.
  4. The filter will be cleared from the column. The previously hidden data will be displayed.

To remove all filters from your worksheet, click the Filter command on the Data tab.

Advanced filtering

If you need a filter for something specific, basic filtering may not give you enough options. Fortunately, Excel includes many advancedfilteringtools, including search, text, date, and numberfiltering, which can narrow your results to help find exactly what you need.

To filter with search:

Excel allows you to search for data that contains an exact phrase, number, date, and more. In our example, we'll use this feature to show only Saris brand products in our equipment log.

  1. Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.
  2. Click the drop-down arrow for the column you want to filter. In our example, we'll filter column C.
  3. The Filter menu will appear. Enter a search term into the search box. Search results will appear automatically below the TextFilters field as you type. In our example, we'll type saris to find all Saris brand equipment. When you're done, click OK.
  4. The worksheet will be filtered according to your search term. In our example, the worksheet is now filtered to show only Saris brand equipment.

To use advanced text filters:

Advanced text filters can be used to display more specific information, like cells that contain a certain number of characters or data that excludes a specific word or number. In our example, we'd like to exclude any item containing the word laptop.

  1. Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.
  2. Click the drop-down arrow for the column you want to filter. In our example, we'll filter column C.
  3. The Filter menu will appear. Hover the mouse over Text Filters, then select the desired text filter from the drop-down menu. In our example, we'll choose Does Not Contain to view data that does not contain specific text.
  4. The Custom AutoFilter dialog box will appear. Enter the desired text to the right of the filter, then click OK. In our example, we'll type laptop to exclude any items containing this word.
  5. The data will be filtered by the selected text filter. In our example, our worksheet now displays items that do not contain the word laptop.
Excel

To use advanced number filters:

Advanced number filters allow you to manipulate numbered data in different ways. In this example, we'll display only certain types of equipment based on the range of ID numbers.

  1. Select the Data tab on the Ribbon, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.
  2. Click the drop-down arrow for the column you want to filter. In our example, we'll filter column A to view only a certain range of ID numbers.
  3. The Filter menu will appear. Hover the mouse over Number Filters, then select the desired number filter from the drop-down menu. In our example, we'll choose Between to view ID numbers between a specific number range.
  4. The Custom AutoFilter dialog box will appear. Enter the desired number(s) to the right of each filter, then click OK. In our example, we want to filter for ID numbers greater than or equal to 3000 but less than or equal to 6000, which will display ID numbers in the 3000-6000 range.
  5. The data will be filtered by the selected number filter. In our example, only items with an ID number between 3000 and 6000 are visible.

Microsoft Excel 2016 For Mac

To use advanced date filters:

Advanced date filters can be used to view information from a certain time period, such as last year, next quarter, or between two dates. In this example, we'll use advanced date filters to view only equipment that has been checked out between July 15 and August 15.

  1. Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.
  2. Click the drop-down arrow for the column you want to filter. In our example, we'll filter column D to view only a certain range of dates.
  3. The Filter menu will appear. Hover the mouse over Date Filters, then select the desired date filter from the drop-down menu. In our example, we'll select Between to view equipment that has been checked out between July 15 and August 15.
  4. The Custom AutoFilter dialog box will appear. Enter the desired date(s) to the right of each filter, then click OK. In our example, we want to filter for dates after or equal to July 15, 2015, and before or equal to August 15, 2015, which will display a range between these dates.
  5. The worksheet will be filtered by the selected date filter. In our example, we can now see which items have been checked out between July 15 and August 15.

Challenge!

  1. Open our practice workbook.
  2. Click the Challenge tab in the bottom-left of the workbook.
  3. Apply a filter to show only Electronics and Instruments.
  4. Use the Search feature to filter item descriptions that contain the word Sansei. After you do this, you should have six entries showing.
  5. Clear the Item Description filter.
  6. Using a number filter, show loan amounts greater than or equal to $100.
  7. Filter to show only items that have deadlines in 2016.
  8. When you're finished, your workbook should look like this:

/en/excel2016/groups-and-subtotals/content/

  • Excel Charts Tutorial
  • Excel Charts Useful Resources
  • Selected Reading

You can use Chart Filters to edit the data points (values) and names that are visible on the displayed chart, dynamically.

Step 1 − Click on the chart.

Step 2 − Click the Chart Filters icon that appears at the upper-right corner of the chart. Two tabs – VALUES and NAMES appear in a new window.

Values

Values are the series and the categories in the data.

Click the Values tab. The available SERIES and CATEGORIES in your data appear.

Values – Series

Step 1 − Point on any of the available series. That particular series will be highlighted on the chart. In addition, the data corresponding to that series will be highlighted in the excel table.

Step 2 − Select the series you want to display and deselect the rest of the series. Click Apply. Only the selected series will be displayed on the chart.

Chart Filter Excel 2016 Mac

Values – Categories

Step 1 − Point to any of the available categories. That particular category will be highlighted on the chart. In addition, the data corresponding to that category will be highlighted in the excel table.

Step 2 − Select the category you want to display deselect the rest of the categories. Click Apply. Only the selected categories will be displayed on the chart.

Names

NAMES represent the names of the series in the chart. By default, names are taken from the excel table.

Chart Filter Button Excel 2016 Mac

You can change the names of the series in the chart using the names tab in the chart filters. Click the NAMES tab in the Chart Filters. The names of the series and the names of the categories in the chart will be displayed.

You can change the names of the series and categories with select data button, in the lower right corner of the chart filters box.

Names – Series

Step 1 − Click the Select Data button. The Select Data Source Dialog Box appears. The names of the series are at the left side of the dialog box.

To change the names of the series,

Step 2 − Click the Edit button above the series names.

The Edit Series dialog box appears. You can also see the cell reference of the name of the first series.

Chart Filter Excel 2016 For Mac Free

Step 3 − Change the cell reference of the name of the first series. Click OK.

You can see that the name of the first series has changed.

Step 4 − Repeat the steps 2 and 3 for the names of the rest of the series.

Note that the names have changed only in the chart. They have not changed in the Excel table.

Names – Categories

To change the names of the categories, you need to follow the same steps as for series, by selecting the edit button above the categories names in the select data source dialog-box.