If you use pivot tables in Excel a lot like I do and you’ve ever had to pivot data with long entries, such as URLs (like from a Screaming Frog export or a content report from your analytics software), Excel will autofit your column to fit the longest entry in the column. This can make for insanely wide pivot table columns. So what I generally do is tighten up the column widths to avoid these unsightly double wides.
However, every time you add new data or shuffle things around (or anything that causes the pivot table to refresh), your columns will autofit again. This can quickly become an exercise in frustration because each time you refresh you have to go through and fix all your pivot table column widths.
‘Nuff whining. Here’s how to fix it:
Excel 2013 (PC)
Click anywhere inside the pivot table, and then go to PIVOTTABLE TOOLS > Analyze tab > PivotTable group (far-left group) > Options (or right-click and choose PivotTable Options). In the PivotTable Options dialog, under the Layout & Format tab, uncheck Autofit column widths on update under Format, then click OK.
Excel 2010 (PC)
Click anywhere inside the pivot table, and then go to PivotTable Tools > Options tab > PivotTable group (far-left group) > Options (or right-click and choose PivotTable Options). In the PivotTable Options dialog, under the Layout & Format tab, uncheck Autofit column widths on update under Format, then click OK. (The dialog looks identical to the 2013 version.)
Excel 2011 (Mac)
Click anywhere inside the pivot table, and then go to the PivotTable tab (in purple) > Data group > Options (or right-click and choose PivotTable Options). In the PivotTable Options dialog, choose Layout at the top, and the uncheck AutoFit column widths on update, then click OK.
And, yes, apparently only the cool Mac kids spell autofit with a captial F. Please note that for the future, kthx.
Learn More
You can learn more about data formatting in my Annielytics Dashboard Course, a 16-hour video course that will teach you how to put your data in stilettos and work the pole. 🙂
~~~
If you would like to learn more about Excel, check out my Excel dashboard course. 24 instructional videos, totaling 6+ hours of instruction for $95.
I have been trying to figure out how to stop pivot tables from doing this for months. Thanks you Annie!
I’m especially glad it helped you, J-H!
can you please do the same article about what happens when you use calculated fields in pivots, in each time you have to input the same formula…. is there a solution for that?
I’m sorry, Idan. I’m not sure what you mean.
And in Excel 2007 it is …
Right click anywhere on the pivot table then click pivot table options. In the dialog the opens up, untick autofit columns on update, which is the penultimate line on the first tab.
Nice! I’m actually going to start using the right-click option. Much easier. Thanks!
No worries. Always more than one way of doing things in Excel … often ten ways of doing it, in fact. One of the best things about it … and probably one of the most frustrating when you’re just picking it up!
Hi Annie,
I saw you have written a lots of article related to pivot table in excel. That’s really helpful and do you have a time to take a look at a pure web version pivot table at
webpivottable.com and bi2.io.
I am the author of this component and would like to have you opinions how to improve it to better serve advance users like you.
Thanks but I’d have to know the business better to download software on my computer. Without insulting you, may I recommend that you invest in some professional copywriting services? The English on the homepage is so poor, it strips the site of credibility.
Thanks a lot for your time and suggestions, Annie. Yes, you are right, English is not my native language and I was more focus on technology 🙂 I will improve this laterly.
Actually, you don’t need download anything if you just need to use pivot table online. The downloadable software is for web developers who would like to integrate this into their web site or web application. Bi2.io will be served as a Software as a service (SaaS) for users just like gmail as email online service. The advantage of bi2.io than excel as pivot table software is that it is totally web based, you don’t need download any software or upgrade it. What you need is only a browser like google chrome, firefox, safari or IE9, your data can be loaded from local drive or cloud drive (this is particularly important if you are using a tablet like ipad). I have implemented most excel functionality for CSV format data but of course more advanced functionality like sort, calculated field will be added in future. Another business point is that it also support OLAP cubes, that’s for enterprise business intelligence requirements.
You are an advanced pivot table user, I will appreciate if you can list some features that you think is very important when you use excel but were missing at bi2.io so that I can try to add them in the future.
Again, thanks for your input.
I checked it out. I don’t see a disclosure about what you do with the data people upload.
Thanks for your check. Please use modern browsers like google chrome, Firefox, safari or ie9 go to bi2.io click import data button at top left of screen, then choose import from CSV tab, you will see some options in the left side of dialog, you can select from local drive or from cloud drive to upload your own data. You call also just click sample link to import those sample data. After you upload your data, click import button at right bottom to import them into pivot table. Thereafter, everything is exactly like excel.
I wasn’t unclear on how to use it. I want to know if you store the data. Without a full disclosure no one’s going to use it.
Currently I didn’t store anything, everything is at your browser. while you close your browser or move to other sites, everything lost.
Yes, you are right, nobody will use it until they can save their reports and reopen it without import data again.
I am still developing this. I will add user login, user management and report saving functionality eventually. Currently this is just a demo to show users what a web version pivot table can do. I need to implement enough functionality of what excel pivot table have then I will think put it into production. So that’s why I am very appreciate your opinions about what a pivot table advanced user are looking for with this kind of software.
Excel is a great product and pivot table is a very strong feature of excel. But you need buy excel, install it, upgrade it, send files back and forth between your computers and you can’t use it at ipad, you can’t use it at Mac book. you can’t share the same report online with colleagues, this is what SaaS for.
If you have used google spreadsheet and pivot table in it, you will love this concept. Just like after we start to use gmail, we will never go back to use outlook except at enterprise internal. But google pivot table is not good enough and google canceled it for a while. I am not quite sure what status it is now.
I am not to say that my product can compare and compete with microsoft and google, I am just trying to give users a free or very low cost choice that can meet most of their requirements and also quite easy to be used.
And, yes, your concern about the security of user data is a very good point. All SaaS solution face the same problem and need to deal with as well. And that’s the major reason why big company don’t want to use Saas. But this is a trend, everything will be in the cloud. Our email, our facebook, our social media, all in the cloud.
No, no. That’s not what I’m saying at all. What I’m saying is you need to disclose if you’re storing data. Most people won’t want their data stored. They’ll want to be able to export and go. Solutions like Many Eyes store the data and make it publicly available. I found one company’s entire customer database b/c someone uploaded the data not realizing that it is not only stored but shared.
Oh, I see, Annie. Don’t worry, I didn’t store anything, everything is in your computer or your cloud drive, nobody can touch it even me. Of course, I am a technology guy, I thought that user know this technology 🙂 but I was definitely wrong at here. Thanks for point out this and I will add disclosure statement lately. Again, this is not in production, it still just a demo and I am still far away from put it in production.
Hi! Do you also know how to fix general formating? I mean I have a source of 50 countries over several divisions and I want to disply my pivot for each month of the year. I want an empty cell before Jan as a divider between the years. So I entered one line in my source withthe month dot. Now I cannot repeat that for all 50 countries accross all division (it’s just too many) and I don’t want to copy them to the buttom of my report each time I refresh it. Can I fix the formating of the month? I want that “dot” month to appear in the pivot even when it’s not in the source data for a specific filer value. Is that possible?
Thanks and cheers!
You don’t ever want to add spaces to your source data. I can’t envision your data set or what you’re trying to accomplish, but you have a couple options for adding empty rows and clustering months by year. You can add rows under the Design tab by choosing Blank Rows under the Layout group. To cluster months by year, right-click on any month and choose Group. Then choose to group by year. This is more effective than adding spaces.
Thanks for your quick reply Annie!
I would need empty columns, not rows in my pivot. Is that possible as well?
Thanks.
You’d have to use a hack like this: http://datapigtechnologies.com/blog/index.php/add-column-spacing-in-a-pivottable/.
Yes I tried that. Just that I have 12 month and a total for several years. so my column labels are made of two categories (years and month). When I use that trick I get 12 columns returned not 1. What am I doing with the remaining unnecessary columns? Do you have a trick for that?
thanks for all your advice so far. It is much appreciated.
No, I don’t.
Hi,
I just have to thank you because this has been driving me crazy for months. thank you, thank you.
Fantastic! My pleasure!
Thanks for this helpful info.
One note that might be helpful to others. I often have several pivot tables on one sheet. ‘Refresh’ refreshes them all, so every one needs to have the ‘autofit column widths on update’ checkbox cleared. Else, the one of ten that didn’t get the checkbox cleared will adjust the column widths.
Thanks again,
Bob
Great point, Bob!
Thank you for saving me from the repeated prod of a blunt but woeful torture instrument!
Thank you for this article!
Question – do we know if this is possible using Google Sheets?
Google Sheets does offer pivot tables. They don’t offer nearly as much functionality as Excel’s pivot tables though.
Thanks – the resizing was really bugging me!
Yay! This has driven me mad for years.
So glad to have a solution. It would be great if it was in a more obvious place.
Agreed!
Five years later and this article still shines! It solved the maddening column-width problem for me, like it has so many others. BTW, the 2013 advice works on Excel 2016 as well.
Fantastic! 🙌
Thank you so much. I was going nuts with extra-long strings constantly pushing the columns outside my screen
My pleasure!
Thanks Annie,
But how do I stop the rows resizing. This is messing with my dashboard
I’m not aware of any way to do that. Excel gives you very few control options for rows.