How Can We Help?

Search for answers or browse our knowledge base.

< Home
Print

How do I complete conditional formatting in Excel and then sort using those conditionals?

UpdatedOctober 3, 2024
Tip

The example below shows a small sampling of students. This process works the same for a small number as well as a large number of students. Also in this example we will be using "greater than 2" to indicate that a student has 3 or more tardies. 

STEP

Open the file downloaded from PowerSchool

This file can be found in your downloads folder. It will be named named 'student.export'. If you have downloaded multiple files from PowerSchool (or never clean out your downloads folder), then the name may be followed by a number. By default the file is a '.text' file which can be opened in Excel. If your computer is set to open .text files in Excel you can just open the file. If Excel is not set as the default, then you can right-mouse click and choose "Open with ..." to open the file with Excel.

STEP

Highlight the columns where the data to be evaluated is located

STEP

With the columns selected click 'Conditional Formatting'

STEP

Choose 'Highlight Cell Rules' and then 'Greater Than...'

STEP

Enter the value in the box and click 'OK'

STEP

All of the cells in those columns that are greater than 2 have now been highlighted

STEP

Click anywhere in the document so that the columns are no longer selected and then click 'Sort & Filter'

STEP

Click 'Custom Sort...'

STEP

Choose the first column of tardy data, then set 'Sort On' to 'Cell Color', and the 'Order' to 'On Bottom'

This command is telling excel to look at the column of '0th Period' and sort the cells that have been colored red together and the ones without color 'on bottom'.

STEP

To add an additional sort command click the '+' in the bottom left-hand corner of the window

STEP

Once you have added in all the sort commands click 'OK'

You can sort by one column, 2 columns, or more. Here we are sort by all 8 periods. 

STEP

The cells with no color are now at the bottom, select those rows

STEP

Press delete on the keyboard and now the unnecessary data is no longer showing

If you would like you can now sort based on name or grade. To do this you can complete another custom sort with different parameters or you can sort by any single column.

Table of Contents