Insert new Pivot Tables in Excel Online
Published Mar 04 2018 04:16 AM 38.4K Views
Microsoft

We are excited to announce that it is now possible to insert new Pivot Tables in Excel Online.

This was one of the top requests from our community and we have now rolled it out for all of our Office Online customers.

To learn more about Pivot Tables please use this overview article and our new Pivot Table Tutorial.

 

Before you get started

  • Your data should be organized in a tabular format. Ideally, you can use an Excel table (select the grid data and use Format as Table in the HOME tab). Tables are a great source of data for Pivot Tables because rows added to the “source” Table (later on) will be automatically included in the Pivot Table once you refresh it.
  • Data types in columns should be the same. For example, you shouldn't mix dates and text in the same column. Also, you’ll get the best results if the “source” data doesn’t have any blank rows or columns.

 

Select the “source” data you want to analyze

Just stand on the data. You don’t even have to select it all. If you do select a region, your selection will be respected.

image002.png

 

Insert Pivot Table

Go to INSERT tab and press PivotTable:

image003.png 

The Create PivotTable dialog will open, and unless you selected a region, the whole Table/Range will automatically be selected as the data you want to analyze:

 image004.png

At this point, you can just click OK, and the new Pivot Table will get added to a new worksheet, just like in Excel Desktop application.

 

You also have the option to select an alternative location - press Existing Worksheet and then click the desired location:

image005_2.jpg

 

Inserted it. Now what?

Once you insert a Pivot Table, you will see something like this on your screen:

image006_2.jpg

As enigmatic as it might look, this actually means you are all set to go and do your analysis!

The yellow outline (above) is the placeholder for your new Pivot Table – that’s where you’ll see your newly formed Pivot Table, once you define it.

The green outline (above) is where you define how your new Pivot Table will look like.

 

To define the look of your new Pivot Table, all you need to do is drag PivotTable Fields into the four areas, as outlined below. And if you want to remove some field you dragged in, just drag it out and drop – it’ll go away.

image009.png

 

Pitfalls & Limitations

Excel Online doesn’t support some of the less common capabilities of Excel desktop application, as outlined below:

image010.png 

 

All other constraints of the feature are the same as in Excel desktop application.

For example, you cannot add a pivot table to a location that is too close to an existing table:

image011.png

 

If you try to do that, you’ll get the following error message:

image012.png

 

Another common pitfall is to try and use a piece of data that isn’t suitable for pivoting.

If you try to create a Pivot Table on top of such invalid “source” data, you’ll get an error.

The “source” data is considered invalid in either one of these cases:

 

● Your “source” data points to an empty range (so, there is nothing to pivot)

image013_2.jpg               

● Your “source” data has a column without a header (so, there is no way to relate to it in a PivotTable)

image014_2.jpg

 Your “source” data points to a single row (so, there is nothing to pivot)

 image015_3.jpg

 

10 Comments
Copper Contributor

That's good news but when will these pivot tables in Excel online finally support refresh on SSAS Tabular sources? A refresh request -despite information that it is allegedly possible on MS communications- still returns errors. We went through 4 support cases with Microsoft and even your employees don't seem to be 100% sure if this should be supported and couldn't get it to work either.

Steel Contributor

Fantastic work Excel Online team! This will help bridge the gap for those like myself that want to use the web applications as much as possible and try to limit use of the thick clients. Keep it up!

Brass Contributor

really good job)))!

Copper Contributor

Awesome guide! So, talking excel with my friends and classmates (I am attending an advanced Office course) I shared this article with them and we agreed that we find excel to be the hardest one of the whole office "bundle" by far. Maybe not so complex when you are supposed to create some simple tables with info and charts but when the time comes to shine with something more complex I can't only rely on my tutor because you can't figure out everything in one listening. Let's say you are trying to do this: https://view.ly/v/ZhSNq43MwrAK . The guy who uploaded that video calls it a magic trick. Let me be honest, it seemed harder than a magic trick to me lol but constant practice makes you feel like you are getting the hang of it and feel like a pro. And when it comes to formulas and stuff? Ugh, don't get me started I think that I'm gonna fail most of my exams lol. Please share some more articles like this one! Thanks!

Copper Contributor

This is great!

BUT me and my fieldforce users in multinational company would appreciate more to have this feature directly on their iPads within Excel for iOS.

Thank you.

Copper Contributor

KINDLY HELP US FOR MAKE A FORMULA FOR TOTAL HOURS THAT FALL BETWEEN SPECIFIC TWO TIMES

CONDITION IS "Night Hours COUNTED FROM 20:00 - 04:00 THEN"

HOW TO CALCULATE NIGHT HOURS IF THERE IS AVAILABLE ONLY " TIME IN" AND "TIME OUT " 

EXAMPLE :

TIME IN = 19:30 

TIME OUT = 06:00

NIGHT HOURS = ? ( FORMULA )

@BRIJESH_KUMAR_KUSHWAHA , I'd recommend to start new conversation from this page https://techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral with your question. And please don't use uppercase for entire text, chances to receive an answer will be much lower.

Copper Contributor

I am looking assistance for Pivot Table grouping.   Sales grouping is in USD.  However when trying to use accounting format it does not show up.  Any way around?  

Copper Contributor

It's 2022 now, and Excel online still doesn't support analyze data for multiple tables? I am looking for that feature. 

Copper Contributor

What am I doing wrong?  Still don’t see pivot table option from Excel Online when using a spreadsheet created on my iPad.  Does my original spreadsheet have to be created online?

 

 

Thank you.

Version history
Last update:
‎Mar 04 2018 10:18 AM
Updated by: