Free Microsoft Excel 2013 Quick Reference

Default Automatic Sorting of Data Table

What is the best approach to sort a 10 column data table by its column headings. The column headings would also be shown in a separate dropdown list, once the column heading name is selected with the dropdown list I would like the data table to sort on that column automatically. Preferably do it without VB, this may eventually run on a Mac, I'm told VB won't run on a Mac. But would use VB if necessary, since Windows is the main platform.

Post your answer or comment

comments powered by Disqus
What is the best approach to sort a 10 column data table by its column headings. The column headings would also be shown in a separate dropdown list, once the column heading name is selected with the dropdown list I would like the data table to sort on that column automatically. Preferably do it without VB, this may eventually run on a Mac, I'm told VB won't run on a Mac. But would use VB if necessary, since Windows is the main platform.

What would be the best way to update the dropdown list automatically if new columns are added to the data table?

What I need is Realtime update of Excel Sheet 1 datas into Sheet 2
automatically by sorting of Sheet 1 rows based on an ID Number and date.


I have a few data tables which depend on each other (from left to right). Now only the right result emerges when I hit F9 1 to 3 times (calculation option is automatic incl. tables of course). So it seems to me that the calcluations order of Excel in this case don't follow a logic path (like it does when having a sheet full of "normal" formula's (and arrays)).

So the question is if someone knows how calculations of data tables are handled in Excel. I could not find anything on the internet about this subject.


I need to sort 3 column ranges;
I put names in column W, SSN's in column X, and Codes in column Y. The range of these columns run from 1 thru 21 with 1 being a header row. What I need to happen is lets say I have the name Sam in W2 with his SSN in X2 and a code letter of 3 in Y2. If I type Adam in W3 his SSN in X3 and a code letter of 4 in Y3, I would like it to automatically sort the data in all three columns based on the last entry in column Y. Simply said I'd like to have the 3 column ranges to auto sort based on column Y. Bearing in mind that sometimes there may not be a code in column Y. I know somebody out there has the knowledge to make this happen however, I don't.

I figured out a long time ago that:
"Standing in the garage all day dosn't make me a car."

I have set up a simple worksheet as a shipping log. Date, sent to, mode,
tracking and a refernce column. I currently sort by columns A (date)
descening and B (last name) ascending.
When I enter a new line of shipping information, or several, is there a way
that the spreadsheet will automatically sort the data into the correct order?

Hi All,

I am extracting data from a database using a Data Table. Is there a way to sort the Data Table with Macros? The normal error message when you attempt to sort is that you can't change part of a Data Table?
Thank You.


Is there a maximum number of data tables that I can import from MS Access into a single MS Excel file.

I have successfully imported four (4) tables into four (4) different worksheets, but when I follow the same methodology to import the fifth and sixth tables, only the row headings are imported [in bold].

Can anyone help?

Thanks, glenn

Hi I want to create a chart use only a part of data table (data legend) but I
want the data table (data legend) to show all data.
Because data is too much I just want the chart to show only two of them but
I want to see the other data values on the data table.
Is that possible ??


I would like to disallow sorting of data in a worksheet as we have lots of
hidden data that will get messed up.

Could anyone help me with the code to create a simple message box explaining
that no data sorting can be achieved and then, once the user has closed the
message box, not allow the sort itself. Ideally, I would like the message
box to appear when the user clicks on "Data - Sort" in the menu bar on Excel.

Many thanks and Happy New Year to everyone.


I need to create a table of results (points) with automatically sorts in descending order when data is changed. This should then show the 'leader' with the most points - however, I also need the table to automatically insert a 'position' so the 'leader' will be shown at the top of the table together with the total points and a position of '1' or '1st'

I am a newbie in excell coding but familiar with the standard formulae.I have designed a workbook with a sheet that has timing of different stages for our local rally .

Eg:Cell C5 and Cell D5 are the cells where the time is in put and C5 is the start time of the stage and D5 is the end time of the stage, the times are all input as numbers without the " : " for the hh:mm:ss.
The data is Input for more than 50 Cars in there start order.

On The same Sheet C 72 and D 72 converts the number value to time format with the formulae.


To compute and get the Difference of the total time Taken for the Car in a section would be D72-C72 which results are aquired on cell E 72 and also copied on Cell E5.

On the same sheet we have ranking applicable for each stage time where i have used F72=RANK(E72,$E$72:$E$107,1). which runs accross all teh 50 names.

Now i have several stages like these which are on the same sheet and the final accumulated time is added from all the competitive time and the ranking is done.

I need to create another Excell Sheet or Workbook that can have a macro to automatically arrange from the shortest time to the longest time and rank the results after the total time has been computed.

Currently i have another Sheet on the same workbook that copies each stage time in each cell difference and adds the row with total stage time taken and ranks it. After that I than have to manually sort the entire sheet so that i can have the ranking arranged from top to bottom.

I would like someone to assit me on how to come up with an automatic arranged sorted results without sorting the data manually.



How can I automatically sort customer data as follows:

(1) Data in four cells across four columns are in credit, then:

(2) Sort results of (1) above from highest credit to lowest credit.debit.


Customer Name: ! month 3 months 6 months 12 months

Joe Bloggs 10000 50000 100000 10000000
Mary Smith 3333 11 12345 33333



Hello -

Apparently I posted about this and then something happened and I didn't follow through. I apologize for that - I don't remember what happened, but I seem to have the same need and no code anywhere in my files, so...

Using Excel 2003
Windows XP SP2

I would like a macro that will copy info from multiple lines into one line based on a count field or some sort of compare functionality.

******** ******************** ************************************************************************>Microsoft Excel - loop test.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2B3B4B5B6B7B8B9B10B11=
ABCDEF1IDCountLocationData 1Data 2Data 3211abc89.8352.772.56320abc56.391.5192.22430abc82.299.8675.98541jlk79.884113.41650jlk68.6179.0328.8760jlk86.4383.8867.18870jlk38.1736.5799.48981xyz23.1594.2681.161091pdq24.2335.0148.2611100pdq22.4859.5668.32Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

ID - a unique ID that doesn't really matter here
Count is either 1 or 0 - 1 is the first in a series of the next column, 0 represents duplicates of the value in the next column (not really as confusing as I am making it sound)
Location- the unique identifier, the thing being counted
data1, data 2, data 3 - these are the pieces of data associated with each record. so some locations have 1 set of data and some have multiples sets of data.

I would like to be able to copy the multiple sets of data into the first line and then delete the remnants like the table below.

******** ******************** ************************************************************************>Microsoft Excel - loop test.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA15=
ABCDEFGHIJKLMNO15IDCountLocationData 1Data 2Data 3 1611abc89.8352.772.5656.391.5192.2282.299.8675.98 1741jlk79.884113.4168.6179.0328.886.4383.8867.1838.1736.5799.481881xyz23.1594.2681.16 1991pdq24.2335.0148.2622.4859.5668.32 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

I realize that the header will not appear for each set (unless I pre-type it in - that isn't a problem).

Thanks for any help I can get!


I am in need of assistance to automatically sort a league table for a wappenschaw competition.

I record the individuals scores on the basis of :

Points Won
Shots For
Shots Against

on rows 1, 2 & 3 repeating the sequence for all the participants over a 22 week series. To bring this into a concentrated listing I have then entered the data in an alpha sort with the players name in Col A. Played in B, Points in C Shots in D and Rank in E on a scores tab.

Finally I wish to automatically sort the final rankings of the players descending from the player with the highest number of points. It is here that I struggle and need your assistance. I wish to display the information as Player in Col A, Played in Col C, Shots in Col G and Points in col H. Cols D E & F will show the numbers of wins, draws and losses and dont figure over all in the calculations.

I am looking for a formula to auto sort on the basis of col H followed by col G and all other related data across cols A to F. Currently the range of rows are No 3 to 57.

It would be appreciated if I could be provided by an early solution as things are getting a bit messy and I would much prefer to provide a properly sorted table for the club.

Thanking you in anticipation of your kind assistance

Excel 2003:
I have a large amount of data going into a pivot table (i.e. 90 entries).
When I drag to the data area, the rows default to "count of" data item 1,
data item 2, ... data item 90.

I then have to select each one individually and change the field settings to
use "sum of" instead of "count of".

How can I set the default to be "sum of"? Or how can I select all 90 rows
and with one choice change them to be "sum of"?

Thank you!

I have a spreadsheet that has a list of high school students in a club, with their grades, club meetings they've attended, and service hours they've done each semester.

I currently have it sorted first by grade, and then alphabetically by last name within each grade. There are a lot of members who need to be added to the spreadsheet, and membership will continue to change as seniors graduate and freshmen come in. So is there a way that when I add a student, the list will automatically resort (by grade and name)?

Also, what would be the easiest way to add new students? This spreadsheet will be primarily edited by other people, who have little experience with excel. Perhaps some kind of data form?

In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. I need your help to automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc. Can anyone help?



As you can see from the attached spreadsheet. I have a page called Pattern Etch where we enter the data for all the processing done in a single machine.
Each device type is slightly different, and therefore has a different calculation fasctor for its etch time.

This all weorks fine.

What I had previously was another spreadsheet with the pages b and g in it. This is where the factors are calculated and updated from.

I'd like to make this update automatic, and suspect the easiest way is to combine the two spreadsheets, as in the attached version.

Can the sorting of the data from the patternetch page to the apprpriate pages, b and g be done automatically?

If so can it be done for many pages as I have cut down the spreadsheet, I actually have 50 variants, each will need its own page (as for b and g).

Hope thats not too confusing......the spreadsheet should make it clearer.

Any help greatly appreciated.



I have data table and created pivot using it. Is it possible to update the pivot table when I filter in Data table?

If I filter Jan in the Data Table of sample file, it should show only the pivot for the month of Jan. I dont want to filter it in Pivot Table.

Any help will be appreciated..


Hi to everyone.

I noticed a strange behaviour in excel 2007 data tables. It doesn't happen in excel XP/2003. I wonder whether something has changed or perhaps it's a sort of bug. Let's cut to the chase.

I set up a one-way data table that generate (for example) an income statement for 10 companies (in this example I would have the formulas in the leftmost collumn and the name of the 10 companies in the top row). Then in one cell I put a simple formula that sum an item of all ten companies (say EBITDA for example summing all items in a row). The problem is that this cell doesn't get updated (ie it doesn't perform the sum) even if I press F9. The only way to have excel calculate the sum is either to enter the cell and press enter or re-open the file. Very strange.

Any suggestion to solve this ?

PS: sorry for my english



I am trying to figure out how to sort a data table beneath an excel graph.

There might be as many as 70 lines in the graph data table that need to be sorted.

I need to be able to sort them from highest to lowest in value according to the furthest right column in the graph data table which is the latest month in a 13 month rolling monthly report.

I am not looking to change the data source from which this graph is derived. I need to just automatically sort the data in the Graph Data Table. I also do not want to create a pivot table.

Does anyone know if there is any VBA code which can do this.

I currently manually move the data in the graph data table by right clicking the graph, selecting data, then click on each line I need to move up or down depending on the result in the new month which takes a very long time when you are doing this for 50 graphs a month.

Any help or suggestions would be much appreciated as I have scoured the web and it does not appear that anyone has asked this specific question before.

Hi there, could anyone help please.

In the attached spreadsheet I need to be able to automatically take imported data and display it in a different layout. Columns A to D on the IMPORTED DATA tab show the raw, imported CSV data. Using the value in column A on the IMPORTED DATA tab I wish to display columns A to D in each of the 15 tables on the NEWLAYOUT tab. I have filled in all of the data manually to show what the end result should look like.

Can this be done, either from a formula look up or by using a macro?. The conditions of the spreadsheet are as follows:

1) The data needs only to be filled in once when the spreadsheet opens. There is no requirement for the data to be dynamically updated if any of the values change in columns A to D. This said, it would not be a problem if it did as long as it didnt trigger a refresh data external import after every sheet change.

2) It must be triggered by the opening of the spreadsheet rather than a shortcut key or clicking on a worksheet textbox / button. If a macro must be used to achieve this then it would not be a problem to include the data import in the same macro.

3) There will never be more than 10 entries per volume group so there is no risk of data "overflowing" the table to the one below it.

4) The number of entries imported will vary, just as the number of entries per volume group will vary

5) The new layout (columns B to J) will be for display purposes only and will not be edited therefore these cells can contain equations.

For info, I'm currently using Excel 2003.

Many thanks,



I need to sort the data in excel using three columns.The data has to be sorted first by columnA and then by columnB in ascending order and then by Column C.IN ColumnC the first three characters has to be taken into consideration.Suppose we have ABC01 and AB001.Then the third character with the alphabet ie ABC01 should be placed ahead of AB001.

Am able to sort the by coulumnA and ColumnB by Sorting them by ColumnC is bit confusing.
Any ideas for that.

ColumnA ColumnB ColumnC A 190 ABC01 C 180 AB021 C 180 ABC01 B 180 AD236 E 180 AV002

The output should be

ColumnA ColumnB ColumnC A 190 ABC01 B 180 AD236 C 180 ABC01 C 180 AB021 E 180 AV002

I am working with an Excel file (file about 4Mb, using Excel 2007) with various complex calculations and charts. Amongst others, there are 20 data tables in the file on a hidden sheet.

Anyhow, the problem is that the calculations do not happen anymore: I have one cell that I change, which should lead to the updating of all my 20 data tables:
-If I put everything to automatic calculations, the data tables will only update to nr 3-5 (seems random when the calculation will stop), leaving the other 15-17 data tables as they were.
-When I change my file to Manual Calculation and try to calculate, the same thing happens.

The weird thing is that the calculation is carried out perfectly (=every data table is recalculated) when I save the file...

Does anybody have an idea what could cause this?

No luck finding an answer? You could always try Google.