Recent posts
Constructive Criticism
4 Benefits of Using Epoxy Glue for Your DIY Projects
05 February 2025
nichemarket Advice
Alternative Apps To Try Now That TikTok Has Been Banned
19 January 2025
Industry Experts
Top Trends in Digital Signage for South African SMEs
15 January 2025
Industry Experts
Grow Your Profile With Cheap Instagram Followers
14 January 2025
Popular posts
Extravaganza
Trending Music Hashtags To Get Your Posts Noticed
24 August 2018
Geek Chic
How To Fix iPhone/iPad Only Charging In Certain Positions
05 July 2020
Extravaganza
Trending Wedding Hashtags To Get Your Posts Noticed
18 September 2018
Money Talks
How To Find Coupons & Vouchers Online In South Africa
28 March 2019
How to heatmap data with conditional formatting colour scales (row by row)
11 April 2017 | 0 comments | Posted by Shamima Ahmed in nichemarket Advice
If you have ever worked with the advanced conditional formatting you'll know it can be a bit of a challenge, especially if you in a rush and all you want to is finish your excelling so you can actually analyse your data! In the data-driven world of analytics, we work with boatloads of data each day and sometimes analysing these trends in quick and obvious ways can be less tedious than you think.
One of my favourite tricks is applying a heat map to a range of data by row so you can analyse when each variable performs at its best - like this:
This can easily be done using the conditional formatting colour-scales feature for a single row. The problem comes in when your data set is hundreds of variables long and applying this method to all of them will take you a better part of the day (hours of your life which you will never get back!) which can be used more constructively! The good news is that as always, I have a trick up my sleeve! Here are two much more efficient ways to apply colour scales to your data to each row independently.
- Copy and Paste
- First format the first row using the colour scales gradient you prefer, I usually choose the Green - Yellow - Red gradient.
- Highlight the first row and copy
- Highlight the second row and past special, values only.
- Now copy both rows and paste special to rows 3 and 4.
- Now copy 4 and paste 4, and continue like this until all cells have been covered.
- Using Macros
- Apply the formatting to the first row.
- Highlight all the unformatted rows (only the cells you want to format)
- Run the macro
- Sit back and enjoy your coffee while the magic happens!
Yes, the answer is as simple as copy and paste. The easiest way to achieve this is to:
While this is the easiest way, it's still not the fastest way if you working with a large dataset.
The good news is if you familiar with macros there is an easier way. The macro below will copy the conditional formatting you applied to the first row of data and apply it to each row you select (independently).
Just REPLACE B1: M1 and reference the first row of your table.
Sub nicheCF()
Range("B1:P1").Copy
For Each r In Selection.Rows
r.PasteSpecial (xlPasteFormats)
Next r
Application.CutCopyMode = False
End Sub
How to use:
The bulk formatting usually goes quite fast, but the time it takes is directly dependant on the size of the dataset.
That's it! You're done!
Contact us
If you have any questions or would like to know more about conditional formatting, comment below or feel free to contact us here!
You might also like
What Is Amazon Retail Advertising Service?
11 January 2025
Posted by Che Kohler in nichemarket Advice
Amazon is opening up its ad tech to other major retailers with its new Advertising platform, which allows other sites to run sponsored ads on their o...
Read moreTop Trends in Digital Signage for South African SMEs
15 January 2025
Posted by Viktoriia Tsykalova in Industry Experts
South African SEMs keep innovating and looking for new technology-based methods in marketing. Following and implementing digital signage trends is a ...
Read more{{comment.sUserName}}
{{comment.iDayLastEdit}} day ago
{{comment.iDayLastEdit}} days ago