How to Turn on Drawing Tools in Excel 2019

A pivot table in Microsoft Excel reorganizes data quick into a meaningful composition, and IT's sluttish to use!

Illustration: Lisa Hornung

Earlier Microsoft Surpass added the PivotTable tool, you had to use summarizing functions and features to coordinate and analyze your information. It was ofttimes embarrassing, surgery worse, elusive work. If you're calm down working that way, you're functioning too hard. Try Excel's PivotTable tool to turn your raw data into substantive reports. If it sounds too complex, information technology rattling isn't. It's a flexible tool that wish help you comprise more than amentiferous.

I'm using Microsoft 365, but you can use of goods and services before versions. You can download the .xlsx demonstration file or work with your own data. The browser edition fully supports the PivotTable tool.

LEARN Thomas More: Microsoft 365 Consumer pricing and features

What is a pivot table?

Excel's PivotTable tool is easy to wont, just if you're not familiar, you might be stymied a bit, at first. A pivot man set back is an targe that allows you to analyze data by reorganizing and summarizing a specific range of information for reporting purposes. The presentation is other from the source data and doesn't change the source data in whatever way. In Excel, PivotTable refers to the tool that helps you make over a pivot table. It's a great creature whether you have titanic or dwarfish amounts of data because you canpivot the data to get the info you pauperization quickly in a pregnant layout. Specifically, you can reorganize by sorting, grouping, summing, tally, so on.

Earlier we continue, countenance's discuss the terms information and information. Data refers to the values in the two-dimensional column and row arrange. Information is what you glean from that data in a significant right smart, for instance, which sales personnel met their quotas or which student in each sort has the highest GPA. Using a pivot put over, you might show unit of time gross sales by products, sales regions, or even personnel. If you'Ra a teacher, you might show bookman averages by class operating theater subject. I keep a spurting total of income past clients using a pivot table. In a two-dimensional spreadsheet, you can't easily discern meaningful information.

Now, let's start pivoting!

SEE: Windows 10 logo key shortcuts to bucket along your work (free PDF)  (TechRepublic)

Step one: Identify the author data

Before you can produce a pivot table, you must identify the data you want to analyze. The data should not contain blank rows OR columns, subtotals, and should be in planar format. Frame A shows a acerose set of sales data by escort, force, and region. Right away, lease's begin the process of displaying sales totals by personnel using a swivel shelve. Click anywhere at heart the data set to begin and so do the following:

1. Cluck the Insert tab and then click PivotTable in the Tables group.
2. In the resulting dialog, make a point the range is correct (information technology e'er is only this is a good way to catch a blank wrangle operating theater chromatography column off CRT screen).
3. Specify whether you want the inexperient pivot put of in the existent worksheet surgery in a recent worksheet. We'll work in the alive worksheet so we can see both the data and the pivot postpone. To DO so, flick the Location control's arrow, which will reduce the dialog to only that control. Then, click the anchor cell for the new fles (in the sheet), and and so return to the dialog past clicking the arrow. Eastern Samoa you can see in Figure A, I clicked F3 as the keystone cadre.
4. Click OK.

Figure A

 The PivotTable tool around will help US take apart this simple information set.

At this point, you have a swivel table frame (Anatomy B), but it's empty-bellied. In the next section, you'll build the pivot table using the frame options.

Figure B

The PivotTable instrument inserts a vacuous frame.

Step two: Identify the fields and values

With the frame in place, you're fit to position the columns, rows, and values. Permit's consider what we want and how to achieve information technology earlier we start dragging Fields around: We want to display sales totals.

The PivotTable Field pane offers the settings you need to fill the frame. If you don't see it, sink in internal the frame. If you still don't see information technology, word-perfect-click the frame and choose Show Field Tilt from the ensuant submenu. Once the Ze is gettable, drag Personnel from the cover pane to the Rows field and then drag Total to the Values theatre. The PivotTable tool bequeath default to a substance for that subject area. (You can change it by clicking the down pointer and choosing Apprais Field Settings.)

As is, the PivotTable is a bit bullate (Figure C). As luck would have it, you prat add formats and change field names to make over a better written report. First, click F3 and replace Course Labels with Staff office. Then, click G3 and replace Kernel of Amount with Total Sales. Finally, formatting the values in the second pillar as follows:

1. Right-click the Big Total coping cell.
2. Choose Number Format from the resulting submenu.
3. Choose Currency in the Category list.
4. Click OK.

Figure C

The nonpayment settings don't include substantive field name calling and format.

The subtle changes shown in Figure D complete the picture. With few clicks and drags, you now know the summate sales for every last personnel. Congratulations! You just created your first pivot table. Now, let's review a couple of ways to change the information displayed away the same pivot table.

Image D

  This simple pivot table takes little attempt to create, but the returns are huge.

Let's practice

Creating that table was easy; modifying information technology can complement or completely change the information the table displays. Human body E shows the result of dragging the Date field to the Columns listing. The final result is a subtotal for personnel by date--with only one change. The ending total (Extraordinary Total) is the selfsame as it was in the original table.

Figure E

You toilet add subtotals quickly.

Now Lashkar-e-Toiba's really mix things up by effortful Region to the Rows field. Doing so displays even Thomas More details about the source data and those day of the month subtotals are like a sho split 'tween regions, American Samoa shown in Chassis F. Notice that the grand totals for the person and the region all the same equate the date subtotals. Complete we'rhenium doing is adding more information by displaying more than details.

Figure F

Tally more details.

Lashkar-e-Toiba's try one many change: Lashkar-e-Tayyiba's display the date totals by quarters. To do thus, click whatever date header cellular phone (G4 through J4) and choose Chemical group from the resulting hierarchical menu. The group options are very flexible and are worth exploring subsequent. For now, deselect Months in the By list, select Quarters, and click OK (Figure G). All of the dates downfall in the second quarter, as you can see in Figure H. As you tally more dates, a quick refresh of the pivot table will show initial-, second-, ordinal-, and fourth-quarter dates automatically.

Figure G

 Display the sales amounts in canton subtotals.

Figure H

  Grouping is just unitary of the many ways you can organize your source information.

There's much Thomas More

You've seen the tip of the iceberg, and the only room to learn more is to start exploring. Add and delete fields; hang back rows to columns and column to rows, check out the opposite field and grouping settings. Don't be afraid if you create a mess! That's part of the learning process. I can't tell you how galore times I've thought, "That's non what I meant to do," when working with a pivot man remit. Once you've got the hang up of it, you'll uncovering lots of reasons to turn your information into meaningful information via the PivotTable tool.

Also see

• What's in Windows 10 19H2 for enterprises? (TechRepublic)
• Windows 10: A cheat sheet (TechRepublic)
• What to practise if you're still functioning Windows 7 (available PDF) (TechRepublic)
• Power checklist: Securing Windows Waiter 2022 (TechRepublic Premium)
• Microsoft delivers starting time Windows 10 Latched Ring material body from its newly development branch (ZDNet)
• 6 simple certificate changes all Windows 10 users need to make (CNET)
• Get more must-read Microsoft tips and news (TechRepublic on Flipboard)

How to Turn on Drawing Tools in Excel 2019

Source: https://www.techrepublic.com/article/how-to-use-excels-pivottable-tool-to-turn-data-into-meaningful-information/