fbpx

Weekend poll: Formulas or Pivot Tables?

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Time for a quick weekend poll. What is your favorite tool for data analysis?

  • Formulas
  • Pivot Tables
  • Or both

Post your choice in the comments. Also mention the number of years Excel experience you have.

For ex, my answer is: Both (10 years)

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

100 Excel Formulas List

From simple to complex, there is a formula for every occasion. Check out the list now.

20 Excel Templates

Calendars, invoices, trackers and much more. All free, fun and fantastic.

IRR and data tables in Excel

Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel

Do you want to simulate multiple cash-flow scenarios and calculate the rate of return? Then this article is for you. In this page, learn how to,

  • Introduction to IRR & XIRR functions
  • Calculate rate of return from a set of cash-flows with XIRR
  • Simulating purchase or terminal value changes with data tables
  • Apply conditional formatting to visualize the outputs
  • Common issues and challenges faced when using XIRR

167 Responses to “Weekend poll: Formulas or Pivot Tables?”

  1. John says:

    Both, 11 years

  2. Ashish says:

    Both (2 years)

  3. Vaibhav Garg says:

    Both, but more formulae than pivot tables.

    Even when using pivot tables, I put the GetPivotData function to a lot of use. (7 years)

  4. Prafull Jadhav says:

    I think Formulas are better than Pivot Table

  5. Dheeran says:

    Both (5 years)

  6. [1] Pivot Tables
    [2] Structured References
    [3] Formulas

    10 years

  7. Grégoire de Labarre says:

    Hi!

    Both
    -Formulas for the challenges
    -Pivot Tables for the ease of use
    7 years

    Have a nice day!

  8. Bokhari says:

    Both 17 years.

  9. Andi says:

    Both, but PivotTables are harder for users to break 🙂

  10. JoAnn Paules says:

    Pivot tables
    Basic Excel skills: about 12 years
    Advanced Excel skills: about 9 years

  11. Chihiro says:

    Both. Excel (8 years) After I saw QA chart using pivot 4 years ago, I learned to use it. Before that, almost exclusively formula.

  12. steve says:

    Pivot tables...definitely pivot tables

  13. Heather says:

    Both - which one I want depends on what I am trying to do.
    My Excel experience is over 15 years (but my database experience began with FoxBase on Apple years ago).

  14. Amberley says:

    Both - depending on what I'm doing.
    I probably use formulas more often because of what I do.
    (15 years)

  15. DE says:

    Formulas (about 6 yrs)

  16. Gunal Olcer says:

    Formulas. (30+ years. Starting by LOTUS.)

  17. Amarnath says:

    Both...(1) Year

  18. Marco Rico says:

    I say both.

    excel experience = 6 years and counting. 🙂

  19. Krishna says:

    Formulas.
    (makes you creative 😀 )

  20. kaZDan says:

    Both are useful to use

  21. Xiq says:

    Both (15 years or so)

    Pivot for getting something quick and easy
    Formulas for getting creative

  22. Steve says:

    Both, but usually formulas that grab the data from Pivot Tables. 7 years

  23. Robert says:

    Both (11 Years)

  24. JP says:

    Both (7 years)

  25. Awesome Walley says:

    Both (3 years). Pivots to sort and filter big data and formulas to massage it into useful metrics.

  26. Mike86 says:

    Fomulas

    30+ years

  27. Doug M. says:

    Pivot tables for the heavy lifting and formulas when needed. (9 years)

  28. doer513 says:

    Both (5 years)

  29. Michael says:

    I primarily use Pivot Tables. I've been an Excel user for 15 years.

  30. Brigitte says:

    Both (23 years plus Lotus 123)

  31. GJB says:

    I use both. Currently I work with analyzing a 400,000 row spreadsheet that takes too long to calculate formulas. Pivot Tables are more forgiving.
    A Chandoo.org expanded training on adding formulas to Pivot Tables would be very helpful.

    "Used" Excel for over 20 years. Used it as a more powerful tool only in the last 8 years.

  32. Erkki says:

    Both – depending on what I’m doing.
    I probably use Pivot tables more often because of what I do.
    21 years

  33. Jan says:

    Both

    25 years (Excel and Lotus 123)

  34. John says:

    Both ,,, for me it all depends on the situation or what is required at the time. Pivot tables can be quick and easy to pull information ( and with the use of slicers etc can be useful for occasional users ).

    15 years plus Lotus 123

  35. Clive says:

    Both, and Power Pivot
    Experience - well I started with VisiCalc so 30 something years

  36. Bermir says:

    Formulas, much more elegant & creative...

  37. Victor says:

    Both (14 years), dependng on the specific need

  38. Bermir says:

    (lost count)

  39. Sudhish says:

    Both, depending on the problem being addressed. (18 years)

  40. Donald Parish says:

    Pivot Tables - hard for users to break.
    Formulas - as needed, but tell users YMMV

    21 years - started with old school macros.

  41. Joe says:

    For analysis = pivot tables
    For a published dashboard = formulas are must!
    Years = however long has Excel been out.

  42. Adell Daugherty says:

    Both - almost 20 years

  43. Bill V says:

    I like both

  44. Srinivas Chilukuri says:

    Both (5 years)

  45. Lisa says:

    Formulas (25 years)

  46. Joann Calabrese says:

    Both

    6 years

  47. Paul says:

    Both. 10 yrs. Excel rocks my boat !

  48. Jim says:

    Both 3 years

  49. cllach says:

    Both (>20 & Lotus, Quatro, I7, Supercalc & others..)

  50. Lynda says:

    Formulas!
    Pivot tables are the one thing I just can't seem to get the hang of, but that could very well be that the test data I work with just doesn't fit that format well. And it's sensitive, proprietary data, so I can't send it out to the greater Excel HiveMind for assistance...;^) But with INDEX/MATCH formulas, they think I can walk on water AND turn it into wine! 10 years experience...

  51. Gary says:

    Both. 20 years.

  52. JOSE PEDRO MUNIZ VARGAS says:

    Formula (10 years)

  53. Pedro says:

    Pivot tables built on PowerPivot metrics. 3 years

  54. Bruce says:

    Both - 25+ years
    Formulas to correct data anomalies then Pivot Tables to summarize and report.

  55. Both- 24 yrs with excel. Lotus before that!

  56. Karlo says:

    both; 10 years

  57. Rich says:

    Both
    12 years

  58. 1. Pivot Tables - simpler to use and often avoids using array formulaas

  59. Gail Rahikka says:

    Pivot Table (17 years) I love Pivot Tables!

  60. Tyler says:

    Both
    Each has pros and cons. together they are awesome!

  61. Ken Rosenzweig says:

    Both (15 years) + Lotus 1-2-3 before that 🙂

  62. Formulas, 8 years with Excel

  63. Gino says:

    Formulas - almost exclusively. Users wreak way too much havoc when given access to pivot tables! 🙂

    I can lock away the formula sheets and just show them the pretty little check boxes and drop down buttons! LOL!

  64. John says:

    Both (15 yrs)

  65. Mike says:

    Formulas. 5 years with Excel

  66. Jdogzilla says:

    Both (20 yrs)

  67. Jeff S says:

    Both, depending on what works best for the user. More often, that's formulas. 20 years.

  68. Ken Wteherell says:

    Both (20 years)

  69. ANAND says:

    Both.
    But I prefer formuals more .

    10 YearS

  70. Chris says:

    Both (14 years)

  71. Ravikumar says:

    both
    no of years expereince 10 years

  72. Ravikumar says:

    answer is both
    no of years expereince 10 years

  73. H Lashkeri says:

    Both (10 years)
    I love pivot table very easy to get the results. Do special paste for formulas in another sheet.

  74. Eric~ says:

    Depends on the need...

  75. Ron Wallace says:

    Formulas for complex analysis, then Pivot Table for simple tasks that fit into that box. (Over 30 years, started with Lotus Symphony, then 123, Quattro Pro, and finally Excel when it got up to speed.)

  76. SamCal says:

    Pivot tables. Almost 27 years since it first appeared in Windows ~ late 1987 to early 1988. I was primarily an Apple ][ (VisiCalc) and CP/M (MultiPlan & SuperCalc) user back then, and Excel was available on Macintosh before Windows. I could not afford a Macintosh and was very resistant to the graphical GUI versus command line back then.

    I've used VisCalc, MultiPlan, SuperCalc, Lotus 1-2-3, Quattro Pro, SmartSuite, etc. on Apple ][, CP/M, DOS, MS-DOS, and Windows over the years beginning in the late 1970's. I never became very proficient in any of them, however. I recall Borland's Quattro was the fist time I learned to appreciate automatically calculated and rendered "pivot tables"; I recall Borland (Quattro) referred to them as "Crosstabs". I loved that program.

  77. Ben says:

    Both. 20+ years.

  78. Ericson says:

    It says favorite, so formulas. I enjoy formulas more but both have advantages (7 years)

  79. Alex says:

    Formulas only (4 years)

  80. Sandeep Badwal says:

    Both
    Fresher

    • Keith Bailey says:

      Both. 15+ years experience. The bigger and more complex the data, the more likely Pivot Tables will be used to provide "analysis" and an effective use of time for this discovery purpose.

  81. VivekD says:

    Pivot Tables (11 yrs)

    Formulas only if Pivot tables don't provide the desired results.

  82. Formulas, sometimes pivot tables.
    I use plans since the Lotus 1-2-3.

  83. Krishna Mysore says:

    Both. 17 Years

  84. etf says:

    I'm in love with (tables) and pivottables for data analysis.
    For other tasks I use formulae.
    (20+ years)

  85. Nallathambi says:

    Both 5 years

  86. GOPINATH says:

    If possible both or else any one of them. Your desired choice. Whatever published is useful to one.

  87. PM Kiran says:

    Both 5years

  88. Shailesh says:

    Formulaes (6 years)

  89. Sien says:

    I prefer formula, pivot is very rigid (20+ years)

  90. Prem Singh says:

    both & have 3 to 4 yrs exp.

  91. Mitali says:

    Pivot Tables. My Excel Experience is about 10 Years.

  92. Ramesh says:

    Formulas (More than 5 Years)
    Pivot Tables (More than 3 Years)

  93. sai says:

    both..5 yeras

  94. vivekanand says:

    Hi Team,

    I using both Pivot and Formula for Analysis. I have 5+ years of exposure to excel

  95. Suryakant says:

    More comfortable with Formula for last 14 years...but great to work with Pivot from last 3 years....

  96. Rahul says:

    Formulas, 4 years.
    Working most of the time with formulas. But am interested in developing on my basic knowledge of Pivot Tables.

  97. Mangirish Nadkarni says:

    Both (12 years)

  98. Anil says:

    Both and 6 years

  99. Ilyas says:

    Offcourse both (13 years),
    using pivot tables since last year

  100. SG Kenny says:

    Formulas (10 years)

  101. Pieter says:

    Good day to All Excellers of the globe!,

    I use formulas based designs - extensively. Here and there a pivot table. Data volumes at around 260 000 record up for simultaneous calculations is testing me with frustration big-time. Plots at this size causes frequent hang-ups.

    Can I use Pivots to work with a slides gliding through the information without having to use Visual basic? (I'm not good at this as yet - no time to look at this - pressure). How do I change the analysis range details via a slider?

    Using some "R" - very quick

  102. Anne Walsh says:

    Formulas for getting the data ready but then pivot tables - and more recently Powerpivot! (over 10 years...)

  103. Aileen McLoughlin says:

    Pivot tables
    10 years

  104. Art says:

    Both, 5 years.

  105. Srikanth says:

    Both! - 3 years of experience

  106. Amit Sony says:

    Both
    7 Years

  107. Wendell says:

    Formulas.
    I have not yet cracked Pivot Tables.

  108. Wendell says:

    Formulas. 20+ years
    I have not yet cracked Pivot Tables.

  109. Leah says:

    Both!
    Excel -18 years.
    LOTUS 123 -12 years before that.

  110. FerryFirmans says:

    I used both for different purposes and I have more than 15 years experience

  111. Kuldeep says:

    One time analysis, Pivot
    Re-occurring analysis . Formulas

    6 Years

  112. Sudhir Gawade says:

    Both (9 years)
    for Data analysis Pivot and for Dashboard formulas.

  113. Sudhir Gawade says:

    Both 9 years for Data analysis Pivot and for Dashboard formulas.

  114. Samantha says:

    Both - Pivot is my first choice

    20+ years

  115. Owen says:

    Pivot tables. I never mastered formulas always looked too much like programming in Assembler or Fortran.

  116. StJohn says:

    Both - it depends on the data and user requirements. Excel user +15 years

  117. Kiev says:

    Both, Used for 15 years.

  118. Allan says:

    25 years using Visicalc / Lotus and Excel.

    Focus mostly on formulas and really don't like Pivot Tables (sorry!).

  119. anand says:

    Both Formulas and Pivot Tables (8 years)

  120. Leonard says:

    both (8 years)

  121. JHarris says:

    Both, but I prefer to use formulas (20 years)

  122. Aziz says:

    Both (20 years)

  123. Venky says:

    Formulas and Pivot table in that order. I am using excel around 15 years.

  124. Deepak Anand says:

    Formulas (7 Years)

  125. KANUJ MAKKAR says:

    If it is one time anaylsis then I prefer Pivot table and if it is continous analysis baed on dynamic data Formuals are preferred. Using excel from last 5 years.

  126. Nikki says:

    Both [5.5 years]

  127. Emlyn Flint says:

    Formulae
    13 years.

  128. Asheesh says:

    Pivot Tables - 10years
    Structure references - 3 years
    Formulas - 12 years

    12 years of experience...

  129. Jacinto Morales says:

    both (7 years)
    Pivot Tables (7 years)
    Formulas (15 years)

  130. Worm says:

    Both. 15 years.

    As always it comes back to the appropriate tool for the task. I was something of a latecomer to Pivot Tables, and my first thought will nearly always be formulae. Particularly useful when rolling out dashboard style reporting to non technical users.

  131. Worm says:

    that is - 'pivot tables are particularly useful when....'

    tsk

  132. Haithem says:

    Formulas (6 Years)

  133. Eliud Glz says:

    I believe both complement each other.
    1. Pivot tables (in order to Analyse)
    2. Excel formulas (to structure, find, link, etc)

    The experience that i had learned is, when you manipulate information, you must structure the information as much as detail you need.
    Experience: 6 years
    Mexico
    Accountant

  134. Shaikhrulez says:

    Both.

    6 years of experience

  135. Akhter Hussain says:

    Both - 9 Years

  136. Mando says:

    If it's a quick time constrained report, pivots.
    When am creating templates and such, formulas.

    intermediate 7 years 🙂

  137. sam says:

    Everything you can do with a Pivot can be done with a Formula. The converse is not true

    It would be difficult to build a reasonably complex model in Excel without using formulas

    However with a advent of Power Query and Power Pivot - the balance is slowing tilting towards formula free solutions is Excel

    20 Years of Pivots + Formulas

  138. Frederick Cortes says:

    Formulas 2 years

  139. Vance says:

    Dealing with large populations or sample sets, I almost always start with descriptive statistics and certain general formulas. Once I understand the distribution and general patterns, I use PivotTables to tease out more specific details, relationships, and trends than you can quickly find with broad summary statistics. (18 years)

  140. Sally says:

    formulas - 18 years.
    Learnt about pivot tables at a training course in 1997, but couldn't get my head around it then.
    Starting to use them a bit now, as so much easier now with Excel 2010
    Also learnt about vlookup at the same course - I could immediately see an reason to use it, and 18 years later, vlookup is still my "go-to" formula. I do use lots of other formula's but vlookup is what I first think of (I have "what to look up, where to find it, what to bring back, false" ringing in my head)
    Sally

  141. Adrian S says:

    Pivot Tables for exploratory data analysis
    Formulas for forecasting data analysis
    4 years

  142. sureshraj says:

    both (5Years)

  143. Gadi Chrust says:

    Formulas; by far. 12 years.

  144. Prajwal says:

    Formulas (3 Years)

  145. Chirayu says:

    Most of the time I use Formulas but sometimes I use Pivots. Depends on data

  146. Rudra says:

    Both,
    10 years

  147. Michael (Micky) Avidan says:

    Most of the time I use Formulas but sometimes - when the requested report is too complex - I use Pivot tables.
    In general I prefer Formulas at least until the Pivot Tables will be capable to Auto-Refresh (without the need of VBA Event-Macro command such as: ActiveWorkbook.PivotCaches(1).Refresh
    (I met many ashamed users who published a “false report”, which didn’t meet the source data, only because they forgot to "refresh" the PT after altering the Source data).
    (Many years of experience - in fact, since "Lotus 1-2-3" for DOS)
    ----------------------------
    Michael (Micky) Avidan
    “Microsoft® Answers" - Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2016)
    ISRAEL

Leave a Reply