I’ve got an Excel spreadsheet that I’m creating for folks that are not especially computer literate. There is information in cells A8:Z32 that they may want to sort; they may want to sort by the data in almost any column in that range. I’d like to set up a way that they can do so automatically–perhaps a drop-down box that lets them choose the column by which they’ll sort, or a button above each column allowing them to sort by it. I’d also like to set up columns A and B to be secondary and tertiary sort columns for any of these, but that’s not necessary.
You can highlight the entire table and then use the “Data” menu to select “Filter”
This sets up an autofilter table with a button at the top of each column. When the user clicks the button it gives him the oportunity to sort ascending or sord descending the entire table based on that column’s values. The button also gives them filtering options which you may want to explain to the user as its a pretty nifty ability.
This is also what I was going to suggest. This is about as simple as you can make it with built-in features. The only way to make it easier is writing VBA code, but it would be far easier to train your users on this one little feature that provides so much bang for the buck.
Sweet! Bubbadog, that’s exactly what I needed, and it’s way easier than I expected it to be. Many thanks!
Santo, that sounds helpful for myself, but I’ll be distributing this spreadsheet to several dozen teachers across the district. Customizing my toolbar wouldn’t do anything to theirs, would it?
You’re right. It’s possible to add VBA code which customizes the user’s tool bar but that would require VBA coding and possibly instructions to the user to enable it.
The auto-filter method doesn’t require coding. It’s ready to go as soon as the user opens the spreadsheet.
The only concern is that the auto-filter can appear to the user to erase data if they aren’t familiar with it.
One solution is to state a hint at the bottom of the table giving instructions on turning off the filters so that they can see the whole table.
I’ve got 25 rows of data, with a different record in each row. I’d like to make a nicely-laid-out worksheet that, for each record, displays four different line graphs. For example, one such page would display a line graph for cells c8, f8, and i8, with cells c36, f36, and i36 providing a benchmark line in the graph (that is, the benchmark line will be the same cells for all records). When I do the next record, it’ll be c9, f9, and i9, with c36, f36, and i36 maintaining the benchmark line.
I’ve made a mockup one with references to the specific cells necessary. But doing this for all 25 records will be kind of a pain in the butt, and will make the spreadsheet unnecessarily bloated. Is there any way in Excel to automate this–to make one set of the four graphs, and have it feed all 25 records into it? I know how to do it in Access, but dont’ know if it’s possible in Excel.
Is this from the same table as your first question?
I’ve created graphs before that respond to input choices. I did it by copying the chosen data from the table into a separate area (I usually create a separate sheet in the workbook and call the page “graph data”). Then I figure out a way of copying line 8 from the table to the graph area.
If you know VBA you could create a small program that does the copy/paste based on your choice.
But another way (and much faster) is to use a function called Indirect. If you aren’t familiar with it spend some time playing around with it.
What it does is grab the data from a cell that is referenced in its argument.
Ex: Indirect(A5) - will put the value of the cell A5 into the cell with the indirect statement.
Pretty boring until you realize that you can also use a formula.
Ex: Indirect (“A” & C5) will display the value of A43 if the number 43 is in Cell C5. Change C5 from 43 to 50 and your indirect statement(s) will now display A50 values.
So you could use indirect statements in the graph series input field in place of C8,D8,I8 from your example and by changing the number in one referenced cell you can choose a different row of data.