In this article we invite you to put aside the tedious practice of marking the cells you want to highlight one by one; Instead, you will automate the shading of Excel columns or rows according to their content , using tools such as conditional formatting, residual formula, among others.
Index( )
- How to shade an entire row or column without any macro?
- How to set different color skip rows?
- Where can you change the format of rows in Excel?
- Use the formula ‘RESIDUE’
- use a filter
- How can you use conditional formatting on an entire row or column?
How to shade an entire row or column without any macro?
To shade an exhausting amount of columns or rows automatically without resorting to time-consuming methods, which we probably don’t have. It’s necessary that:
On the one hand, if what you need is to shade a column, position yourself in the top cell of your column, press simultaneously (Ctrl + Shift + down arrow) and shade with fill color. On the other hand, if what you want is to shade an entire row, go to the first cell of your row, press simultaneously (Ctrl + Shift + right arrow) and color the cells of the row with ‘fill color’ .
How to set different color skip rows?
If what you want is to organize your table with different colors according to its content , you just have to: 1. Select the table without the header. 2. On the Home tab, choose the ‘conditional formatting’ option. 3. Click on ‘new rule’, set the option ‘format values where this formula is true’, 4. Define the shading color in ‘format’ and write the condition you want to color specifically in the description of the rule.
The condition can be determined to the numerical values of a cell, for example if e2=3, it will be painted according to the color you selected in shading. The process can be repeated as many times as you want depending on the content of your table in Excel and it will be shaded with the colors you choose.
Where can you change the format of rows in Excel?
Similarly, you can change the format of the rows in Excel just by using tools, such as: the Residual formula or by using a filter that will allow you to group your data according to its content.
Use the formula ‘RESIDUE’
The REMAINDER function in Excel aims to find out if a number is even or odd in a cell by dividing by 2; for which it must be formulated as follows =RESIDUO(CELL;2)=0, making all even numbers give 0 and odd ones 1.
If you want to color the parts, go to the home tab, click conditional formatting, new rule, in the pop-up window choose ‘ use a formula to determine the cells to apply formatting’
In format define a color and write the formula in ‘format the cells where this formula is true’, for example: =A2 (Cell where the Residue is) =0. This will automatically mark the even rows of your document.
use a filter
If what you need is to group the rows by a specific color , do not despair, it is very easy to do it automatically with the filter tool, you just have to: 1. Go to the home tab of your Excel document. 2. Click on the ‘sort and filter’ tool. 3. Choose ‘Filter’. 4. On the header there will be some icons with a downward arrow, press the icon and select the value within the drop-down list that you want to filter.
How can you use conditional formatting on an entire row or column?
Next we will show you how to use a conditional format that will allow you to highlight the entire row or column if a condition is met. For example: If you have an inventory control table in Excel for your company (product / unit / stock) and you want the entire row to be marked with a color when the stock of products runs out, you only have to:
Go to the top ‘home’ tab of the document, select the table you want to condition without headers , choose the ‘conditional formatting’ option, click ‘new rule’, choose ‘use a formula to determine the cells to apply a format’, press the ‘format’ button to define the shading of the row.
Next, choose ‘format values where this formula is true’ and set the condition. For example: I want row A2 to be shaded when row D2 is equal to 0, to do this select the cell (=$D$2=0) with the cursor, edit the formula removing the dollar sign between D and two.
Otherwise, the formula would remain fixed in cell D2, preventing the other cells from identifying whether the condition is met or not.