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  
Fish Chart in Excel
Charts and Visualization

Fish Chart in Excel

Fish Chart is my lasted innovation. I have used it to display sales data points. The new thing is that fishes are getting changed randomly whenever worksheet is recalculated.  I have used 3 fishes here. Data label is in front of fish’s mouth looking as a water bubble. I have used Line Chart to create this. Below is the data set for fish chart. [caption id="attachment_8475" align="aligncenter" width="166"] Data Points[/caption]
Conditional Formatting in Lollipop chart
Charts and Visualization

Conditional Formatting in a Lollipop Chart in Excel

Using the Conditional Formatting in any chart is very useful. It helps to audience to identify which category is meeting the target and which is not meeting. Here, we have used the Conditional Formatting in a beautiful Lollipop Chart. We also have created 4 variants of this chart with some different shapes which make it more eye catching. Days wise sales are available on range “A1:B8” and target is available
3D Glass Chart
Charts and Visualization

3D Glass Chart in Excel

I am back with a very innovative and Informative chart that is 3D Glass Chart in Excel. This chart can be used to display a KPI metrics like Service Level, Quality Score, Productivity etc. Here we have created this chart for Zone Wise Service Level. Below are the data points for which we will create this chart [caption id="attachment_8337" align="aligncenter" width="232"] Data Sets[/caption] Below are the steps to create this
Flag Chart
Charts and Visualization

Info-graphics: Flag Chart in Excel

If you have a small data set and you want to create an attractive chart then Flag chart is very nice Idea. It is very eye catching and innovative Idea. To create the flag chart we will use 2D clustered column chart, Error Bars and Shapes. Below is the data set- [caption id="attachment_8295" align="aligncenter" width="179"] Data Set for Flag Chart[/caption] Below is the Snapshot of Flag Chart in Excel. [caption
Create Multiple Folder

Create Multiple Folders from Excel Range

In the previous article you have learnt how to check the whether a folder exist or not. In this article you will learn we can create multiple folders on one click using excel range.   Below the VBA code to create multiple folder from excel sheet Option Explicit Sub Create_Multiple_Folder() Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("Sheet1") Dim sub_folder_path As String Dim i As Integer For i = 4
Folder Existence Function

Check Folder Existence using VBA

During the automating some task in VBA, many times we need to check that a folder is exits or not. For example if you want to create or delete a folder using VBA then you should check first whether that folder exits for not. If that folder exits then you take your next action. It method can prevent you from an error. In this article you will learn how to
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
Rename the Multiple files

Rename Multiple File on one click

In this article you will learn how to rename the multiple files available in a folder. In the previous post I have explained you how we can get the files information in a excel sheet. We will use the same method to get the current file names of given folder. Once we have the file names in the excel sheet then we will give new name in another excel column
Get File Information

File System Object: Get Files information in Excel

In this article you will learn how to get the files information from a folder like File Name, File Type, File size, Last modify date etc. in excel sheet. We will use the file system object to create this. To use the file system object you need to add “Microsoft Scripting Runtime” Reference. To add this Reference go to Visual basic editor > Tools > References > Microsoft Scripting Runtime