Hello, I am getting by pretty well in my switch to Mac with Excel generally, but I am put out by an inability to clear multiple filters. Office Home & Business 2019 – For families and small businesses who want classic Office apps and email installed on one PC or Mac for use at home or work.
Filters can be used to narrow down the data in your worksheet and hide parts of it from view. While it may sound a little like grouping, filtering is different because it allows you to qualify and display only the data that interests you.
For example, you could filter a list of survey participants to view only those who are between the ages of 25 and 34. You could also filter an inventory of paint colors to view anything that contains the word blue, such as bluebell or robin's egg blue. In this lesson, you'll learn how to filter the data in your worksheet to display only the information you need. Filtering data F ilters can be applied in different ways to improve the performance of your worksheet.
You can filter text, dates, and numbers. You can even use more than one filter to further narrow your results. Filtered worksheet Filtering options can also be found on the Home tab, condensed into the Sort & Filter command. To add another filter: Filters are additive, meaning you can use as many as you need to narrow your results.
In this example, we'll work with a spreadsheet that has already been filtered to display only laptops and projectors. Now we'll display only laptops and projectors that were checked out during the month of August. Click the drop-down arrow where you want to add a filter.
In this example, we'll add a filter to the Checked Out column to view information by date. Uncheck the boxes next to the data you don't want to view. Check the boxes next to the data you do want to view. In this example, we'll check the box next to August. Clearing a filter. The filter will be cleared from the column. The data that was previously hidden will be on display once again.
To instantly clear all filters from your worksheet, click the Filter command on the Data tab. Advanced filtering To filter using search: Searching for data is a convenient alternative to checking or unchecking data from the list. You can search for data that contains an exact phrase, number, date, or simple fragment. For example, searching for the exact phrase Saris X-10 Laptop will display only Saris X-10 laptops.
Searching for the word Saris, however, will display Saris X-10 laptops and any other Saris equipment, including projectors and digital cameras. From the Data tab, click the Filter command.
Click the drop-down arrow in the column you want to filter. In this example, we'll filter the Equipment Detail column to view only a specific brand.
Enter the data you want to view in the Search box. We'll enter the word Saris to find all Saris brand equipment. The search results will appear automatically.
Worksheet filtered using Search Using advanced text filters Advanced text filters can be used to display more specific information, such as cells that contain a certain number of characters or data that does not contain a word you specify. In this example, we'll use advanced text filters to hide any equipment that is related to cameras, including digital cameras and camcorders. From the Data tab, click the Filter command.
Click the drop-down arrow in the column of text you want to filter. In this example, we'll filter the Equipment Detail column to view only certain types of equipment.
Choose Text Filters to open the advanced filtering menu. Choose a filter. In this example, we will choose Does Not Contain to view data that does not contain the text we specify. Entering filter text.
Click OK. The data will be filtered according to the filter you chose and the text you specified. Using 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.
Excel automatically knows your current date and time, making this tool easy to use. In this example, we'll use advanced date filters to view only the equipment that has been checked out this week. From the Data tab, click the Filter command. Click the drop-down arrow in the column of dates you want to filter.
In this example, we'll filter the Checked Out column to view only a certain range of dates. Choose Date Filters to open the advanced filtering menu. Click a filter. We'll choose This Week to view equipment that has been checked out this week.
Worksheet filtered by date If you're working along with the example file, your results will be different from the images above. If you want, you can change some of the dates so the filter will give more results.
Using advanced number filters Advanced number filters allow you to manipulate numbered data in different ways. For example, in a worksheet of exam grades you could display the top and bottom numbers to view the highest and lowest scores. In this example, we'll display only certain types of equipment based on the range of ID #s that have been assigned to them. From the Data tab, click the Filter command. Click the drop-down arrow in the column of numbers you want to filter. In this example, we'll filter the ID # column to view only a certain range of ID #s.
Choose Number Filters to open the advanced filtering menu. Choose a filter. In this example, we'll choose Between to view ID #s between the numbers we specify.
Worksheet filtered by number Challenge!. Open an existing Excel 2010 workbook.
If you want, you can use this. Filter a column of data. If you are using the example, filter the Type column so it displays only laptops and other equipment. Add another filter by searching for the data you want. If you are using the example, search for EDI brand equipment in the Item Description column.
Clear both filters. Use an advanced text filter to view data that does not contain a certain word or phrase. If you are using the example, display data that does not contain the word cam. This should exclude any camera-related equipment, such as digital cameras and camcorders. Use an advanced date filter to view data from a certain time period. If you are using the example, display only the equipment that was checked out in September 2010.
Use an advanced number filter to view numbers less than a certain amount. If you are using the example, display all ID #s less than 3000.
Named ranges are one of these crusty old features in Excel that few users understand. New users may find them weird and scary, and even old hands may avoid them because they seem pointless and complex.
But named ranges are actually a pretty cool feature. They can make formulas.a lot. easier to create, read, and maintain. And as a bonus, they make formulas easier to reuse (more portable).
In fact, I use named ranges all the time when testing and prototyping formulas. They help me get formulas working faster. I also use named ranges because I'm lazy, and don't like typing in complex references:) Like named ranges? Hate named ranges? Leave your thoughts below. The basics of named ranges in Excel What is a named range? A named range is just a human-readable name for a range of cells in Excel.
For example, if I name the range A1:A100 'data', I can use MAX to get the maximum value with a simple formula. = (data ) // max value = (data ) // min value = (data ) // total values = (data ) // min value Creating a named range is easy Creating a named range is fast and easy. Just select a range of cells, and type a name into the name box. When you press return, the name is created: To quickly test the new range, choose the new name in the dropdown next to the name box. Excel will select the range on the worksheet. Excel can create names automatically (ctrl + shift + F3) If you have well structured data with labels, you can have Excel create named ranges for you.
Just select the data, along with the labels, and use the 'Create from Selection' command on the Formulas tab of the ribbon: You can also use the keyboard shortcut control + shift + F3. Using this feature, we can create named ranges for the population of 12 states in one step: When you click OK, the names are created. You'll find all newly created names in the drop down menu next to the name box: With names created, you can use them in formulas like this. = (MN,WI,MI ) Update named ranges in the Name Manager (Control + F3) Once you create a named range, use the Name Manager (Control + F3) to update as needed. Select the name you want to work with, then change the reference directly (i.e. Edit 'refers to'), or click the button at right and select a new range.
There's no need to click the Edit button to update a reference. When you click Close, the range name will be updated. Note: if you select an entire named range on a worksheet, you can drag to a new location and the reference will be updated automatically. However, I don't know a way to adjust range references by clicking and dragging directly on the worksheet. If you know a way to do this, chime in below! See all named ranges (control + F3) To quickly see all named ranges in a workbook, use the dropdown menu next to the name box.
If you want to see more detail, open the Name Manager (Control + F3), which lists all names with references, and provides a filter as well: Note: on a Mac, there is no Name Manager, so you'll see the Define Name dialog instead. Copy and paste all named ranges (F3) If you want a more persistent record of named ranges in a workbook, you can paste the full list of names anywhere you like. Go to Formulas Use in Formula (or use the shortcut F3), then choose Paste names Paste List: When you click the Paste List button, you'll see the names and references pasted into the worksheet: See names directly on the worksheet If you set the zoom level to less than 40%, Excel will show range names directly on the worksheet: Thanks for this tip, Felipe!
Names have rules When creating named ranges, follow these rules:. Names must begin with a letter, an underscore , or a backslash ( ). Names can't contain spaces and most punctuation characters. Names can't conflict with cell references – you can't name a range 'A1' or 'Z100'.
Single letters are OK for names ('a', 'b', 'c', etc.), but the letters 'r' and 'c' are reserved. Names are not case-sensitive – 'home', 'HOME', and 'HoMe' are all the same to Excel. Named ranges in formulas Named ranges are easy to use in formulas For example, lets say you name a cell in your workbook 'updated'. The idea is you can put the current date in the cell (Ctrl +;) and refer to the date elsewhere in the workbook. The formula in B8 looks like this. = 'Updated: ' & (updated, 'ddd, mmmm d, yyyy' ) You can paste this formula anywhere in the workbook and it will display correctly. Whenever you change the date in 'updated', the message will update wherever the formula is used.
See for more examples. Named ranges appear when typing a formula Once you've created a named range, it will appear automatically in formulas when you type the first letter of the name.
Press the tab key to enter the name when you have a match and want Excel to enter the name. Named ranges can work like constants Because named ranges are created in a central location, you can use them like constants without a cell reference. For example, you can create names like 'MPG' (miles per gallon) and 'CPG' (cost per gallon) with and assign fixed values: Then you can use these names anywhere you like in formulas, and update their value in one central location.
Named ranges are absolute by default By default, named ranges behave like absolute references. For example, in this worksheet, the formula to calculate fuel would be. =C5 /MPG Since MPG is absolute by default, the formula can be copied down column D as-is. Named ranges can also be relative Although named ranges are absolute by default, they can also be relative. A relative named range refers to a range that is relative to the position of the active cell at the time the range is created.
As a result, relative named ranges are useful building generic formulas that work wherever they are moved. For example, you can create a generic 'CellAbove' named range like this:. Select cell A2. Control + F3 to open Name Manager. Tab into 'Refers to' section, then type: =A1 CellAbove will now retrieve the value from the cell above wherever it is it used.
Important: make sure the active cell is at the correct location before creating the name. Apply named ranges to existing formulas If you have existing formulas that don't use named ranges, you can ask Excel to apply the named ranges in the formulas for you. Start by selecting the cells that contain formulas you want to update. Then run Formulas Define Names Apply Names. Excel will then replace references that have a corresponding named range with the name itself. You can also apply names with find and replace. Important: Save a backup of your worksheet, and select just the cells you want to change before using find and replace on formulas. Key benefits of named ranges Named ranges make formulas easier to read The biggest single benefit to named ranges is they make formulas easier to read and maintain.
This is because they replace cryptic references with meaningful names. For example, consider this worksheet with data on planets in our solar system. Without named ranges, a VLOOKUP formula to fetch 'Position' from the table is quite cryptic. = (planet,data, 2, 0 ) // position = (planet,data, 3, 0 ) // diameter = (planet,data, 4, 0 ) // satellites At a glance, you can see the only difference in these formulas in the column index.
Named ranges make formulas portable and reusable Named ranges can make it much easier to reuse a formula in a different worksheet. If you define names ahead of time in a worksheet, you can paste in a formula that uses these names and it will 'just work'. This is a great way to quickly get a formula working. For example, this formula counts unique values in a range of numeric data. = ( - ( (data,data ) 0 )) To quickly 'port' this formula to your own worksheet, name a range 'data' and paste the formula into the worksheet.
As long as 'data' contains numeric values, the formula will work straightway. Tip: I recommend that you create the needed range names.first.
in the destination workbook, then copy in the formula as text only (i.e. Don't copy the cell that contains the formula in another worksheet, just copy the text of the formula). This stops Excel from creating names on-the-fly and l ets you to fully control the name creation process.
To copy only formula text, copy text from the formula bar, or copy via another application (i.e. Browser, text editor, etc.). Named ranges can be used for navigation Named ranges are great for quick navigation. Just select the dropdown menu next to the name box, and choose a name. When you release the mouse, the range will be selected.
When a named range exists on another sheet, you'll be taken to that sheet automatically. Named ranges work well with hyperlinks Named ranges make hyperlinks easy. For example, if you name A1 in Sheet1 'home', you can create a hyperlink somewhere else that takes you back there. To use a named range inside the HYPERLINK function, add a pound symbol in front of the named range.
= ( '#home', 'take me home' ) Note: oddly, you can't hyperlink to a table like you can a normal range name. However, you can define a name equal to a table (i.e. =Table1) and hyperlink to that. If anyone knows a way to link directly a table, chime in! Named ranges for data validation Names ranges work well for data validation, since they let you use a logically named reference to validate input with a drop down menu. Below, the range G4:G8 is named 'statuslist', then apply data validation with a List linked like this: The result is a dropdown menu in column E that only allows values in the named range: Dynamic Named Ranges Names ranges are extremely useful when they automatically adjust to new data in a worksheet.
A range set up this way is is referred to as a 'dynamic named range'. There are two ways to make a range dynamic: formulas and tables.
Dynamic named range with a Table A Table is the easiest way to create a dynamic named range. Select any cell in the data, then use the shortcut Control + T: When you create an Excel Table, a name is automatically created (e.g.
Table1), but you can rename the table as you like. Once you have created a table, it will expand automatically when data is added. Dynamic named range with a formula You can also create a dynamic named range with formulas, using functions like OFFSET and INDEX. Although these formulas are moderately complex, they provide a lightweight solution when you don't want to use a table. The links below provide examples with full explanations:. Deleting named ranges Note: If you have formulas that refer to named ranges, you may want to update the formulas first before removing names.
![How To Clear Filter Range Name In Excel For Mac 2010 How To Clear Filter Range Name In Excel For Mac 2010](/uploads/1/2/5/4/125465869/258879045.png)
Otherwise, you'll see #NAME? Errors in formulas that still refer to deleted names. Always save your worksheet before removing named ranges in case you have have problems and need to revert to the original. Named ranges adjust when deleting and inserting cells When you delete.part. of a named range, or if insert cells/rows/columns inside a named range, the range reference will adjust accordingly and remain valid.
However, if you delete all of the cells that enclose a named range, the named range will lose the reference and display a #REF error. For example, if I name A1 'test', then delete column A, the name manager will show 'refers to' as.
Delete names with Name Manager To remove named ranges from a workbook manually, open the name manager, select a range, and click the Delete button. If you want to remove more than one name at the same time, you can Shift + Click or Ctrl + Click to select multiple names, then delete in one step.
Delete names with errors If you have a lot of names with reference errors, you can use the filter button in the name manager to filter on names with errors: Then shift+click to select all names and delete. Named ranges and Scope Named ranges in Excel have something called 'scope', which determines whether a named range is local to a given worksheet, or global across the entire workbook. Global names have a scope of 'workbook', and local names have a scope equal to the sheet name they exist on. For example, the scope for a local name might be 'Sheet2'. The purpose of scope Named ranges with a global scope are useful when you want all sheets in a workbook to have access to certain data, variables, or constants.
For example, you might use a global named range a tax rate assumption used in several several worksheets. Local scope Local scope means a name is works only on the sheet it was created on. This means you can have multiple worksheets in the same workbook that all use the same name.
For example, perhaps you have a workbook with monthly tracking sheets (one per month) that use named ranges with the same name, all scoped locally. This might allow you to reuse the same formulas in different sheets. The local scope allows the names in each sheet to work correctly without colliding with names in the other sheets. To refer to a name with a local scope, you can prefix the sheet name to the range name. =lastupdate Global names must be unique within a workbook. Local scope Locally scoped named ranges make sense for worksheets that use named ranges for local assumptions only.
For example, perhaps you have a workbook with monthly tracking sheets (one per month) that use named ranges with the same name, all scoped locally. The local scope allows the names in each sheet to work correctly without colliding with names in the other sheets. Managing named range scope By default, new names created with the namebox are global, and you can't edit the scope of a named range after it's created. However, as a workaround, you can delete and recreate a name with the desired scope. If you want to change several names at once from global to local, sometimes it makes sense to copy the sheet that contains the names.
When you duplicate a worksheet that contains named ranges, Excel copies the named ranges to the second sheet, changing the scope to local at the same time. After you have the second sheet with locally scoped names, you can optionally delete the first sheet.
Jan Karel Pieterse and have developed a utility called the Name Manager that provides many useful operations for named ranges.