[lwptoc]

Amazing lesson of Sorting in Excel with plenty of examples

In this lesson you will learn to sort data in Excel. We have many examples of sorting for you to make your spreadsheets better and more useful. Remember that sorting date is not only for better visibility. It gives also more opportunities and helps you to get much more. You will get to know it from below Excel article.

Sorting in Excel

To learn how to sort data need data. Let’s use the table below. Excel sorting table You need sorting because that’s not look professionally. Sort sales descending.

Sorting starts marking data that you want to sort. In this case, select the range of cells A1: C11. Then go to the Data tab on the ribbon and press the Sort button. Dialog box appears. Excel Sort dialog box Choose sort by Sales order Largest to Smallest value. Don’t forget to tick My data has headers check box. Excel Sorting You can also to add next level of sorting. Click Add Level and sort Regions alphabeticaly A to Z.

Excel Sort second level

Sorting finished. Well done! Excel sorting done

How to Sort by More than Three Columns?

The sorting of more than three columns can easily be performed. First you need a data that looks like this: data table 1. Click on first cell of the data (manager). click first cell 2. Click on Data (1), and then press sort (2). ribbon data sort 3. Choose “Year of Employment” in sort by, and then press ok. Year of Employment 4. Repeat, but in Sort by, choose manager (or the most important column) (1), and then click Add level (2), to choose the second most important column, and then press ok. add level Note: Keep repeating this step until you have sorted the columns from most important to least important. It is important to acknowledge that, if for instance cells in a column would exchange position for the sort to take effect, then Excel would ignore them.

After following the steps above, the sort would now look like this: data sorted

Sorting by color

You can also sort colors. You may need that especially when you like to color your spreadsheets like I do. I must admit it sometimes makes some problems in understanding the sheet but also gives me additional opportunieties.

To sort colours in your table first prepare colorful data table like this one in the picture below. data table color 1. Click Data (1), and then click on sort (2). ribbon sort Note: You should first press the AZ beside the small picture showing ZA/AZ, so it makes easier to move ahead.

2. Choose the column (1), then choose color in sort by (2), and then order (3). sort by color Note: After following this step, press Add level, sorting by the same thing in column, cell color, and choose color you’d like to have on the second place. Continue until you’d add all the colors in the data, and then press ok.

How to Sort by Row?

Who said you need to sort only columns? Teach yourself how to sort by row. This trick maybe useful for large spreadsheets with many columns.

First prepare you data table. Here is an example. data table to sort by row Go to the ribbon to the Data tab. Click Sort button. ribbon sort button Sort dialog window appears. Click Options button. Sort dialog window Click Sort left to right. Sort Options sort left to right Now decide what do you want to sort. I sorted Row7 (which is Total in my table) by Largest to Smallest order. Way how to sort Here you can see how Excel sorted the data. Total row is sorted descending. Data sorted by row This is how to sort by row within Excel.

Sorting a list of words by character count

Excel lets to sort data not only alphabetically but also based on cell colour or font colour. Probably you didn’t know but you can also sort based on count of characters. To do this you will need some data. Let the picture below to be the example. Sort Character Count Table In the B column write formula which refers to value in A column. In B2 formula will be: =LEN(A2) Sort Character Count Formula Drag the formula down. Sort Character Count Drag Now you see count of characters of words in A column in B column. Sort Character Count Value Next highlight whole table and go to Ribbon > Data > Sort. Tick My data has headers and sort by Charcter Count Smallest to Largest. Sort Character Count Sort Excel sorted your words from shortest to longest. Sort Character Count

How to Sort Out Blank Rows?

The sorting out blank rows comes down to one thing, how many they are, and that if they should be there or not. But first, have a look at this rows with both filled and empty rows: sort out data table Mark the whole data. mark whole table Press CTRL + G and click Special special Choose Blanks blanks Press Home (1), Choose a color with Cell Styles (2). cell styles Note: Give the blank cells a unique color. It is recommended that you’d use only one color in the actual data.

Mark the whole data again. mark data again Click on Data (1), and then on Sort (2). click data sort Choose a category in Sort by (1), choose cell color on Sort On (2), No Cell Color in order (3), On Top (4), and then press ok. no cell color Choose all the colored rows, right click on the marked rows (2), and choose delete (3). delete blanks

Template

You can download the Template here – Download