Make Money Online Queries? Struggling To Get Traffic To Your Blog? Sign Up On (HBB) Forum Now!

5 Ways to make your Excel Spreadsheet Professional

CONNECT WITH HBB ON SOCIAL MEDIA

Adding formatting to your Excel spreadsheet makes it look professional—but manually highlighting or changing text colors can take hours if you have a large spreadsheet. And if your data changes regularly, it’s nearly impossible to do it by hand.

With conditional formatting, you can automatically apply the formats you want to a specific subset of cells. You just need to know how to tell Excel which cells you want.

Here are five ways you can use conditional formatting to make your spreadsheet look more professional.

1. Highlight Alternating Rows

This is a simple one, but highlighting every other row goes a long way toward improving the readability of your spreadsheet.

First, highlight the data you’d like to format (in most cases, this will be all of the data in a table). Then click Conditional Formatting in the Home tab of the Ribbon and select New Rule:

Conditional New Rule

In the Use a formula to determine which cells to format tab, enter this formula:

=MOD(ROW(),2)=1

New Rule

Then use the Format button to select the fill color you’d like for your rows.

Once you have, hit OK, and you’ll have nicely highlighted alternating rows:

Alternating Highlights

In our case, we highlighted odd rows start with row #2 to preserve the fill color on our header row. To highlight even rows, change the formula to this one:

=MOD(ROW(),2)=0

You can adjust the values in the MOD equation to highlight every third row, every fourth row, or any other interval that you like. You could also create multiple highlighting rules to create highlighted rows with different colors.

2. Use Dynamic Conditional Values

In most cases, conditional formatting works with a single value. You might highlight all of the cells that have a value above 10,000, for example. But by using dynamic conditional values, you can change the rules for formatting on the fly.

Let’s look at an example. In our spreadsheet, we have a list of sales by a number of salespeople, as well as the average sales value over the course of the year:

Sales Average

We can use conditional formatting to highlight values that are $10,000 above or below the average. And by using dynamic values, this will work even when the average changes.

First, we’ll highlight the relevant data (this doesn’t include the salespeople’s names, so we’ll start with B2). Then head to Conditional Formatting > Highlight Cells Rules > Greater Than.

Greater Than

When you click on it, you’ll be asked to enter a value. Instead of entering a number, though, we’ll click on a cell to create a cell reference. In our case, we’re clicking on H3:

READ
The Best Table Goodies For Business Conferences

Dynamic Greater Than

Now, every cell containing a value larger than the value in H3 will get highlighted. That will be true even when H3 changes.

Finally, let’s add $10,000 to that value:

Mixed Greater Than

We’ve selected green fill with dark green text to highlight these.

Now, to do the same with values $10,000 less than the average, do the same thing again, only select Less Than in the Conditional Formatting menu, and use ($H$3)–10000 for the value:

Dynamic Formatting

Now any cell that’s $10,000 above or below the average will be highlighted, no matter how the average changes. (For readability, you may want to remove the alternating row highlights for an operation like this.)

3. Highlight a Row Based on a Single Value

Highlighting a single cell is great, but what if you want to highlight a whole row? In our example of the sales records, you might want to highlight the entire row if an employee’s total sales reach a certain value.

We’ve added a column for the total annual sales by each employee. Using the standard greater-than rules would highlight only the cell that contains the value.

To highlight the entire row, we’ll need to select the entire table, from B2 through the end, then use the Conditional Formatting menu to create a new rule:

Highlight Row

Because we’ve selected the entire table, the format will be applied to the whole row. But specifying $F2 as the reference cell means Excel will only look at that cell.

Note: the dollar sign is important here, because it tells Excel to only look in column F. There’s no dollar sign before the 2, because we want it to check out all of the rows.

Hit OK, and every salesperson who sold over $250,000 for the year has been highlighted:

Highlight Row

You can combine this with the dynamic highlighting above to highlight rows based on automatically updating values.

4. Quickly Apply Color Scales

Color scales give you a lot of information very quickly—but they can require numerous conditional formatting rules to get right. Instead of going through that process, you can use Excel’s built-in color scale rules to get a head start.

Just select the data you want to highlight and head to Conditional Formatting > Color Scales:

Color Scales

Select one of the color scales from the menu, and Excel will apply it to your data.

Color Scaled Data

There are several different scales that you can use, so mouse over them to get a preview of what they look like and choose the best option for your data.



By

Ram Kumar blogs at DeviceBowl. He is a graduate in Computer Science and Engineering. Addicted to Blogging and Coding.

Want to discuss your queries and interact with experts? You can connect with HellBound Bloggers (HBB) Facebook group for free!

Join HellBound Bloggers (HBB) and get Instant Updates. We'll also notify you with Great Deals, Discounts and other Interesting Tips. We won't SPAM or SHARE your Email Address with anyone.

Thank you for reading! We welcome and appreciate your comments, but at the same time, make sure you are adding something valuable to this article. If you have any serious queries, suggestions or anything related to this article, feel free to share them, we really appreciate that.

But, if your blog comments are a random "Thank you", "Useful Post", or anything that doesn't actually upscale the article, then we'll be removing them and it won't be appeared below. Thanks for understanding and thanks for connecting with us. If you want to give us any feedback or report any errors, you can kindly contact us and we'll revert back soon.

  • Comments
  • Facebook Comments
  • Comments

    Do you have any queries? Want to share something?

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • Facebook Comments
More in Business
Using Tracking To Make The Most Of Your Site Visitor Behaviour
Using Tracking to Make the Most of Your Site Visitor Behaviour

Close