• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Excel 2007 pivot table-- can you show format a field for no calculations in the "Sum values" drag box?

I'd like to show values in a pivot table column that are not calculated.  However, when I drag this field into the "Sum values" box, I do not see any options except for calculated options.  I'd like to just show text.

Thanks for any suggestions you can offer!
0
BBRRGG
Asked:
BBRRGG
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
MadonnaCCommented:
I would try Max or Min - the only time you may see unexpected results would be when there are actually 2 or more possible values for a cell
0
 
BBRRGGAuthor Commented:
Hmmm, still getting 0s when I do max or min...
0
 
telyni19Commented:
Can you give any examples of the values you are trying to show?

Generally in a pivot table, you'll put textual values as categories along either the column or row headers, and then put numerical or grouped and counted values in the central values area to be counted or summed. Based on what you've said, you could try putting your data in the row header area instead, and remove subtotals. If you've only got one value per category, then you should get what you're asking for.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Richard DanekeTrainerCommented:
Could you possible use that field as an additonal ROW or COLUMN value?   Data values are expected to be counts or other domain aggregate functions.  
0
 
MadonnaCCommented:
Put all the values you want as text in the Rows box
In the Field Settings, set the SubTotals and Filters to None
Set Layout & Print to Show Item Labels in tabular form

See attached spreadsheet for an example
PivotTest.xlsx
0
 
BBRRGGAuthor Commented:
Thanks for all's input.

MadonnaC-- this is certainly a great way to do what you're showing.  However, what I need to do is show these text records not as row labels but rather as values in 1 particular field, where other fields are in fact calculated.  It would look something like the following, where "additional notes" is the field I need to include from the raw data table.  Currently, when I try to add "additional notes," the program wants me to calculate the field.

             SumOf Days   CountOf Holidays     additional notes
Jan          31                             2                   mom's birthday
Feb          28                             0                   trip to Florida
March      31                             0                   basketball tournament
April         30                             0                   Taxes


Sounds like this may not be possible... I'll give it another day or so and then will close out the question... unless anyone has any further thoughts / suggestions.  Thanks!
0
 
telyni19Commented:
It looks like you're doing a pivot table on data where you have one line for each day. The problem is that when you summarize by month, the "additional notes" are not connected to the month, but to the day. If you want the notes to be effective for the whole month, you'll have to organize the data differently. To give you a comparison, what would happen, in your current setup, if you have notes for more than one day? Would you want them combined into one text field to show for the month? It isn't clear to Excel what would happen; there's no "concatenate" summary function for text fields (although that would certainly be useful in situations like this).

If you do what I suggested before and put the Notes field as a row header, you'll get a table that looks like the attached, where you have the data summarized by each note (I've included a second March note to illustrate) plus the blanks. With the subtotals, you get the details by note as well as the totals for each month. I think this is the closest you can get to your example table with an Excel pivot table.

If you used Access, you could have two tables, one for date (which includes holidays) and one for month (which includes monthly notes), and combine the two in one query which could produce exactly what you gave. But the notes would not be tied to a particular day in that case, so you'd lose the meaning of which day is "mom's birthday" for instance.
monthly-notes.xls
0
 
BBRRGGAuthor Commented:
Thanks!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now