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)
How to calculate WEEKNUMBER in Month / Quarter / Year with Excel?
Let’s say you have daily data and your boss wants to see the trends by week in month or week in quarter? How do you calculate the week number in a month or quarter? In this article, let me explain the logic and formulas we can use Excel for this.
167 Responses to “Weekend poll: Formulas or Pivot Tables?”
Both
Pivot Tables.
15 years using Excel, but last 5 years more analysis.
both
Both 5 years
Both
Love, love, love Pivot Tables.
Need formulas though, for automating BW workbooks. Unless changing pivot tbl data source can be automated (but I don't know VBA).
10-15 years
Both, 11 years
Both (2 years)
Both, but more formulae than pivot tables.
Even when using pivot tables, I put the GetPivotData function to a lot of use. (7 years)
I think Formulas are better than Pivot Table
Both (5 years)
[1] Pivot Tables
[2] Structured References
[3] Formulas
10 years
Same as Winston:
[1] Pivot Tables
[2] Structured References
[3] Formulas
10 years
Same as Winston
20+ years
Hi!
Both
-Formulas for the challenges
-Pivot Tables for the ease of use
7 years
Have a nice day!
Both 17 years.
Both, but PivotTables are harder for users to break 🙂
Pivot tables
Basic Excel skills: about 12 years
Advanced Excel skills: about 9 years
Both. Excel (8 years) After I saw QA chart using pivot 4 years ago, I learned to use it. Before that, almost exclusively formula.
Pivot tables...definitely pivot tables
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).
Both - depending on what I'm doing.
I probably use formulas more often because of what I do.
(15 years)
Formulas (about 6 yrs)
Formulas. (30+ years. Starting by LOTUS.)
Both...(1) Year
I say both.
excel experience = 6 years and counting. 🙂
Formulas.
(makes you creative 😀 )
Both are useful to use
Both (15 years or so)
Pivot for getting something quick and easy
Formulas for getting creative
Both, but usually formulas that grab the data from Pivot Tables. 7 years
Both (5 years)
Both - 10 years
Both (11 Years)
Both (7 years)
Both (3 years). Pivots to sort and filter big data and formulas to massage it into useful metrics.
Fomulas
30+ years
BOTH
Pivot tables for the heavy lifting and formulas when needed. (9 years)
Both (5 years)
I primarily use Pivot Tables. I've been an Excel user for 15 years.
Both (23 years plus Lotus 123)
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.
Both – depending on what I’m doing.
I probably use Pivot tables more often because of what I do.
21 years
Both
25 years (Excel and Lotus 123)
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
Both, and Power Pivot
Experience - well I started with VisiCalc so 30 something years
Formulas, much more elegant & creative...
Both (14 years), dependng on the specific need
(lost count)
Both, depending on the problem being addressed. (18 years)
Pivot Tables - hard for users to break.
Formulas - as needed, but tell users YMMV
21 years - started with old school macros.
For analysis = pivot tables
For a published dashboard = formulas are must!
Years = however long has Excel been out.
Both - almost 20 years
I like both
Both (5 years)
Formulas (25 years)
Both
6 years
Both. 10 yrs. Excel rocks my boat !
Both 3 years
Both (>20 & Lotus, Quatro, I7, Supercalc & others..)
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...
Both. 20 years.
Formula (10 years)
Pivot tables built on PowerPivot metrics. 3 years
Both - 25+ years
Formulas to correct data anomalies then Pivot Tables to summarize and report.
Both- 24 yrs with excel. Lotus before that!
both; 10 years
Both
12 years
Both (21 years)?
Join me at http://www.careware.wordpress.cpm
1. Pivot Tables - simpler to use and often avoids using array formulaas
Pivot Table (17 years) I love Pivot Tables!
Both
Each has pros and cons. together they are awesome!
Both (15 years) + Lotus 1-2-3 before that 🙂
Formulas, 8 years with Excel
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!
Both (15 yrs)
Formulas. 5 years with Excel
Both (20 yrs)
Both, depending on what works best for the user. More often, that's formulas. 20 years.
Both (20 years)
Both.
But I prefer formuals more .
10 YearS
Both (14 years)
both
no of years expereince 10 years
answer is both
no of years expereince 10 years
Both (10 years)
I love pivot table very easy to get the results. Do special paste for formulas in another sheet.
Depends on the need...
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.)
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.
Both. 20+ years.
It says favorite, so formulas. I enjoy formulas more but both have advantages (7 years)
Formulas only (4 years)
Both
Fresher
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.
Pivot Tables (11 yrs)
Formulas only if Pivot tables don't provide the desired results.
Formulas, sometimes pivot tables.
I use plans since the Lotus 1-2-3.
Both. 17 Years
I'm in love with (tables) and pivottables for data analysis.
For other tasks I use formulae.
(20+ years)
Both 5 years
If possible both or else any one of them. Your desired choice. Whatever published is useful to one.
Both. Almost 20 years.
Both 5years
Formulaes (6 years)
I prefer formula, pivot is very rigid (20+ years)
both & have 3 to 4 yrs exp.
Pivot Tables. My Excel Experience is about 10 Years.
Formulas (More than 5 Years)
Pivot Tables (More than 3 Years)
both..5 yeras
Hi Team,
I using both Pivot and Formula for Analysis. I have 5+ years of exposure to excel
More comfortable with Formula for last 14 years...but great to work with Pivot from last 3 years....
Formulas, 4 years.
Working most of the time with formulas. But am interested in developing on my basic knowledge of Pivot Tables.
Both (12 years)
Both and 6 years
Offcourse both (13 years),
using pivot tables since last year
Formulas (10 years)
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
Formulas for getting the data ready but then pivot tables - and more recently Powerpivot! (over 10 years...)
Pivot tables
10 years
Both, 5 years.
Both! - 3 years of experience
Both
7 Years
Formulas.
Formulas.
I have not yet cracked Pivot Tables.
Formulas. 20+ years
I have not yet cracked Pivot Tables.
Both!
Excel -18 years.
LOTUS 123 -12 years before that.
.....And I continue to learn more each day!!
Formulaes
I used both for different purposes and I have more than 15 years experience
One time analysis, Pivot
Re-occurring analysis . Formulas
6 Years
Both
Both (9 years)
for Data analysis Pivot and for Dashboard formulas.
Both 9 years for Data analysis Pivot and for Dashboard formulas.
Both - Pivot is my first choice
20+ years
Pivot tables. I never mastered formulas always looked too much like programming in Assembler or Fortran.
Both - it depends on the data and user requirements. Excel user +15 years
Both, Used for 15 years.
25 years using Visicalc / Lotus and Excel.
Focus mostly on formulas and really don't like Pivot Tables (sorry!).
Formulas 29 Years
Both Formulas and Pivot Tables (8 years)
both (8 years)
Both, but I prefer to use formulas (20 years)
Both (20 years)
Formulas and Pivot table in that order. I am using excel around 15 years.
Both (8years)
Formulas (7 Years)
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.
Both [5.5 years]
Formulae
13 years.
Pivot Tables - 10years
Structure references - 3 years
Formulas - 12 years
12 years of experience...
both (7 years)
Pivot Tables (7 years)
Formulas (15 years)
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.
that is - 'pivot tables are particularly useful when....'
tsk
Formulas (6 Years)
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
Both.
6 years of experience
Both - 9 Years
If it's a quick time constrained report, pivots.
When am creating templates and such, formulas.
intermediate 7 years 🙂
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
Formulas 2 years
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)
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
Pivot Tables for exploratory data analysis
Formulas for forecasting data analysis
4 years
both (5Years)
Formulas; by far. 12 years.
Formulas (3 Years)
Most of the time I use Formulas but sometimes I use Pivots. Depends on data
Both,
10 years
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