Jun 21, 2018 - If you haven't tried out the conditional formatting features of Excel before. One way to use this feature is to cause Excel to shade every other row in your data. As to how this formatting can be done using Excel for Mac v15. For example: I want all odd numbered rows with white background, and all even numbered rows with a light green background. Menu: Format -> Styles and Formatting Result style, right-click and modify; Background tab, and chose light green color. Excel’s options for shading alternate rows are limited and sometimes ugly. Here’s how to take full control and design the way you like I sometimes wonder whether Microsoft spends so much on its programming budget it has little left over to spend on designers. This thought often crosses my mind when working with Office sample files, templates and styles. Take, for example, the auto-formats supplied with pre-2007 versions of Excel. Who designed these things? Not only are they ugly, they also don’t address basic formatting needs such as coloring every other row in order to make a table more readable. Yes, there’s one auto-format option which gives you this – sort of – but it comes with unattractive headings and footer formatting and it’s inflexible. The solution? Do the job yourself, using conditional formatting. Conditional formatting lets you apply formatting based on the value contained in a cell or based on the result of a formula. Here’s how you can use it to apply shading to alternate rows: 1. Select the range of cells you wish to format. Click Format -> Conditional Formatting. In the drop-down box, select Formula Is. In the box type: =MOD(ROW(),2)=0 5. Click the Format button and then click the Patterns tab. Select a color for the shaded rows and click OK twice. There you have it: a table with banded colors. If you find you don’t like your chosen color, simply select the range once more, open the Conditional Formatting box and choose another color. The real beauty of this method is that even if you delete a row in your table, the banding will be maintained automatically. FORMATTING IN EXCEL 2007 If you’re using Excel 2007, you have a whole range of new formatting tools at your disposal, including the alternate row shading offered through the Format As Table options. ![]() I’ll be writing more about these options in the future. Nevertheless, you can still roll your own conditional formats if you choose: from the Home tab, click Conditional Formatting -> New Rule -> Use A Formula To Determine Which Cells To Format and then use the same formula described in Step 4 above to create banding. CONDITIONAL FORMATTING EXPLAINED If you’re not familiar with conditional formatting, a little explanation is in order. Torrent movie players for mac. Conditional formatting is applied to a cell when the condition you specify is true. The condition may relate to the cell’s contents. For example, you could set all cells containing negative values to be displayed in a red font. Alternatively, you can use a logical expression or formula which evaluates as TRUE or FALSE, as we did in the example above. In Excel, a 0 value equates to FALSE, while any number greater than 0 equates to TRUE. THE MOD() AND ROW() FUNCTIONS The formula we used contains two functions, ROW( reference) and MOD( number,divisor). The ROW function returns the number of the row contained in reference or, if the reference is omitted (as in our example) it returns the number of the row containing the function. So, if you place the formula =ROW() in cell A9, the value returned is 9. The MOD function returns the remainder of the number divided by the divisor. So, the formula =MOD(7,6) returns a value of 1, while =MOD(6,7) returns 6. EVALUATING LOGICAL EXPRESSIONS Now take a look at the formula we used to create shading on every other row.
0 Comments
Leave a Reply. |