#Use Data Filter In Excel 2011 How To Summarize Data

Use Data Filter In Excel 2011 How To Summarize Data

Here are 10 of the best.Plus, learn how to summarize data using core functions and formulas format worksheet elements create bar, column, and line charts create and filter.set of Excel shortcuts for Mac and Windows: Click here to learn more These shortcuts were tested on Excel 2010 for Windows, and Excel 2011 for the Mac. Step 3: Click the drop-down arrow of the column city to view the different names of the cities.Want to get more out of Excel? At Microsoft's inaugural Data Insights Summit last month, several experts offered a slew of suggestions for getting the most out of Excel 2016. The drop-down arrows, shown within the red boxes in the following image, are filters. Step 2: The filters are added to the selected data range. Step 1: Select the data and click filter under the sort and filter drop-down.

So if you've got Power Query on an earlier version of Excel on Windows, a lot of these tips will work for you as well, although they may not work on Excel for Mac.) 1. And many of the query options in Excel 2016's data tab come from the Power Query add-in for Excel 20. If you have trouble with certain shortcuts, make sure that there is not a conflict with another program/application on (Note: Keyboard shortcuts will work for the 2016 versions of Excel, including Mac those were the versions tested.

Add a summary row to a tableYou can add a summary row to a table in the Design ribbon on Windows or the Table ribbon on a Mac by checking 'Total Row.' Although it's called Total Row, you can select from a variety of summary statistics, not just a total sum: count, standard deviation, average and more.While you could certainly insert this information into a spreadsheet manually with a formula, putting the info in a Total Row means it's 'attached' to your table but will stay in the bottom row regardless of how you then might choose to sort your table data. Your future self will thank you if you need to access that information from a new, more complex workbook. In addition, if you make charts from your data, using a table means the chart will automatically update if you add new rows.If you've been creating tables from your data by going to the Excel ribbon, clicking Insert and then Table, there's an easy keyboard shortcut: After first selecting all your data with Ctrl-A (command-shift-spacebar for Mac), turn it into a table with Ctrl-T (command-T on Mac).Bonus tip: Make sure to rename your table to something related to your specific data, instead of leaving the default titles Table1 or Table2. Tables make it easier to sort, filter and visualize, as well as add new rows that maintain the same formatting as the rows above them. Changes you can customize the scrolling rate in Mac OS X for both a Mac trackpad and.Tables are among the most useful features in Excel for data that is in contiguous columns and rows.

For table data, the selections stop at the table's borders.If you want to select an entire column that's not in a table with just the cells that have data in them, put your cursor in a column next to it, hit Ctrl-down arrow, use the right or left arrow key to move to your desired column, and then hit Ctrl-Shift-up (use command instead of Ctrl on a Mac). Note that if your data isn't in a table, these selections go beyond available data and include any empty cells beyond. If your cursor looks like a cross when you do so, you'll get a reference to just that lone cell, not to the whole column.Whether or not your data is in a table, there are a couple of handy selection shortcuts you can use: Shift+spacebar selects an entire row and Ctrl+spacebar (or control+spacebar for a Mac) selects an entire column. That will give a reference to the full column by name - useful if you later add more rows to the table, because you won't have to readjust a more specific reference such as B2:B194.Note: It's important to make sure your cursor looks like a down arrow before you click on the column name. Easily select columns and rowsIf your data is in a table and you need to refer to an entire column in a new formula, click on the column name.

Several of the presenters at the Data Insights Summit suggest using slicers instead.'Anybody who sends you a pivot table without slicers, you should teach them slicers in 30 seconds. However, trying to filter data with that small drop-down when you've got a large number of items can be somewhat cumbersome. Filter table data with slicersExcel tables offer drop-down arrows next to each column header for easy sorting, searching and filtering.

To clear all filters, there's a clear button at the top right of the slicer. You can add columns to the slicer layout within the slicer options on the Ribbon.If you want to filter by more than one item in a slicer, Ctrl-click. But if you have a long, narrow spreadsheet with lots of space to the right of your data, you can resize a slicer to be considerably wider than the default. (Slicers are available for pivot tables but not regular tables in Excel for Mac 2016.)To add a slicer to a table, with your cursor already somewhere in the table, head to the Design ribbon, select Insert Slicer and then choose which column(s) you'd like to filter.The slicer will show up on your worksheet, appearing one column wide with just a few items showing. 'This is actually more useful,' Winston argued. But while slicers were originally developed for pivot tables, they now work on 'regular' tables as well (and have since Excel 2013 on Windows).

'Unpivot' dataSome call this reshaping data from 'wide' to 'long'. However, if you choose the lone dropdown arrow on the first column, you'll get a menu allowing you to sort by any column. But unlike regular tables, pivot tables don't have dropdown menus on each column offering the ability to sort.

If you try to close without saving, you'll be asked whether you want to keep your changes say Yes and they'll be saved on a new worksheet.Unpivoting data turns a wide table into a longer one, combining multiple columns into two: attribute (category) and value.The Microsoft Office website has more information on unpivoting. You can rename those columns to something that makes more sense, such as 'Product' and 'Price' or 'Quarter' and 'Revenue.'To save your work, select File > Close & Load (to the default destination) or File > Close & Load To in order to be asked where you'd like to save your results. Access the Query Editor via the Data ribbon: In the Get & Transform section, choose From Table.Once the Query Editor comes up (if your data isn't already in a table, you'll be asked to confirm a data range first), select the columns you want to unpivot, click on the Transform tab and chose Unpivot Columns.Excel's Query Editor provides users with the option to unpivot columns.That will create two new columns at the right of your spreadsheet, Attribute and Value, with the columns you unpivoted. Basically, it's the opposite of creating a pivot table - in a pivot table, you pull categories within one column up into their own columns.To unpivot columns, you need to use the Query Editor in Excel 2016.

You first give it a range of cells, either within a single column or a single row, and then tell it the specific number of the cell you want. Create a formula using this format:=INDEX(ColumnToSearchForValue, MATCH(CellWithLookupKey, ColumnToSearchForLookupKey, 0)This is how/why INDEX MATCH works (if you don't need to know, skip to the next tip): INDEX selects a specific cell by numerical location. Here's how to use them.Let's say you have a lookup table where column A has computer model names, column B has price information, and column D also the name of a computer model where you want to add price info. Look up data with INDEX MATCHWhile VLOOKUP is a popular way to find data in one Excel table and insert it into another, INDEX combined with MATCH can be more powerful and flexible. This can be handier than having to click through each category in your filter manually.(On Excel 2016 for Mac, go to the PivotTable Analyze tab on the Ribbon and choose Options > Show Report Filter Pages.) 9.

Use Data Filter In Excel 2011 For Mac