 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Hoping the hive mind can help me out with an Excel quandary.
I'm using pivot tables to consolidate lots of data. The first one I did had SUM as default when adding data to the value field, but all subsequent attempts have COUNT as default. It's also defaulting to adding all data to the rows field when the first one let me add multiple values under the value heading.
It's just becoming time consuming having to move things from the row field to the value field and then changing settings from COUNT to SUM. My Google-Fu has proven to be weak. Is there some way to set defaults for value type and where data is to be placed?
Any help appreciated as I can't figure why the first one worked and subsequent attempts aren't playing nice.
Apparently there's no way to set defaults but from some random site I found:
"The PT default mode is to use SUM if all the data in the range for that
field are Numeric and to use COUNT if there are any text data or blank
cells."
So if there are blank cells you could put zero's into them to change the way excel handles the data.
Yep, blank cells are present. I'll put zeros in them and see if that works. Cheers.
Yeh its an annoying feature of pivot tables if there are any non numerical values than it defaults to count
Awesome! Thanks OP and others, I've been wondering what causes this for a while as I often run IF formulae where a false returns "" (blank cell) and this has been annoying me for yonks!