Custom Formatting
Excel Tips and Tricks

Arrows Symbols (➷➹) with Custom Formatting in Excel

Custom formatting is very useful in Excel. You can display some Icon with colors with the numbers. Here we have taken an example wherein we have displayed designer up/down arrow (➷➹) for Variance %. We have a table for Market wise sales for Current Year and Previous Year. We have displayed Variance % here with designer arrows. [caption id="attachment_12838" align="aligncenter" width="617"] Market Wise Data[/caption] In the Variance % column we
Highlight Unmatched Cells
Excel Tips and Tricks

Highlight Unmatched Cells using Conditional Formatting in Excel

In this article you will learn how to highlight the Unmatched Cells values from another range using Conditional formatting. For Example, we have Sales and Product Master worksheets. In the Sales worksheet we need to highlight the Products which are not available in Product Master. [caption id="attachment_12307" align="aligncenter" width="1496"] Sales Data and Product Master Worksheets[/caption] Below are the steps to put the Conditional Formatting to highlight Product Name in Sales
Highlight Consecutive Duplicates
Excel Tips and Tricks

Highlight the Consecutive Duplicates in Excel using Conditional Formatting

In this article you will learn how to highlight the Consecutive Duplicates in Excel using Conditional Formatting. You will also learn how to highlight consecutive weekly off in an Attendance Sheet. Let’s say we have month wise sales wherein we have month name duplicate. We want to highlight the consecutive duplicate month as given in below image [caption id="attachment_12290" align="aligncenter" width="282"] Consecutive duplicate month[/caption] Below are the steps to put
Smart Check Box
Excel Tips and Tricks

Excel Tip: Checkbox in Excel Table

Check box control in Form Controls is very useful in Microsoft Excel. Here we have created a table in each row one check box is available. We have used conditional formatting to highlight the checked row. Calculation of Total is changed according to checked rows.   Below is the table with Check boxes [caption id="attachment_8641" align="aligncenter" width="514"] Table with Check boxes[/caption]   Click here to download this Excel file  
Sort by Custom List
Excel Tips and Tricks

Excel Tips: Sort data by Custom List

Many times it happens when we are not able to sort our data as per our requirements. We have to take some support column to sort the data. For example if you have month wise data points. Month name is in text format like Jan, Feb, Mar etc. If data is not sorted by month and we have to sort the data by month name. We would not be able
Analog Clock
Charts and Visualization Excel Tips and Tricks

Analog Clock in Excel (Pie Chart + Doughnut Chart)

After creating the Digital Clock, we have created a beautiful Analog Clock. This stunning Analog Clock we have developed using Pie Chart and Doughnut Chart. To calculate the formula automatically, we have used below given code. Option Explicit Sub Start_Clock() Dim sh As Worksheet Set sh = ActiveSheet sh.Range("N1").Value = "Start" x: VBA.DoEvents If sh.Range("N1").Value = "Stop" Then Exit Sub Application.Calculate GoTo x End Sub Sub Stop_Clock() Dim sh As Worksheet
Fill Blanks
Excel Tips and Tricks

Excel Tip : Fill the blanks cells in your data

Many times we have zig-zag data which has to be formatted in proper manner to create some report or dashboard. In this article you will learn how we can fill the blanks in our data. Below is the data in which blanks cells have to be filled: [caption id="attachment_7049" align="aligncenter" width="507"] Data Sets[/caption]  We can fill the blanks using 4 differed tricks. 1-Using Filter: We can fill the blanks using
Highlight Top and Bottom Performer
Excel Tips and Tricks

Highlight Top and Bottom Performer using conditional formatting

In this article you will learn how to use Conditional Formatting to highlight the Top/bottom performer on the base of performance score. Top performer has been highlighted in green color and Bottom performer has been highlighted in red color. A drop-down of Top Performer and Bottom performer has been given on cell C1 using Data Validation list. In the below image, top performer has been highlighted. To highlight the top
Lock the Cell after updating
Excel Tips and Tricks

Lock Excel Cell after Updating

One of our client asked us to create a template wherein data can entered only once. As soon as data is entered in the cell, it should be locked for editing. Only Admin (who has the protection password) should be able to edit this. Here we have given some solution: Below is the worksheet wherein data will be entered. [caption id="attachment_4021" align="aligncenter" width="720"] Excel sheet[/caption]   We have put the