How to use Analyze Data to get quick information about your data in Excel

2 years ago 345

Excel's Analyze Data has been astir for a while, but galore users are inactive unaware of its powerfulness and flexibility.

Most of america usage Microsoft Excel to analyse data—what's the total, who's performing best, which invoices are outstanding and truthful on. That's what Excel does–it turns information into accusation we tin use. Now that Analyze Data is available, on-the-fly accusation is conscionable a fewer clicks away.

In this article, we'll reappraisal Excel's Analyze Data feature, however to usage it astir efficiently, and past enactment done a fewer examples that volition show charts and adjacent PivotTables that analyse your data, astir instantly. You tin adjacent inquire questions utilizing earthy language!

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

I'm utilizing Microsoft 365 connected a Windows 10 64-bit system. Currently, lone Microsoft 365 and Excel for the Web enactment Analyze Data. You tin enactment with your ain information oregon download the objection .xlsx file.

What is Analyze Data successful Microsoft Excel?

Excel's Analyze Data diagnostic offers a batch of accusation with a speedy click. In a nutshell, you click wrong the information and past click Analyze Data successful the Analysis radical connected the Home tab. Excel opens the Analyze Data pane, which volition show respective antithetic ocular interpretations of the data, specified arsenic totals, averages, rank, inclination and truthful on. If you similar something, you tin adhd it to your expanse (or dashboard sheet). This diagnostic volition besides show a fewer earthy connection questions that you mightiness privation to pursue. Or you tin participate a question yourself.

This diagnostic goes good beyond the Quick Analysis instrumentality that was added a fewer years ago. If you're not acquainted with this feature, prime 2 oregon much cells anyplace successful the information acceptable and past click the instrumentality astatine the bottom-right of the selection. In the resulting dialog shown successful Figure A, click immoderate of the imaginable valuation possibilities. It's rather simple, but it isn't arsenic almighty arsenic Analyze Data.

Figure A

To comparison Quick Analysis to Analyze Data, marque the aforesaid enactment and past click Analyze Data connected the Home tab. As you tin spot successful Figure B, this diagnostic goes good beyond Quick Analysis and possibly the astir absorbing enactment is that you tin participate a earthy connection question. (This enactment is presently disposable successful English; different languages volition beryllium supported gradually.)

Figure B

You can't spot each the disposable charts successful the figure, but if you're pursuing along, you'll find astatine slightest 4 charts that analyse the selected information successful the astir logical ways. Because the enactment is simply a subset, Analyze Data refers to the 3 selected columns arsenic Field1, Field2, and Field3. The archetypal 2 charts supply a hint astir the underlying data:

The 2 largest values are reasonably close.
June has nary income for the Northwest and Central regions (for the selected subset).
James has a substantially higher full than June (for the selected subset).

However, without accepted information labels and legends, immoderate of the charts mightiness request a spot of mentation connected your part. Fortunately, you tin insert a illustration successful a expanse and modify it arsenic you would immoderate different chart. Doing truthful mightiness beryllium quicker and easier than starting from scratch.

Now that you've had a speedy look astatine the feature, determination are respective guidelines that erstwhile followed, volition instrumentality the champion results from this feature:

Format your information arsenic a Table object. (Click anyplace wrong the information scope and property Ctrl + T.)
Header substance should beryllium a azygous enactment of unsocial labels for each column. (Avoid merged cells.)
Analyze Data doesn't enactment datasets implicit 1.5 cardinal cells. That won't beryllium an contented for astir of us, but if you're that one, you request to know.
Analyze Data doesn't construe dates formatted arsenic strings. You indispensable person these strings into valid Excel information types.

Now, let's spot what the diagnostic does with the afloat information set.

How to usage Analyze Data successful Excel

Now that you cognize a spot astir the feature, let's enactment it to work. Select the full Table and past click Analyze Data. Figure C shows the archetypal fewer results; determination are 3 questions astatine the apical and 4 charts. The archetypal illustration shows the full committee and worth for each region; the 2nd breaks down the values by personnel. The frequence illustration astir apt isn't adjuvant but the fourth, interestingly, shows a correlation betwixt the values and the commission, arsenic it should due to the fact that each committee is 20% of the corresponding value.

Figure C

At the precise top, determination are 3 earthy connection questions based connected the data. Click wrong the power and you'll spot that determination are really respective questions. Click immoderate of them to spot the due visual. For example, Figure D shows commissions by region. This database of questions updates on the mode truthful it's casual to suffer a question.

Figure D

Not lone does the diagnostic contiguous the question, but it determines the astir due visual. For instance, the ocular for this question is simply a barroom chart, displaying commissions successful descending order. If that's not precisely what you were hoping for, ever cheque adjacent the bottommost for alternatives.

To instrumentality to the archetypal pane, click the backmost arrow to the near of the question. In that pane, you'll spot the archetypal visuals. The PivotTable shows the full commissions and worth for each region. The pursuing barroom illustration sums values by unit … and truthful on. Depending connected the information set, you mightiness extremity up with respective charts.

Now, let's instrumentality to the apical to look astatine the earthy connection question option. There are much questions, conscionable click wrong and you'll that database of 3 questions expand. If the question you privation to inquire isn't successful that list, you tin inquire it yourself. It mightiness instrumentality a portion to get the bent of this enactment due to the fact that it won't ever instrumentality what you expected. For example, inquire the question: How galore dates are there? Figure E shows the close result: determination are 9 chiseled dates. Next, inquire this question: How galore months are there? The reply is close according to Analyze Data, but it isn't the expected answer. If you had a file of months, the diagnostic would instrumentality the expected answer.

Figure E

Let's effort a fewer much tenable questions. Figure F shows the effect of Top 3 commission. You could besides effort Bottom 3 commission, Top 3 region, oregon Top region. The effect of a spot much hard question, Percentage of Northwest, is shown successful Figure G. The northwest portion accounts for astir fractional of each sales.

Figure F

Figure G

We've looked astatine the basics and with a small exploration, you should beryllium capable to enactment this diagnostic to bully use. But there's 1 much enactment you should cognize about. In the archetypal pane, announcement the small cogwheel and the question, Which fields involvement you the most? By clicking it, you tin absorption connected circumstantial data. Figure H shows a tract list. In our information set, removing a file (field) doesn't marque overmuch sense, but that won't ever beryllium the case. The Summarize Value By enactment lets you alteration the setting. Three are available:

Not a Value
Sum
Average

Figure H

The option, Not a Value, shows up erstwhile the information acceptable contains information that isn't summed oregon averaged, specified arsenic dates. You tin benignant by dates, but you wouldn't sum oregon mean them. Analyze Data volition usage these values arsenic enactment labels erstwhile utilized with different fields. When this tract is utilized by itself, Analyze Data returns a unsocial count.

Excel's Analyze Data returns a batch of large accusation considering however casual it is to instrumentality and use. Learning however to inquire questions is the astir challenging feature, but that volition get easier with experience.