How to Conditionally Format Text Cell Color in Tableau

The "make #Tableau work like Excel" tip you need! Conditionally changing the background color of text in Excel is very easy but requires a hack in Tableau 10.3. Use my video to learn how to conditionally format the cell background of a text or dimension value in Tableau.
★☆★ Join this channel to get access to perks: ★☆★
/ @anthonysmoak
★☆★ FOLLOW ME BELOW: ★☆★
bit.ly/m/anthonysmoak
☕☕ If my channel has helped you, consider buying me a coffee! Thanks & Cheers! ☕☕
www.buymeacoffee.com/anthonys...
★☆★ Support this Channel: ★☆★
👕 Merch ► shop.spreadshirt.com/AnthonySmoak
★☆★ VIEW THE BLOG POST: ★☆★
anthonysmoak.com/2017/08/12/h...
#Tableau #datavisualization #data #analytics #data #dataanalyst #education #onlinelearning

[@AnthonySmoak]

Thank you all again for taking this video to 200k views! I remember trying to figure this out many years ago and decided to make a video. I never knew it would be this popular. Thank you all for subscribing, liking and commenting due to this video!

[@MadMACS73]

Thank you! This is EXACTLY what I was looking for. Perfectly explained, and SO powerful.

[@McRaeJim]

You know Anthony, Tableau formatting is a nightmare. We need a whole series on formatting. How to find a cell, how to find the line or what header attaches to what part of the sheet. It is time consuming to "play around" as lousy Tableau teachers like to say or wade through the internet or You Tube videos. Your tips are some of the best and I always appreciate your efforts and the time you take to explain color format or cell formatting. Please keep it up. What might be obvious for a master like yourself is not to the novice user. Oh and I appreciate your constant mention of the 10% of people being color blind and taking that into consideration. \

[@AnthonySmoak]

Your check's in the mail James! Seriously, thank you for taking the time to leave such a positive comment. I'm trying to learn something new everyday and I'm very thankful that people care to watch. Regards from Atlanta!

[@daveab424]

Awesome! Very helpful. Surprised this still requires so many steps.

[@saragioffre]

Thank you! This is EXACTLY what I was looking for!!!

[@boboconnell861]

Easiest & most direct discussion of this topic out there!

[@AnthonySmoak]

Much appreciated!

[@Jillingit]

Thank you for this much needed tip!!

[@explorewithping5757]

thanks for this tip. really good tip. used it at work.

[@danielstober6249]

Well explained and demonstrated. Thanks!

[@josevargas1968]

Thanks Anthony. So Useful!

[@elwyndude]

Great stuff, thanks for the clear explanation. I just wish Tableau had the ability to put a color indicator on the left most column, before the dimensions, as a highlight/indicator for the rows "state".

[@syednajeeb6634]

You're a damn CHAMP . Really helpful

[@TexasElites]

Awesome! Thanks so much for this video.

[@Ivy_1057]

Thank you for the lesson! 👏

[@AnthonySmoak]

Thanks for leaving a comment!

[@shilpakumari1559]

Thanks for sharing this .. Really helped

[@amangoyal3793]

Can you move these fields that you have formatted to extreme left?

[@hultsmi]

You did a fantastic job explaining how to do this. Thank you for taking the time to do so.

[@AnthonySmoak]

I appreciate the comment!

[@arminbettinger2174]

Thank you so much! That really helped me!

[@Esha25ghosh]

You are the one of the best tutors! Very well explained. Saved me!

[@AnthonySmoak]

You're too kind!

[@marod1971]

It is a fantastic explanation. I am trying to figure out how I can justify to the left in each column.

[@user-qj6ck9yn4y]

this is super helpful Anthony. thank you so much haha

[@brianturner5995]

Excellent tips...thanks

[@wishurdead444]

This helped a lot! Subscribed!

[@calekanack]

This was absolutely perfect and exactly what I needed. Thanks so much!

[@AnthonySmoak]

Fantastic, glad my video could help!

[@pratikhawle7677]

exactly wt i was looking for ... thumbs up mann.. (Y)

[@JohnPDX]

Good job explaining now to do this , but this is why I have a love/hate relationship with Tableau. This should be simple. It's not. It's ridiculously convoluted.

[@funk44]

That's tableau for you. Massively overpriced and massively over-engineered. Like SAS and MATLAB something better will come along and it will eventually die (might take another 10+ years though)

[@murtazaakbarreborn]

You’re a life savior!

[@Hiran47]

Thank you for saving my day! Exactly what I was looking for... 🙏

[@AnthonySmoak]

Glad I could help!

[@komal2286]

Awesome!!!!! Thank You Anthony. This was very helpful..

[@AnthonySmoak]

Thank you!

[@CristianEspinosa-rf6qu]

Thank you very much, you helped me with a job I had been trying to solve for days.

[@AnthonySmoak]

Fantastic! Thanks for the comment.

[@Gerberd4936]

Hello - this is a great video and helped me out very much. I do have a question regarding sorting this using a parameter. How would you create not only a parameter to sort but a secondary parameter to sort after your primary sort? Kind of like in excel. Do you have any videos on that?

[@DjBiggs10]

This is amazing. However having an issue of unable to add a row after the new formatted row? It seems to always come before is there a workaround this ?

[@j.danielzapatap.5940]

Very useful video and great explanation. Thank you !!

[@AnthonySmoak]

Thanks for watching!

[@jiteshkale8716]

The logic implemented with 1,0 will only work if , no of records is same for rating and symbol, in my case the Blue bar at 2.00 was not of same length, I had to make the sum(0) and Sum(1) as attribute to get it work. I've used sample superstore datasource

[@shivani8126]

This is so so helpful. Thank you!!

[@AnthonySmoak]

Thank you!

[@AnthonySmoak]

Thank you all for subscribing and pushing this video over 100k views! I'm glad so many people found it useful.

[@tarunsachdeva3570]

Hey! please help me i want that a user will enter the threshold % and than it will get the text highlighted below that percentage.. Please please

[@markhill7496]

This is what I needed. I just needed to conditionally highlight a column base on the text that it contained. Thank you

[@azammerza3597]

Hello I need some help I need to do this but for a sales vs sales target chart. how would I go about this i tried the formula IF and Elsif but I keep getting a syntax or error or saying the formula is unended

[@azammerza3597]

would the formula be: IF [Sales] > [Sales Target] then "Green" ELSEIF [Sales] < [Sales Target] then "Red" END for if you are trying to color code bars where sales is less than or greater than sales target?

[@ganlian6501]

Good and very detail guide line. Good job. Thanks.

[@AnthonySmoak]

Thank you Gan!

[@jc411911]

Thanks I used this and it worked great and enhanced my viz!

[@AnthonySmoak]

Glad to hear it!

[@user-km7hy8qk4e]

Thank you for the informative video on conditional formatting. It was a a life saver. I was able to create the visualization using this trick but sone values show up indented to left while others are in the center. How can I fix this issue. Can you please help!

[@neetusingla5591]

Very nicely explained Anthony. Can you please resolve one of my issue. My text length is approx 200. So when I am using color coding, it aligns text on center. For small text, its working fine, but if the length is large, then it truncates/Hides the text from both the sides.

[@nknareshkumar]

Legend!!! ty

[@minnisong6143]

You are genius！！！

[@EASYTIGER10]

Can you format the entire row (including Company Name etc)?

[@tweetybrrd]

Hi Anthony...this is awesome! I got this to work but my users would like to do the font conditionally instead....would that be possible somehow using these workarounds? So for your example, instead of the background of the cell being Red for BUY, it would show BUY in Red font. I'm very new to Tableau...I think I got a variation of this by only using one of the placeholders...but I don't see the ability to have a title now for my column?

[@MsTruepers]

thank you for that trick

[@bw424]

Thanks for the workaround! I'm surprised this isn't built in to Tableau already.

[@AnthonySmoak]

I agree. Thanks for the comment!

[@tsvetomirpetkov]

I have a question - is it possible to keep on adding other data in Rows without the conditional colors persistently staying last in the table view. Thanks for the video!

[@ssokol91]

What if the column you want to color is in the middle of your text table? Is there a way to only color code specific columns in a longer list of columns? Tks!

[@IceTexThor]

...the dual axis column(s) can only be at the end (right-most), right? It would be nice to be able to do this on any column. Has this obvious use case been implemented in later Tableau versions?

[@AliciaMarkoe]

You Rock! Thank you 🦋🌷🌼

[@AnthonySmoak]

You're too kind!

[@CthulhuTheory]

This was perfect.

[@AnthonySmoak]

Thanks for the perfect comment!

[@sundar1980ify]

Awesome! Can We set the Color Green & Red directly without changing ?

[@ankurbose11]

I'm not able to color code my calculated fields using this. Need your help.

[@sitientibus]

thanks!

[@thinkngskeptic]

Amazing

[@scottgruenewald7343]

Instead of using the square, you can use a bar. Play with the axis and size to fill the whole cell. It looks better if you have longer strings to present.

[@AnthonySmoak]

Thanks for the tip.

[@NagaRaju-tm8xe]

Thank u , good video

[@AnthonySmoak]

Thank you!

[@annaspramudita1755]

nice!!!

[@PANKAJV09]

Great Trick.. Awesome..... @Anthomy One question, how do it shift this colored columns to different column positions.

[@AnthonySmoak]

Thank you!

[@hemandave80]

Thanks Anthony! This is amazing stuff. The only question I have is how to reorder the column with conditional formatting. For example in a 10 column grid if I need to place the conditionally formatted field as 4th column. How do you do that?

[@AnthonySmoak]

With this technique, these measure columns have to be applied at the end of all the dimension columns.

[@Kutateladze737]

Why it is not possible to sort this formatted field?

[@yuvarajkayachitula4134]

when I increase the size of the square, it ends up spanning rows, How do I address this ?

[@anuskasarkar2183]

This is helpful but when I am downloading the extract from tableau server, I am getting duplicate values

[@reenashanthi4349]

How to validate number and email in tableau?

[@wilan3823]

Thank you very much. Thanks to this, I achieved something that others said was impossible :) There is only one thing: is it possible to give separate colors to the headers of these columns in a similar way?

[@AnthonySmoak]

I don't believe so, although I haven't revisited this video in awhile.

[@funguy29]

Couldn't you duplicate rating and make it a rating_fact for example and then use the colors as per usual?

[@morhaliva]

Thank you!! I still have a challenge I can’t figure out- can I make it that the conditional formatting comes first at the table? I mean comes before the dimensions I put on Rows

[@AnthonySmoak]

Unfortunately with this hack, the measures have to be after the dimensions. Thanks for the comment.

[@bakk2963]

Thanks a lot!! it worked with me for one column. But when I tried to do a second colored column, some rows are not appearing based on the first column values. Do you have any idea how to fix this?

[@AnthonySmoak]

Sorry but It's hard to diagnose via text without knowing your specific dataset.

[@karenyanez6130]

I have a table with multiple measures, do I need to follow these instructions to put color to each column or exist another way to do it more easy?

[@AnthonySmoak]

This video deals with dimensions. For measures see if this thread helps you: community.tableau.com/thread/107750

[@Karthik6902]

Hey Anthony Smoak, thanks for the video. I had a query if there is one of the new category is added in the column then how do we prefix the colour in case we know all the distinct possible categories in prior. It would be really helpful.

[@AnthonySmoak]

Perhaps this video can help kzbin.info/www/bejne/fWGynopvnrKmnKsfeature=shared

[@Karthik6902]

@@AnthonySmoak Thanks for sharing, this is what I was looking for 😃

[@manikandanu4131]

sir, I want this in excel. That is "How to set item based color change in excel cell"

[@smitakar809]

I wanted to know is there any way we can shuffle the coloured column to let's say beginning of the worksheet? Since it is not an actual column from the data source, I do not know how to move it. Any thoughts? Sorry, if it is too basic a question. I am new to Tableau Thanks, Smita

[@AnthonySmoak]

The measures come after the dimensions. You would have to use this technique to make everything a measure which may be overkill.

[@yyz619]

thank you very much, Do you know how to make the title or "Rating", "Sector" TEXT WRAP? I am using your method and I have around 15 of this columns to show which I made 30 of placeholders and then dual axis, and some of my column name are long, I want to text wrap it but doesnt seem there is a way. And by the way after I made 15 of columns like this the tableau is kind of laggy, my data is only around 1000 rows, so I am guessing this method is only good for a few columns? Thank you for answering!

[@AnthonySmoak]

It sounds like you're trying to automate a complex Excel report. Tableau is a pure visualization tool not suited to make large complex tables. This is why we have to do silly hacks for conditional coloring. See if your stakeholder will let you present an actual visualization of your data in lieu of so much detail.

[@ashleyxu2824]

Hi Anthony, thanks for this wonderful tutorial! Do you have the tutorial how to rate the buy or sell symbol? Thanks!

[@AnthonySmoak]

Help me understand what you mean by rate the symbols?

[@ashleyxu2824]

@@AnthonySmoak Hi "Symbol" is the Second Column listing "BUY" and "SELL" in the video. Are these automatically updated by using any functions?

[@AnthonySmoak]

This are just randomly assigned values. There is no function to determine the ratings.

[@xroydacute]

Good one! My question is can I make the conditionally formatted table to be the first column?

[@AnthonySmoak]

Only if all your columns use the placeholders (1 & 0) technique I show in the video.

[@MRUMESH-zx7dk]

Good job now can you try for measures (dimensions) value.....

[@AnthonySmoak]

What do you mean specifically?

[@firstname4337]

@@AnthonySmoakI have a measure (let's say weight) and they want the weight highlighted according to a various ranges -- but they want the units (a text column to the right) -- so 50 lbs 25 kilos 454 grams the only way I could figure out how to do that was to duplicate the weight field, convert it to dimension, do a dual axis with the weight dimension and units and color the weight accordingly only problem is that since the weight is a dimension it is being interpreted as text and it is all left aligned instead of right aligned but I couldn't have even got this far without your video, so awesome work and thank you very much

[@komalkusum4508]

Hi Anthony, can I add few uncolored columns after the colored ones?

[@AnthonySmoak]

I don't see why not. Give it a shot.

[@santiroberts4426]

Hi Anthony this is great. got it working with my dataset, with one small issue: the second of two conditionally formatted columns displays "1" for all cells as well as the text I want displayed. Any chance I could send you a screenshot or something (unless you know how to deal with it right off the bat!)? Thanks.

[@mitchfiggat100]

Same issue, have you figure it out?

[@ireneanibogwu7242]

@@mitchfiggat100 You probably don't need this any more but in the Marks section, go into the one you put the square AGG(1) in this case. Then under Text , and uncheck 'show value labels'

[@ireneanibogwu7242]

*show mark labels*

[@TheSaisharath]

After hard coding the shape to square, when I increase the size of square it's getting spreaded to other cells as well, how to avoid it? Please help

[@AnthonySmoak]

Don't increase the size as much or don't use a shape and stick with the square.

[@555srinath]

By Any chance do we know the limit like how many fields can we add and color code?

[@AnthonySmoak]

Keep in mind that it takes 2 columns to color format in this manner. www.tableau.com/about/blog/2019/10/tableau-table-improvements

[@manojprabhakarselvaraj1903]

Fantastic tip!! Where can I get this dataset

[@AnthonySmoak]

Not sure if I have this dataset anymore, but you can always make up your own data at mockaroo.com

[@Vintagetube310]

Hi Anthony. I am working on a case study using digital data for basketball engagement. I wanted to see if you could possibly help me with my project?

[@AnthonySmoak]

Hi, I do get a lot of requests for help as you can imagine. If you have a question I can answer very quickly off the top of my head, you can ask here. If you'd like to book an hour of my time, contact me at the link on anthonysmoak.com. Thanks

[@ManojYadav-mo3ei]

Please create the video in show hide filters

[@aayushsharma4848]

When i am dropping 1 and 0 calculation in row.. the size of bars are not even but different for every row...how to change that

[@anny72234]

same here. have you found a way?

[@aayushsharma4848]

@@anny72234 yes, change those dimensions to attribute

[@Charango123quena]

this can be done in less than minute in Power BI ... with no need for calculated fields ....

[@ln6835]

Is there is any easier method? It is very simple logic indeed. I am new using tableau but sometimes i feel it is silly using it, sometimes it make simple thing become complex.

[@AnthonySmoak]

Unfortunately this is the easiest method. I remember it took me awhile to figure it out because no one had a nice easy to follow video or writeup back then. Tableau doesn't have the concept of cells like Excel, and this can frustrate new users.

[@sargamgupta7194]

I think you are a big GOT fan lol

[@radiosonicfan1726]

Nice Tip. Seriously this should be faster and easier. Should not have to watch a video on how to change the background!

[@guitarreality5981]

It takes 2 clicks to do it with Spotfire, just saying :)

[@AnthonySmoak]

I wish I could verify, but there's no Spotfire Public :)

[@cbracamonte]

Great but please slow down

[@AnthonySmoak]

Thanks. Watch the video at half speed if that helps.

[@joshdee7639]

Lmao this man is doing everyone a service for free and youre here crying because you cant figure out how to slow the video down? Any person with a normal functioning brain can watch this on 1.5x speed and still understand perfectly. My god you people that complain are annoying af

[@sanagaribabu7956]

Why thismuch steps ? Just follow the seperate legend option

[@AnthonySmoak]

Let me know if you're successful using the separate legend option on dimensions instead of measures.

[@im4485]

Tableau sucks

[@AnthonySmoak]

Microsoft has entered the chat..