QUICKLY CUT AND INSERT CUT CELLS
Consider the data in Figure 1. Twelve months appear along the left side of the data. Sales for five stores appear in columns B to F. The total sales for the five stores appear in column G. But the stores aren’t in the “correct” sequential order.
One way to reorder them is using the Insert Cut Cells command. Let’s say that you wanted to move Store 1 from column C to column B. You would first select C1:C13 by clicking in C1 and then pressing Ctrl+Shift+Down Arrow. You could cut the data by using Ctrl+X. Move the cell pointer to column B using the Left Arrow. Then use the Insert Cut Cells command (press Alt+I, followed by e) to insert the cut cells to the left of the cell pointer. This command will shift the existing data in column B to the right to make room for the data on the clipboard.
I was watching a YouTube video where Sumit Bansal of trumpexcel.com proposed a faster way to rearrange the columns. The method involves selecting cells C1:C13 by placing the cell pointer in C1 and then pressing Ctrl+Shift+Down Arrow. Then hold the Shift key while you use the mouse to drag the left edge of the selection rectangle and drop it to the left of column B. This is an interesting technique: Holding down the Shift key during the drag and drop tells Excel that you want to cut the data from the original location and insert it to the left of where you drop it.
Even with this faster method to cut and paste, however, you’ll have to repeat the steps to select and move the data three more times—first, to move Store 2 from column E to C, followed by moving Store 3 from column F to D. At this point, Store 4 would be in column F and Store 5 would be in column E. So you need to repeat the steps one last time to move Store 4 from column F to column E.
Sorting left to right would arrange the columns in a single operation.
USING THE SORT DIALOG
It’s probably rare for you to open the Sort dialog in Excel. Most of the time, you probably sort using the AZ or ZA icons on the Data tab in Excel. For me, the only time that I need to use the Sort dialog box is when I need to sort based on a custom list.
Near the top of the Sort dialog is an Options button. Click the Options button to reveal the two settings in the Sort Options dialog. First, you can specify that the sort should be case- sensitive.
The other setting is where you can change the Orientation from “Sort top to bottom” to “Sort left to right.” Choose Sort Left to Right and click the OK button to close the Sort Options dialog.
After choosing to sort left to right, the Sort By drop-down menu in the original Sort dialog changes to include choices such as Row 1, Row 2, and so on. Choose Row 1 in order to sort the data left to right based on the store names in row 1. The result is shown in Figure 2. By executing a single Sort command, you have successfully rearranged the columns so the stores are in the correct sequence.
Not only is the left-to-right sort quicker, it also preserves the SUM formula in column G. Before the sort, the formula in G2 was =SUM(B2:F2). After the sort, the formula is unchanged and still totals the sales for the five stores. Had you rearranged the columns using Cut and Insert Cut Cells, many of the operations were dragging a column outside of the original sort range. In our example, the Total column had changed to =SUM(F2:F2), leading to an incorrect total.
SORTING WITH A FORMULA
For Microsoft 365 subscribers, the relatively new SORT function allows you to sort a range with a formula. The optional fourth argument in the SORT function allows you to specify that you’re sorting by columns instead of sorting by rows. A formula such as =SORT(A1:G13,1,1,TRUE) will sort the data by row 1.
This method isn’t as robust as sorting the original data. The SORT function will return the results in General format, meaning the dates will appear as serial numbers and the SUM function will be converted to values.
February 2022
