Help me create a simple spreadsheet (Excel)

Factual Questions
1

I know I could figure this out myself, but I’d like to be schooled in order to learn the best method of accomplishing my modest goal.

I am in the market for a new vehicle. I want a method of comparing vehicle rankings from various (maybe 6 or 7) websites such as Edmunds, Car & Driver, etc.

These sites review vehicles, and there is always a ranking of, say, best compact SUVs. On one site the Mazda may be ranked #1, #3 on another, etc.

I’d like to be hand-held through the process of setting up an Excel spreadsheet that would allow me to see, at a glance, where different vehicles rank. Maybe even a method of consolidating all the rankings into a master ranking, if that is possible.

Or maybe I’m over-complicating things and I should simply enter each ranking into the spreadsheet and manually/visually determine which vehicles rate highest.

All advice appreciated.

mmm

2

I think your best bet for not overcomplicating things is to use conditional formatting to color the cells with the ranking. That way you’ll get an “at-a-glance” appreciation of the spread of rankings. I think I would also add a simple “average of rankings” and sort the entries by that.

3

That is pretty cool, thanks, @naita.

mmm

4

You ask how to do this in Excel. Clarification question: do you wish to improve your skills in Excel so you’re using this self-assigned task as an instructional experience towards that end, or do you wish to compare different rankings of the same vehicle at a glance and doing it in Excel is what strikes you as the way to go at it?

I ask because Excel does not strike me as the ideal tool for the job. Better than Word, to be sure, but not the app I’d reach for.

5

Both, actually.

I want to improve my Excel skills. And I don’t think I need anything more powerful (or something that I don’t already have easy access to) to accomplish what seems to me to be a pretty basic objective.

Curious, though - what software would be more appropriate?

mmm

6

Well, you know the meme about how when the only tool at your disposal is a hammer, everything looks like a nail? I should confess in advance that my hammer is FileMaker. Having issued that disclaimer though, yeah I would use FileMaker for this.

Table: Vehicles, in which every record (row) is a specific make model & year of vehicle

Table: Publications, in which every record (row) is a publication like Edmunds or Car & Driver

Table: Issue, in which every record (row) is a single issue of a publication with a pub date and an evaluation article in it

Table: Ranking, in which every record (row) is a ranking OF a specific Vehicle BY a specific evaluation article OF a specific publication

From the context of Vehicles I could have a portal to Ranking and sort it by a quantitative equivalent of the ranking that the valuation articles used, color code it by publication, whatever;

From a neutral context I could set up a portal to Vehicles, showing each vehicle once and sort them by the average of the many different rankings that each vehicle has obtained from all the related Ranking records

From Publication I could set up a portal to Rankings sorted by ranking quanititate value color-coded by Vehicles and set up so I could scroll from publication to publication and watch the colors change

Could make easy subsummary reports showing aggregate data with summed up figures per the category, like Ranked VeryBest to Best then per the Vehicle, how many for each Vehicle got rankings within that range, then the next Vehicle, sorted alphabetically, subtotal for VeryBest to Best at the bottom, then onwards to the next ranking category range etc. Or subsummary by manufacturing corporation of the vehicle so I see the breakdown of Toyota products then Nissan products etc, sorted as I choose.

I’m not saying you can’t do all that in Excel, it’s just that it’s so easy in FileMaker, whereas Excel “thinks flat” even though it can do relational. ETA: you could make separate spreadsheet tabs in Excel for each of the tables I describe above and use that as your starting point in Excel. But you might end up immersed in pivot tables and macros trying to graph or display the relational data. Which is fine if that’s what you’re up for, you’d end up with a nice display of your data and a better grasp of Excel than the vast majority of people have.