This blog post is brought to you by Chad Rothschiller a Program Manager on the Excel team.
As part of our planning work at the beginning of the Office 2013 project, we (the Excel team) participated in several customer visits. We went in small groups to someone’s work environment (whether that was a large corporation, a small business, or even a home office), watched them use Excel, and talked with them a bit about what they were doing. Of course several patterns and themes were observed, but here are a few that caught my interest and really resonated with me and with several others on the team:
· Many people aren’t familiar with the variety of data analysis features that Excel has to offer
· Many people are hesitant to create / insert new features, even a chart. Some people feared they wouldn’t be able to change it back if they didn’t like the result
· Many people are simply unwilling to navigate away from the home tab to go looking for interesting features & capabilities
These are the main motivations behind Excel 2013’s Quick Analysis feature. We set out to create a dynamic interface that allows fear-free exploration of Excel’s analytical capabilities. The Quick Analysis galleries are dynamic: what appears in them changes depending on the type of data you’ve selected. Live preview makes it quick and painless to see what’s going to happen to the workbook before committing to a change.
Feel free to open the workbook and come along as we take a closer look at how it all works.
Quick Analysis Quick Analysis helps you to visualize very quickly how you can display your data in a Chart, a Table, Conditional Formatting, Sparklines, But it is also possible to remove this option (end of the post). Excel for Mac Discover contextual tools You can display additional ribbon commands by selecting specific items in your workbook. For example, select a chart to reveal the Chart Design and Format ribbon tabs, or select a sparkline to reveal the Design tab. Insert functions, build formulas. Quick analysis tool Please have a quick analysis tool in mac. I am doing an Excel based course for my degree and there is a whole section for quick analysis but i dont have it on mac. Quick analysis for Excel on mac Hi all, I'm trying to find the QUICK ANALYSIS tool in my Excel on mac version 15.14. I can't seem to find it. I have the data analysis tool but this in NOT what i'm looking for. I want to find the Quick analysis tool which is supposed to appear when you select rows of cells. Thanks in advance.
Getting Started: Select a Range of Data
The way to bring up the Quick Analysis interface is to select a range of data, and then click the little button that appears near the bottom right of the selection:
Clicking the button brings up a gallery of options to explore:
By default the Conditional Formatting gallery comes up, showing just a handful of choices, but there are other galleries to explore as well. The Charts, Totals, Tables, and Sparklines tabs in this callout each show a handful of choices from those respective categories to explore.
Conditional Formatting
Looking at my sample data, I might first be interested in taking a closer look just at the numbers, so I select G4:I20, and click the button. Hovering my mouse over the various icons in the gallery gives me a live preview of what choosing that option will do. Below are some examples with Data Bars and Color Scale. If you like what you see, click the button to add that to your selection, otherwise if you just move your mouse away the live preview will disappear.
(Data Bars)
(Color Scale)
Dynamic Conditional Formatting Gallery
In the example above I’ve selected numbers, so it makes sense that I’m seeing Conditional Formatting options that work well with numeric values. If I select all Text or all Date values, the items in the Conditional Formatting gallery change to work better with those types of data:
(Text values in the “Territory” column selected, showing a live preview of the “Text Contains” Conditional Formatting rule)
(Date values in the “Dates” column selected, showing a live preview of the “Last Week” Conditional Formatting rule)
Charts
Now let’s say I want show my data as a chart, but I’m not yet sure exactly what I want. I’ll select the entire range of data from B3:I20 (Names column all the way to Sales 2011 column), and take a look at the CHARTS gallery in the Quick Analysis callout. This gallery is dynamic as well – – in the background Excel is doing some light weight analysis of the data in the selection to determine some good chart recommendations to get started.
At first glance it seems that the Clustered Column chart is being recommended over and over again:
Looking through the live previews though shows me that these are different Clustered Column PivotChart recommendations. The indicator that a PivotChart (instead of a regular chart) is being recommended is the little PivotTable icon in the upper right corner of the chart preview:
Incidentally, the choices you see here are a subset of the recommendations you get when you navigate to the Insert ribbon tab and choose “Recommended Charts” (the maximum number of recommendations in the Quick Analysis gallery is 5, whereas clicking the ribbon button doesn’t limit the recommendations to 5). Also, if the recommendation engine has fewer than 5 recommended charts, then fewer recommendations will appear in the Quick Analysis Charts gallery.
Totals
Quick Analysis Tool Excel Youtube
To explore the Totals gallery in the Quick Analysis callout, let’s first select all the numbers for Sales 2009, 2010, and 2011 (range G4:I20). Looking at the icons in that gallery, I’m thinking that this part of the feature will put totals either at the bottom or to the right of my selection (OK, since I helped design those icons, I’ve got an unfair advantage J):
Live previews confirm my guess:
This particular gallery has more choices, with right/left scrolling arrows to see the additional options. Hovering over the 2nd “Sum” button shows a live preview of the totals placed to the right instead of at the bottom:
This gallery offers “Percent of Total” as well as “Running Total” at the click of a button – – which is a first for Excel.
Tables
Exploring the Tables gallery quickly shows me that this is the place to get recommendations about how to summarize my data in a PivotTable. This gallery is dynamic as well – – in the background, Excel is doing more lightweight analysis of the data in the selection to determine good recommendations to get started. Even if I don’t know what the word PivotTable means, I can see from the live previews that it’s putting subtotals into a nicely formatted layout:
Again, just like recommended charts, this is a subset of the options you get when you navigate to the Insert ribbon tab and choose “Recommended PivotTables”. Also, if the recommendation engine has fewer than 5 recommendations, then fewer recommendations will appear in this gallery. As an extreme example, if the engine doesn’t recommend any PivotTables, there won’t be any recommendations in the gallery; only the option to insert a blank PivotTable. This can happen if your data contains only unique values, column-wise, since in that case there isn’t anything to consolidate and subtotal.
I’m really excited that you don’t have to know the word “PivotTable” in order to get good summary information about your data, and you get live previews of a good number of options to choose from.
Sparklines
Finally, let’s take a quick glance at the Sparklines gallery. I’ll select the numbers again, and hover over the “Column” icon to see a live preview of where the Sparklines will be inserted, and get a better understanding of what Sparklines even means:
Wrap Up
One aspect of this feature that was challenging for us to dial in exactly right was how and where the Quick Analysis button would initially show up: On the one hand we want people to see it and click it. We don’t want to be too subtle. On the other hand we don’t want to annoy people with a button always appearing and following them around as they work in Excel – – that would be a distraction. We tried to keep it toned down by having the button fade/disappear as the mouse moved away from it.
What do you think:
· Did we get the balance right?
· Do you think that people will notice it?
· Did you notice it?
· If so, did it draw you in to explore the feature?
· Or is it simply distracting? (you can turn it off in Excel / Options)
Thanks everyone for reading and providing comments!
–Chad
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. Here are 10 of the best.
(Note: Keyboard shortcuts will work for the 2016 versions of Excel, including Mac; those were the versions tested. And many of the query options in Excel 2016's data tab come from the Power Query add-in for Excel 2010 and 2013. 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. Use a shortcut to create a table
Tables are among the most useful features in Excel for data that is in contiguous columns and rows. 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. 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. Your future self will thank you if you need to access that information from a new, more complex workbook.
2. Add a summary row to a table
You 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. This can be quite handy if you're doing a lot of data exploration.
Note that you'll need to create a total row for each column individually; creating a sum for one column won't automatically generate sums for the rest of your table (since not all columns may have the same type of data -- a sum for a column of dates wouldn't make much sense, for example).
![2017 2017](/uploads/1/2/6/2/126293446/655235216.jpg)
![Quick analysis tool for excel on a mac Quick analysis tool for excel on a mac](/uploads/1/2/6/2/126293446/174611344.jpg)
3. Easily select columns and rows
If your data is in a table and you need to refer to an entire column in a new formula, click on the column name. 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. 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. Note that if your data isn't in a table, these selections go beyond available data and include any empty cells beyond. 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). This can be handy if your data column is quite long.
4. Filter table data with slicers
Excel tables offer drop-down arrows next to each column header for easy sorting, searching and filtering. However, trying to filter data with that small drop-down when you've got a large number of items can be somewhat cumbersome. Several of the presenters at the Data Insights Summit suggest using slicers instead.
Quick Analysis Tool On Excel For Mac
'Anybody who sends you a pivot table without slicers, you should teach them slicers in 30 seconds. People love slicers,' said Indiana University professor Wayne Winston, who also advises Dallas Mavericks owner Mark Cuban on basketball stats.
But while slicers were originally developed for pivot tables, they now work on 'regular' tables as well (and have since Excel 2013 on Windows). 'This is actually more useful,' Winston argued. (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. 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. 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. To clear all filters, there's a clear button at the top right of the slicer.
5. Create a summary cell that changes when you filter a table
If you create a cell outside a table that summarizes data within a table -- the sum of a column, for example -- and you'd like that cell to display an updated sum if you filter the table by something, a basic SUM formula won't work.
Instead of simply using SUM in that cell, use the AGGREGATE function within your cell, and then your cell can be linked to your table filters.
AGGREGATE requires three arguments: A function number, a desired option number and the range of cells you want to operate on. Type
=AGGREGATE(
in Excel for Windows and you'll see the available functions and options; in Excel for Mac, you'll have to click on the AGGREGATE help function in order to see available function and option numbers.SUM is function number 9; ignore hidden rows is option 5. So, a cell with the following code:
=AGGREGATE(9,5,Table1[Expenditures])
gives you the sum of all visible rows only. If a filter changes which rows are visible, your sum will change accordingly.
6. Sort data in a pivot table
Sometimes you'd like to sort data by a specific column in a pivot table -- just as with a regular table. But unlike regular tables, pivot tables don't have dropdown menus on each column offering the ability to sort. However, if you choose the lone dropdown arrow on the first column, you'll get a menu allowing you to sort by any column.
7. 'Unpivot' data
Some call this reshaping data from 'wide' to 'long'. In the database world, it's known as 'fold': Taking data from individual columns and moving them into rows. 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.
Pangu 9 iOS 9 – 9.0.2 jailbreak tool for Mac OS X has been released and can be downloaded from here. Up until now, Pangu 9 had been a Windows-only jailbreak tool, meaning that OS X users were. The jailbreak will not work with iOS 9.1 (and iOS 9.2 betas) as Apple has patched the exploits used in this jailbreak tool in iOS 9.1 firmware. The good news though is that Apple continues to sign iOS 9.0.2, which means you can still upgrade or downgrade to that firmware and jailbreak using Pangu 9. Download pangu 9 jailbreak tool for mac os x. Download Pangu Jailbreak for iOS 9.0 – iOS 9.0.2 Pangu Jailbreak tool for iOS 9 ( Pangu 9 ) is known ad “ Fuxi Qin ” and it was released on 2015/10/14 (only for Windows), but in the post we are also describing a method to Jailbreak iOS 9 using Pangu on Mac. Pangu for iOS 9 – 9.0.2 version 1.0.0 is the first release for Mac OS X. It is available for download now and works exactly like the Windows version. If you’ve been waiting for a Mac version of the iOS 9 jailbreak tool, then this is it.
To unpivot columns, you need to use the Query Editor in Excel 2016. 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.
That will create two new columns at the right of your spreadsheet, Attribute and Value, with the columns you unpivoted. 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. 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.
The Microsoft Office website has more information on unpivoting.
8. Make multiple pivot tables for one column of categories
If you have a pivot table and add a filter for one column that contains categories, you can generate copies of that pivot table, one for each category in your filter, by going to Analyze > Options > Show Report Filter Pages and then selecting the filter you want. 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. Look up data with INDEX MATCH
While 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. 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. Create a formula using this format:
=INDEX(ColumnToSearchForValue, MATCH(CellWithLookupKey, ColumnToSearchForLookupKey, 0)
A sample might look like:
=INDEX(B2:B73, MATCH(D2, A2:A73, 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. 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.
Jun 22, 2015 Windows 10 is able to run on all modern Mac hardware in a dual boot environment thanks to Boot Camp. If you’re aiming to run Windows alongside OS X on the same Mac, you’ll want to create a bootable Windows 10 installer drive out of a USB drive, which can be done quickly from OS X and the Boot Camp Assistant tool. Go to the Microsoft official website and download the free ISO file for Windows 10. Download Windows 10 ISO file to create bootable USB from iso on Mac. Note: Ascertain that it is Windows 10 version 1511 since older versions don’t let you use Windows 7 or 8 keys to activate. Windows 10 bootable creator tool for mac.
For example, you could pick the 6th item in column B with:
=INDEX(B2:B19, 6)
.You'd be using the following format:
=INDEX(ColumnOrRowToSearch, ItemNumberInThatColumnOrRow)
However, using INDEX alone isn't much help if you want to find a value based on some condition in another column. That is, you don't want the 6th item in your Price column B; you want the item in your Price column that matches something in column A, such as a certain computer model.
That's where MATCH comes in. MATCH searches for a value in a range of cells and returns the location of what's matched, using the following format:
=MATCH(SearchValue,RangeToSearch,MatchType)
(Match type can either be 0 for exactly equal, 1 for largest value less than or equal to what you're searching for or -1 for the smallest value that is greater than or equal to your lookup value.)
So, if you wanted to find the location of a cell in column B that was exactly 999, you could use:
=MATCH(999, B2:B79, 0)
.And, so the combination: MATCH, looking for a specific value based on a search term, returns a cell location; and INDEX needs a location as its second formula argument.
10. Watch a formula be evaluated step by step (for Windows only)
Have a complicated formula? If you want to see how it gets evaluated, go to Formulas > Evaluate Formula to see the calculations run step by step.
11. Import and refresh data from the Web into Excel
This works best when you've got well-formatted HTML tables on a Web page; with more free-form text (or even poorly formatted tables), you'll need to do a fair amount of additional editing to get your data into a form you can analyze.
With that warning in mind, if you want to pull an HTML table from the Web into Excel, head to the Data tab on Excel for Windows and select: New Query > From Other Sources > From Web
Enter the URL of the appropriate Web page. Excel will look for and list available HTML tables on that page. Click on a table to see a preview; when you find the one you want, click Load.
Why not just copy and paste a well-formatted HTML table into Excel? If the data updates frequently, you can easily refresh it by right-clicking in the table and selecting Refresh instead of having to copy and paste new data.
For more on the conference, check out the Microsoft Data Insights videos on YouTube.