- 3,028,945 hits
Join 3,888 other subscribers
- Follow wmfexcel on WordPress.com
Want Google to translate this page?
Top Posts & Pages
- How to sort bar chart in descending order?
- Show number in thousand (k) or in million (M) by using custom format
- How to replace value with a value from another column in Power Query
- Date Formats - A trick to format date with "st", "nd", "rd", "th"
- Insert a text string into a specific position of another text string in #Excel
- Repeat all item labels in Pivot Table (aka Fill in the blanks please)
- Rename column names in a dynamic way with #Excel #PowerQuery
- Open an embedded Excel file during a slide show in PowerPoint?
- Send a cell content to Google search
- Perform VLOOKUP with 2 lookup values
My YouTube Channel
My Facebook page
- My Tweets
Tag Archives: Conditional Formatting
Highlight differences in two ranges with Conditional Formatting in #Excel
Happy New Year! Wish you all an Excellent Year of 2022! To kickstart the year, let’s share a trick to solve a common task – highlighting differences in two ranges, with conditional formatting. Common enough, right? The ingredients are simple, … Continue reading
Posted in Excel Tips Tagged Conditional Formatting 1 Comment
Create in-cell chart with Conditional Formatting in #Excel
Have you ever seen an Excel sheet with in-cell bars to visualize the data like the ones below, and wonder how they are made? Believe it or not, you can do it in one less than a minute with Conditional … Continue reading
Posted in Excel Tips Tagged Conditional Formatting Leave a comment
Apply Conditional Format for alternate row color based on groups…
…that works even when filter is applied. The situation: We have a table that we would like to apply color banding based on groups. We can achieve this by inserting a helper column to identify the sequence of each group, … Continue reading
Posted in Formula Tagged Conditional Formatting, IF, ISEVEN, MAX, SUBTOTAL 2 Comments
Hard-coded range expands automatically with #Excel Table
The INDIRECT trick of using Structured Reference in Conditional Formatting Is it a feature or a bug? This is an extension of the previous post, in which we discussed the use of INDIRECT trick to deal with Excel Table in Conditional … Continue reading
Posted in Excel Tips Tagged Conditional Formatting, Excel Table, VLOOKUP 1 Comment
The INDIRECT trick of using Structured Reference in Conditional Formatting
When setting up conditions for Conditional Formatting in Excel, have you ever tried to set a formula that refers to a Excel Table, but failed?
First of all, why we want to refer to Excel Table in Conditional Formatting? There is one simple answer: To accept new data automatically.
To use Structure Reference in Conditional Formatting, we need an INDIRECT approach which is discussed here. Continue reading
Posted in Excel Tips Tagged Conditional Formatting, COUNTIF, Excel Table, INDIRECT 7 Comments
Highlight weekends and holidays using Conditional Formatting in #Excel Part 2
Conditional Formatting with formula could be tricky (or difficult), especially when the data layout is bad because you have to very clear and careful on the “applied to” range and the absolute/relative references set in the formula. Otherwise, it won’t work and can be quite confusing, if not frustrating. 😛
In this post, I will talk about a case that you will see how empty rows in a data set would complicate the process in setting up conditional formatting. And then I will show three different approaches to tackle the issues. Hope you find it helpful. Continue reading
Posted in Formula Tagged AND, Conditional Formatting, OR, TEXT Leave a comment
Highlight weekends and holidays using Conditional Formatting in #Excel
In this post, I will show you step by step how to set up conditional formatting to highlight weekends and public holidays with Excel. The key is to set the formula right. Having a proper layout will make the whole thing more easier, which will be discussed in the next post. Continue reading
Posted in Format Tagged Conditional Formatting, COUNTIF, OR, TEXT 4 Comments
Highlighting Top X values with Icon Set in #Excel
This post is a continuation of the previous one – Highlighting Top X values with Conditional Formatting in #Excel So I will go straight to the point. For background information, please read the previous post. 🙂 To insert Icon Set Select … Continue reading
Posted in Excel Tips Tagged Conditional Formatting, LARGE Leave a comment
Highlighting Top X values with Conditional Formatting in #Excel
Well… think this topic is too simple? How about doing this with a dropdown menu to make the Top X a dynamic one? with Icon Set? You may download a Sample File to follow along. Let’s start with the basics … Continue reading
Posted in Excel Tips Tagged Conditional Formatting, Custom Format, Data Validation, LARGE, \ Leave a comment
Season’s Greetings 2017
Ho…Ho…Ho… Merry Christmas! Wish you and your beloved ones a Merry Christmas and a Happy New Year of 2018! The above is made of #Excel of course… You may download a copy Seasons Greetings 2017 if you like it. 🙂
Posted in General Tagged Conditional Formatting 2 Comments
#,##0, k or #,##0.0, k ? Why not both?
Conditional Custom Formatting in #Excel Got the following question: This gives me £28.8 K (if 28800 is input) and £28.0 K if 28000 is input. I was rather hoping that if there was no decimal after the thousand, ie £28 … Continue reading
Posted in Excel Tips Tagged Conditional Formatting, Custom Format, MOD 6 Comments
Interactive Chart with navigation panel [NO VBA]
Isn’t it nice? Don’t think that this is difficult. No VBA is required. Indeed you only need to know a few Excel skills in order to create an interactive chart with navigation panel like this. The skills required: Conditional formatting … Continue reading
Posted in Chart Tagged Conditional Formatting, Form Controls, INDEX, Interactive chart, Linked Picture, ROW 3 Comments
Want some pies?
First of all, this blogpost is about Pie Chart, but not about how to make a Pie Chart. Indeed, I am not going to show the steps of making Pie Chart here. Also I am not intended to discuss whether … Continue reading
Posted in Chart Tagged Conditional Formatting, Form Controls, INDEX 2 Comments
Dear readers, Merry Christmas!!! Wishing you and your family a healthy, fruitful, rewarding and Happy New Year of 2017! See you next year! p.s. Should you want to play around the Excel Card above, here’s the christmas-tree! Tip: Hold F9 for … Continue reading
Posted in General Tagged Conditional Formatting, Form Controls Leave a comment