Join 3,438 readers in helping fund MetaFilter (Hide)


Excel Short cut Question
July 29, 2010 9:47 AM   Subscribe

Excelfilter: This may be a long shot, but..... Is there a quick way of formatting a table to group like records under a single heading?

I'm not sure I'm going to describe this correctly, but I want to change a simple table like this into a formatted chart like this. In the past, I've done all this manually, but I'm sure there has to be a quicker way. I'm not familar with macros and the like but I think I could figure them out with a bit instructions. Am I correct in thinking that that's what I need to do? Are there some good resources the hive-mind could point me to? Something that would instruct the program to grab all lines with the value "--" in category A and populate the table under A.
posted by Kurichina to Computers & Internet (14 answers total) 1 user marked this as a favorite
 
I think you may be able to do something like this with Pivot tables? I am new to them myself, so you'll have to do some googling, but from what I've seen you have a good shot.
posted by xammerboy at 9:55 AM on July 29, 2010


Seconding xammerboy - that looks EXACTLY like an application of Pivot Tables. Warning: they're a lil' bit of a pain in the ass sometimes. HOWEVER, if you have your base table labeled and sorted and organized neatly, you should be able to generate a workable PivotTable from it with minimal effort.
posted by julthumbscrew at 10:02 AM on July 29, 2010


Which version of Excel are you using? Pivot tables will work, but they work slightly differently in the 2003 and 2007 versions.
posted by prenominal at 10:03 AM on July 29, 2010


Macros is an option. You could also build a report on a second tab that accomplishes what you want via lookups, but it wouldn't necessarily be easier. You'd definitely master the INDEX function, though.

My aversion to the "report format" in your second image comes from its lack of flexibility. If anything about your input data (the first image) changes, you have to recreate the report. Yes, sometimes the people that use these reports are used to a specific format, and you really have no option but to continue formatting the report in that way.

Pivot tables, though, are far superior as analytical tools, and the formatting is done for you. If you can master the use of pivot tables, and then educate the people that will use your reports on the benefits of pivot tables, you'll be the Excel hero.
posted by RobinFiveWords at 10:05 AM on July 29, 2010


Pivot table instructions from the MSOffice website
Excel 2003
Excel 2007
posted by prenominal at 10:11 AM on July 29, 2010


I'd use this site for instructions on pivot tables.

Also, if you want great answers to Excel questions, aside from AskMe, try this discussion group out.
posted by dfriedman at 10:17 AM on July 29, 2010


OK. I can do a pivot table, but those usually generate counts, sums, etc of the values in between, where as I do actually want a list of the data to display that fits in whatever category.

There is no analysis taking place in this particular report, only a display (and a lot of the data that should be numeric isn't). The people using this particular report have no interest in anything other than getting it to fit tidily on a piece of legal-size paper.

(I'm using MS 2007, btw.)
posted by Kurichina at 10:17 AM on July 29, 2010


I don't see why you can't format a pivot table like that.

Another option for that kind of formatting is to use Access to generate a report, but that is likely more trouble than it is worth to you.
posted by dfriedman at 10:23 AM on July 29, 2010


I suggest you have a read through this documentation.

It may well give you what you need.
posted by Simon_ at 10:39 AM on July 29, 2010


I don't see why you can't format a pivot table like that.

It's possible I could, but that's the part that I don't know how to do. For example, in this link, the example shows sums of "Insured Value" for the categoreis, 'central', 'east', etc. What I'd want to do rather is have all the the "insured values" along with several other columns for 'central' under a horizontal heading called 'central'.
posted by Kurichina at 10:42 AM on July 29, 2010


Looks like this would easily be done in Access using one of the Report Wizards. It's very straightforward to import Excel into Access, not sure why dfriedman thinks its troublesome. Just open Access, create a new blank database, go to File > Get External Data > Import.

Once you've got the data into a table, go to the reports section of Access and click Create New Report Using Wizard. It'll walk you through all the grouping level issues, etc.
posted by jasper411 at 11:13 AM on July 29, 2010


Access is troublesome because not everyone has it, and, even if they do, they have to insure that their data in Excel is formatted properly. Else they will generate confusing import errors.
posted by dfriedman at 11:22 AM on July 29, 2010


I still use 2003, but I'll try to answer your question about how to get a pivot table to just organize the data and not summarize it. Put everything you don't want summarized into the ROW or COLUMN area and put some unimportant detail in the DATA area. I'd put something like "Count of Applicant" from your example. What you should get is your data organized and then an unneeded column at the end that has a 1 in every row. If you need to remove that column or tweak the sheet even more, Copy the whole page and then Paste Special... and select Values. That will keep everything there but remove the Pivot Table aspects of the sheet.

Sorry if this does not apply 2007.
posted by soelo at 1:28 PM on July 29, 2010


The pivot table as soelo described in combination with a series of vlookups (facilitated by concantanates) seems to have done the trick. Thanks hive mind!
posted by Kurichina at 10:31 AM on July 30, 2010


« Older What is this insect sitting on...   |  How do I automatically alphabe... Newer »
This thread is closed to new comments.