Improved conditional formatting in Excel For Better Data Visualization
Conditional formatting makes it easy to emphasize important cells or ranges of cells, highlight unusual values, and visualize data by using data bars, color scales, and icon sets. In each newer version of Excel, it includes further more formatting flexibility.
Conditional formatting in Excel is a powerful feature that allows you to automatically apply formatting to cells based on specific conditions.
It’s a fantastic tool for visualizing data trends, highlighting important information, and making your spreadsheets more informative and user-friendly.
Here are some of the best ways to use conditional formatting, along with concrete examples:
Color Scale for Data Ranges:
Use a color scale to visually represent the distribution of data values within a range.
Example: Apply a green-to-red color scale to a list of temperature readings to quickly identify hot and cold temperatures.
Icon Sets for Comparisons:
Apply icon sets to cells to compare values and show trends using icons like arrows or traffic lights.
Example: Use upward and downward arrows to indicate whether sales figures have increased or decreased compared to the previous month.
Data Bars for Proportional Data:
Use data bars to create horizontal bars within cells to represent the proportional value of each cell compared to others.
Example: Apply data bars to visualize the relative sizes of monthly expenses in a budget spreadsheet.
Highlighting Duplicates and Unique Values:
Apply conditional formatting to highlight duplicate or unique values in a range of cells.
Example: Highlight duplicate names in a list of customers to identify potential data entry errors.
Use conditional formatting to color-code cells based on priority levels, making it easy to identify important tasks or items.
Example: Color-code tasks in a to-do list as high, medium, or low priority.
Custom Formulas for Complex Conditions:
Create custom formulas for more complex conditions that aren’t covered by built-in formatting rules.
Example: Apply conditional formatting to highlight cells with values greater than the average of a range.
Apply conditional formatting to highlight dates that fall within a certain range, such as upcoming deadlines or overdue dates.
Example: Use red formatting to highlight dates that are past the current date in a project timeline.
Data Validation Feedback:
Use conditional formatting to provide feedback on data validation rules, making it clear why certain entries are invalid.
Example: Apply a red border to cells that contain text longer than a specified character limit.
Heat Maps for Data Analysis:
Create heat maps by applying conditional formatting to visualize patterns and trends in large datasets.
Example: Apply color scales to sales data to quickly identify regions with the highest and lowest sales figures.
Use conditional formatting to trigger alerts or notifications based on specific formula-driven conditions.
Example: Apply a bold font and red text to cells where inventory levels are below a certain threshold.
Key to effective conditional formatting is to choose formatting options that align with your goals and data presentation needs. By using conditional formatting strategically, you can make your data more visually engaging and facilitate better decision-making.
New icon sets: In Excel, we can access to more icon sets, including triangles, stars, and boxes. We can also mix and match icons from different sets and more easily hide icons.
For example, we might choose to display icons only for high profit values and remove them for middle and lower values.
More options for data bars: Excel now comes with new formatting options for data bars. You can apply solid fills or borders to the data bar, or set the bar direction from right-to-left instead of left-to-right.
Not only that, data bars for negative values appear on the opposite side of an axis from positive values.
Singapore CPF Rules
In Singapore, all salaried employees need to contribute 5-20% of their salary, depending on age to their Central Provident Fund or CPF. The employer contributes 7.5% to 17% of the salary to the CPF Fund, depending on the age of the worker.
The CPF contribution calculator for employers and employees can be done using Microsoft Excel.
CPF contribution calculator Excel File is available for download below.
CPF Contribution Rates changes from 1 Jan 2022
(monthly wages > $750) Based on CPF Board
(% of wage)
(% of wage)
(% of wage)
55 & below
Above 55 to 60
Above 60 to 65
Above 65 to 70
So payroll executives from Finance or HR professionals need to calculate the correct amount for the contribution to CPF, and provide the net salary to the employees who are Singapore Citizens or Singapore Permanent Residents (SPR). The employer also needs to know their total payroll expenses so as to know their Payroll Liability each month.
The spreadsheet uses simple formulas that are already pre-programmed. So you can just begin to use this spreadsheet for your company. It has been tested for CPF Contribution Rates in 2022 and beyond, and everything will work smoothly.
Increase in CPF contribution rates from 1 January 2022
The CPF contribution rates for employees aged above 55 to 70 have been increased.
The increase in the CPF contribution rates will be fully allocated to the employees’ Special Accounts to provide a bigger boost to their retirement income.
For those earning monthly wages of more than $500 to $750, the employee contribution rates will continue to be phased in.
There are no changes to the graduated contribution rates for first and second year Singapore Permanent Residents (SPRs).
Hope you like this CPF Calculator Singapore Excel Template for calculating the CPF in Singapore. If you like it, I would appreciate it if you Like Us on Facebook, and leave a comment below!
Cheers, Vinai Prakash, MBA, PMP, ITIL, GAP, Six Sigma
Master Trainer, Intellisoft Systems
Vinai teaches Advanced Excel Techniques, Dashboard Techniques using Excel, Data Interpretation and Analysis Training courses at Intellisoft. He has trained over 25,000 students in over 28 countries and regularly conducts Excel Workshops in Singapore, Malaysia, Indonesia, Australia, India, Dubai, Egypt, Zimbabwe, South Africa etc.
Wondering what to pursue after learning Excel? Let us find the answer. Before that, let’s first read a quick short story.
Once upon a time, a very strong woodcutter asked for a job in a timber merchant. The pay was good and so were the working conditions. So, the woodcutter was determined to do his best.
His boss gave him an axe and showed him the area where he would work.
The first day, the woodcutter felled 18 trees.
“Congratulations,” the boss said. “Go on that way!”
Motivated by the boss words, the woodcutter tried harder the next day, but he could only bring down 15 trees. However, the third day he tried even harder, but he could only manage 10 trees. Day after day, he finished with fewer trees.
“I must be losing my strength,” the woodcutter thought. He went to the boss and apologized, saying that he is week and could not understand what was going on too.
“When was the last time you sharpened your axe?” the boss asked.
“Sharpen? But I’ve had no time to sharpen my axe. I have been so busy trying to cut trees….”
This story states that equipping yourself shouldn’t ceases & sharpening the tool set & keep you updated is the only way to a long term success.
Now, why are we reading to this story?
Let me jump to the point directly.
The world is fast paced. So, we need to have the required tools ready & sharp for use.
It is almost impossible to find work that does not require some amount of Excel. Having the right skills for using and applying Excel for Simple or Advanced tasks is a must.
Microsoft Excel is one of the most valuable skill sets that can take you far!
Grab SQL Skills today to be an independent analyst otherwise make a new career as Data Analyst, Database Administrator, Data Scientists or even Data Architect at Intellisoft Singapore.
Join Excel VBA (Macro) Course:
I am an Excel expert. Do I only get to learn the skills that are into Data Analysis?
Well definitely not. As we have already discussed Excel is a program that is used in all the fields in which the Data Analysis is just a new popular.
Excel could be enhanced with further skill sets and utilized in various tasks as well.
One such skill set that is highly useful for people into leadership, or managerial position to review the work or report, or even for someone who is into repeating the functions, formulas, or a set of same steps dozens of times every day.
There is a boon to such people – Macros in Excel.
You can write & record macros for simple to complex steps that you accomplish every day. In addition to that, Macro Code runs much faster and save a huge amount of time, which in turn will increase productivity.
After all, you don’t get bored of what you are doing every day.
Every job seeker would have this word written in their resume – Microsoft Office Proficiency.
What Exactly is Microsoft Office Proficiency?
A Microsoft Office Proficient should be able to create an error-free, beautifully formatted document with header, footer, page number & Page breaks.
In Excel, they should be able to handle a large volume of data, format, sort, filter and create Pivot Tables for better visualization of Data.
In PowerPoint, the ability to make aesthetically appealing stellar presentation with graphics, images, GIFs, Animation is considered good proficiency.
Handling Microsoft Outlook like a pro to manage emails, create separate folders, track tasks, manage calendar, set reminder, marking them complete, and most important, getting things done quickly with these productivity tools.
This should not be overwhelming. It should be easy to do, and management expects everyone to be proficient in Microsoft Office.
It has been years since the Microsoft Skills have become mandatory for every person coming into the workforce – whether administration, data handling, HR or even the Managerial Position.
A research by IDC concludes that Microsoft Office is one of the top three skills desired by any employers next to oral and written communication Skills.
Possessing Microsoft Skills not only merely helpful in typing docs and tabulating number, rather they help us to get more productive, efficient and handle and present data professionally effectively than ever. Microsoft Office is the most effective, accessible, and easy to use software’s.
Excel is often used in accounting and finance for its ability to calculate and compute complex numbers. Even non-financial businesses use Excel because of its unbeatable ability to organize data in tables & for updating, organizing, and displaying the data nicely.
It is highly used in administration and managerial level to analyze data quickly and present to management.
It even supports Visual Basic Applications to create macros to automate functions that increase efficiency.
In fact, the Pivot Tables and Dashboards are inevitable when it comes to analysis and reporting. You must up your proficiency in Excel to master these functions.
Everyone would have made a basic presentation in Schools and workplaces to present the work. But PowerPoint comes handy to make it visually appealing and effective.
Its intelligence in generating different design ideas for the slides is becoming so popular nowadays. Other than just suggestion, it allows you to use the plenty of shapes and icons that are in-built and even allows you to import illustrations and graphics from the internet while working.
In short, anyone can make a stellar presentation with a quick guidance and practice.
It is always wise to have a most sough after skill in hand to increase employability& cement a position in job industry.
While Microsoft Office is filled with short cuts and methods to create attractive and easy to manage documents, a comprehensive hands-on training can help you to master the hooks and corners of the Microsoft Office package within just 3 days.
Simply follow the step-by-step instructions and learn the art of creating professional business documents easily every time.
With this training you will be able to Create Impressive Proposals, Charts & Presentations to Woo Clients.
Microsoft Excel is one of the most used software at work. People who claim to know Microsoft Excel software mostly are the ones with basic or intermediate knowledge & understanding of Excel. The productivity increases only when we make the most out of the useful software, and gain mastery.
There are many hidden gems and short cuts within Excel, that hardly anyone talks about. I am amazed when people with years of Excel experience tell me they haven’t heard about these. What a waste of time…
‘Let us discuss in this series the ultimate tips in Excel that will make things snappier, and make your co-workers say, “how did you do that??”. They will save a lot of time for you too and improve your productivity.
Tip Number 01 INSERT SERIAL NUMBERS IN A FLASH
What do we when we have to insert Serial Numbers in Excel? We type the number 1 in the cell A1, then 2 in the cell A2, and finally select both the cells & drag the mouse pointer until we get the required serial numbers generated. Right?
But what happens when we need Serial numbers until 100? 200? Or more?
Do we keep dragging? Certainly not!
There is an ultimately easy way in which you can just flash fill the serial numbers to whatever value needed.
Here are the steps:
Input Number 1 in the first cell
Select Fill > Series from Editing Ribbon of Home Tab
Home> Fill > Series
Select Columns from the Dialog box, give Step & Stop values and Hit OK.
NOTE: If you are looking to Fill Rows instead of columns, just select Rows in the dialog box and follow the same steps.
Bet you didn’t know this trick… Well, now you do.
Tip Number 2 FORGET COPY+PASTE – JUST DRAG & DROP
When we have a set of data in one column & we want it to move to another column. What do we do? Select them all, copy or Hit CTRL C and select the destination column, and paste or hit CTRL V?
Forget the hassle, now select the required data, just Drag & drop to the destination.
Tip Number 3: COPY MULTIPLE VALUES USING THE CLIPBOARD
Working with multiple values? Forget the hassle of copying and pasting one by one.
Use the built in Clipboard feature of Excel.
You can expand the clipboard from the icon lists or use the Win (Windows Key) + V to click on the required values
Tip Number 4: DUPLICATE A SHEET IN JUST ONE-CLICK
Want to copy the whole sheet? Are you selecting and copy pasting?
Or right click, copy, select the sheet, move to bottom?? So many steps for duplicating the sheet?
Now Duplicate a Sheet like a Pro!
Just hold down Ctrl + Drag & Drop the sheet to desired position.
Tip Number 5: UNDO & REDO IN ONE HIT
Undo & Redo would be the most used operations in Excel.
Now do undo and redo with just one click.
Use the buttons on top.
Tip Number 6 Use the QUICK ACCESS TOOLBAR
Tedious to find the operations that you frequently use?
CHANGE ALL CAPS TO Mixed Case for Next 3 Rows.
CLICK THE DROP DOWN ARROW TO SEE THE OPTIONS
YOU CAN ADD THE FREQUENTLY USED OPTION TO QUICK ACCESS TOOLBAR
EXTRA TIP: YOU CAN ADD ANY OTHER COMMANDS THAT YOU USE FREQUENTLY
Hope that helps! Want to learn more exciting tips, tricks & shortcuts and improve your productivity in Microsoft Excel?
If you wish to build your knowledge to have a better Excel Operations knowledge, you must start working on it. You will do well in your career, and get ahead faster, even land that dream job with better skills & competence in Excel.
We at Intellisoft are here to guide you.
Intellisoft Systems offers 2 day extensive workshops on Microsoft Excel.
Want to go beyond the common use of Excel and Explore its Extremely Useful but Lesser-Known Features of Microsoft Excel?
Microsoft Excel’s extensive features can help you to increases productivity and also saves time. Become proficient in Data Management & Business Analytics – Turn Data into Insights, Develop better tables & charts.
Lesser Known Features of Microsoft Excel
Apply formatting to cells automatically depending on the value of the cell. It is also a powerful feature, given that conditions can be based on any Excel formula. Conditional Formatting works by letting you set rules for how cells can be customized.
Conditional formatting makes it easy to highlight required cells or ranges of cells, emphasizing unusual values. In addition to that, it is used to visualize & analyze data in Excel by using data bars, color scales, and icon sets. Overall, it helps you to identify important data faster.
This Advanced command works differently from the Filter command in several ways. It displays the Advanced Filter dialogue box instead of the Auto Filter menu. You can filter the data in place, or to a different location. You can also specify that you want to see unique items only.
All the duplicates are removed from the filtered list, with Advanced filter. The Data tab contains all the features of Advanced Filters. The advantage of using advanced filter is that you can see the filtered criteria in Excel cells. You can also easily add new filters by typing the values directly in the cells. Thus, it is absolute must learn feature.
Database functions is a Seldom used Gem in Microsoft Excel. With this, you can not only perform basic calculations but also use complex criteria, that allow you to perform the calculation for a specified subset of the records in your database.
These functions treat the entire set as a Database, just like a SQL Database.
Consolidating Multiple Worksheet:
If you receive information in multiple sheets or workbooks that you want to summarize, the Consolidate command can help you pull data together onto one sheet. It allows you to combine information from multiple workbooks when you need them one place.
The Excel consolidate function lets you select data from its various locations and creates a table to summarize the information for you, automatically, painlessly.
Preventing others from Changing Master Data:
This feature allows you to prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet. You can lock the cells on your Excel worksheet & then protect the sheet with a password.
With worksheet protection, you can make only certain parts of the sheet editable and users will not be able to modify data in any other region in the sheet.
You can lock the structure of your workbook by specifying a password.
Locking the workbook structure prevents other users from adding, moving, deleting, hiding, and renaming worksheets.
Learn from expert tips, tricks and resources for Excel, PowerPoint, Photoshop, Python, Power BI, Project Management, IT, Soft Skills & more with our Email Newsletter. Plus get the latest news on Grants. Join Today!