The right sort


Q: I receive large amounts of data that need to be sorted both vertically and horizontally in Excel. I typically accomplish this task by transposing the data onto a separate worksheet (using the Paste Special, Transpose command), where I sort the data vertically (which is actually a horizontal sort because the data has been transposed). I then transpose the sorted data back to the original worksheet and sort the data vertically to complete the double sort task. The data we receive is never the same size and, often, extra columns are included—ruling out an easy macro solution. Is there a way to make this task easier?

A: Excel 2010, 2007 and 2003 all provide the ability to sort top to bottom and left to right, with Sort top to bottom as the default setting. To use the Sort left to right option, select the data to be sorted, then select Sort from the Data tab (or menu). In the Sort dialog box, click the Options button, click the Sort left to right radio button, and then click OK.

Define the sort criteria as you normally would, then click OK to complete the left-to-right sort. Once you have completed this task, sort the data again, this time changing the option back to Sort top to bottom. Using this approach, you will avoid the extra tasks of copying, pasting and transposing data twice between two worksheets.

More from the JofA:

 Find us on Facebook  |   Follow us on Twitter  |   View JofA videos


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.