• Skip to main content

Annielytics.com

I make data sexy

  • About
  • Services
  • Tools
  • Blog
  • Portfolio
  • Contact
  • Log In

Jun 23 2013

My Biggest Pivot Table Annoyance (And How To Fix It)

annoying autofit in Excel pivot tables
Zomg anNOYing

 

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.

pivot table options in Excel 2013

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.

pivot table options for Excel 2011

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. 🙂

Image by dfinnecy.

~~

Written by Annie Cushing · Categorized: Excel

Reader Interactions

Comments

  1. JHTScherck says

    June 23, 2013 at 5:11 pm

    I have been trying to figure out how to stop pivot tables from doing this for months. Thanks you Annie!

    Reply
    • Annie Cushing says

      June 23, 2013 at 5:22 pm

      I’m especially glad it helped you, J-H!

      Reply
      • Idan says

        October 6, 2014 at 1:41 pm

        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?

        Reply
        • Annie Cushing says

          October 7, 2014 at 4:06 pm

          I’m sorry, Idan. I’m not sure what you mean.

          Reply
  2. WholesomeGoodness says

    July 3, 2013 at 3:21 am

    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.

    Reply
    • Annie Cushing says

      July 3, 2013 at 8:58 am

      Nice! I’m actually going to start using the right-click option. Much easier. Thanks!

      Reply
      • WholesomeGoodness says

        July 3, 2013 at 2:22 pm

        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!

        Reply
  3. minghai Zhao says

    July 3, 2013 at 5:17 pm

    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.

    Reply
    • Annie Cushing says

      July 3, 2013 at 11:00 pm

      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.

      Reply
      • minghai Zhao says

        July 4, 2013 at 12:10 am

        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.

        Reply
        • Annie Cushing says

          July 4, 2013 at 10:10 am

          I checked it out. I don’t see a disclosure about what you do with the data people upload.

          Reply
          • Sean Zhao says

            July 4, 2013 at 12:00 pm

            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.

          • Annie Cushing says

            July 4, 2013 at 12:45 pm

            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.

          • minghai Zhao says

            July 4, 2013 at 1:50 pm

            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.

          • minghai Zhao says

            July 4, 2013 at 2:04 pm

            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.

          • Annie Cushing says

            July 4, 2013 at 2:19 pm

            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.

          • minghai Zhao says

            July 4, 2013 at 2:43 pm

            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.

  4. Kata says

    March 26, 2014 at 6:04 am

    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!

    Reply
    • Annie Cushing says

      March 26, 2014 at 7:45 am

      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.

      Reply
      • Kata says

        March 26, 2014 at 10:45 am

        Thanks for your quick reply Annie!
        I would need empty columns, not rows in my pivot. Is that possible as well?
        Thanks.

        Reply
        • Annie Cushing says

          March 26, 2014 at 11:39 am

          You’d have to use a hack like this: http://datapigtechnologies.com/blog/index.php/add-column-spacing-in-a-pivottable/.

          Reply
          • Kata says

            March 27, 2014 at 6:33 am

            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.

          • Annie Cushing says

            March 27, 2014 at 7:25 am

            No, I don’t.

  5. Melida says

    March 23, 2017 at 12:16 pm

    Hi,

    I just have to thank you because this has been driving me crazy for months. thank you, thank you.

    Reply
    • Annie Cushing says

      March 23, 2017 at 12:57 pm

      Fantastic! My pleasure!

      Reply
  6. Bob Brown says

    May 2, 2017 at 3:19 pm

    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

    Reply
    • Annie Cushing says

      May 3, 2017 at 11:17 am

      Great point, Bob!

      Reply
  7. Lisa says

    May 18, 2017 at 7:35 pm

    Thank you for saving me from the repeated prod of a blunt but woeful torture instrument!

    Reply
  8. David says

    October 9, 2017 at 12:03 am

    Thank you for this article!

    Question – do we know if this is possible using Google Sheets?

    Reply
    • Annie Cushing says

      October 10, 2017 at 10:15 am

      Google Sheets does offer pivot tables. They don’t offer nearly as much functionality as Excel’s pivot tables though.

      Reply
  9. Margaret says

    January 29, 2018 at 12:02 pm

    Thanks – the resizing was really bugging me!

    Reply
  10. Catherine says

    January 31, 2018 at 2:42 am

    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.

    Reply
    • Annie Cushing says

      January 31, 2018 at 7:08 am

      Agreed!

      Reply
  11. J. Gabriel says

    April 11, 2018 at 6:56 pm

    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.

    Reply
    • Annie Cushing says

      October 10, 2018 at 8:35 pm

      Fantastic! ?

      Reply
  12. Alex says

    October 10, 2018 at 1:35 pm

    Thank you so much. I was going nuts with extra-long strings constantly pushing the columns outside my screen

    Reply
    • Annie Cushing says

      October 10, 2018 at 8:35 pm

      My pleasure!

      Reply
  13. matthew hallewell says

    October 31, 2018 at 3:01 am

    Thanks Annie,

    But how do I stop the rows resizing. This is messing with my dashboard

    Reply
    • Annie Cushing says

      November 1, 2018 at 7:55 pm

      I’m not aware of any way to do that. Excel gives you very few control options for rows.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Copyright © 2025