Posts Tagged ‘Basic’


It’s no secret that I’m a big fan of beautiful data visualizations. What may not be so obvious is that I’m huge on the KISS (keep it simple stupid) principle when it comes to getting work done. I believe that any moron can complicate things, but it takes a genius to simplify them. (On a separate note, I often recall a quote on life from one of my all-time favorite movies – “It is very simple to be happy, but it is very difficult to be simple”)

Today I’m sharing a simple tip on how you can highlight a data-point on your chart, based on a selection, without using VBA. And the best part is that it can be used for any chart type.

Chart - Highlight Data PointI have data for average precipitation in Delhi by month (for years 2000-2012). I have placed my data in cells A4:B15. Data validation has been put in cell B2 such that it can only take month names from January-December. Now that we are set up, follow these steps: (more…)

Introducing Our New Category… R

Posted: December 1, 2014 by MaxFraudy in R
Tags: ,

You know how learning new things keeps one’s mind agile (or so I’ve heard). Well, following this adage, I’ve recently been spending some time in trying to learn R. I find R to be very different from SAS & SQL or any other language that I have used. It took me quite a while to get used to how data is handled in R.

I’m still a beginner so I don’t see myself posting a lot of ‘useful gyaan‘ on R in near future, however, I believe other beginners may also be facing similar problems as I am. So in line with our mission to ‘make life @work easier‘, I’ll be ‘sharing stuff‘ that could be real help (at least to a novice like me).

The first in line is a cheat sheet for data.table in R (Download data+table+cheat+sheet). This is not my creation but I found it at this place and felt like sharing it. For someone like me, who tends to forget syntax this is a ready reckoner for data.table manipulations. Some examples:

Snapshot of the data.table cheat sheet

Snapshot of the data.table cheat sheet


UntitledWe have earlier discussed how to hide errors in a worksheet in this post. Today I’m going address another similar problem that is very commonly faced. There are various ways to tackle it and there is no right or wrong about it. I’m sharing what I prefer to use (because I find it very simple and easy to implement).

Suppose I have a data table in my dashboard that is referencing to another range. My table has 10 rows (A2:D11) that reference range Z2:AC11. As long as there is data in Z2:AC11, everything is fine.

TableNow suppose that I have data only till row 4. In this case, my table shows 0’s which looks ugly and I’d rather prefer those cells to be blank. How do I solve this problem?

table2 (more…)


UntitledToday, I’m sharing a basic but nonetheless a neat trick to make your reports more presentable. I’ll take an example to illustrate my point.

I have Life Expectancy data for various countries for years 2011 and 2012.  The fourth column is calculated using data in B & C columns. The number of rows of data can change, so I have put formula in extra rows. The problem then is that the rows that don’t have data return #DIV! error in column D as below. (more…)


This is a common requirement while creating dashboards. One selects an option and the charts update to show data for that particular selection. It would be very useful if the same chart could show you how that particular selection fares against average or a set benchmark.

Comparison Chart Against AverageLet’s take an example. I have data for a class of students with their subject marks. I make a selection of the student name, his marks for each subject are plotted on a chart. I wish to see how this particular student has performed in each subject as against the class average. This custom chart does just the same, and it can be very very easily created in Excel as below:

(more…)


UntitledWhen working with a hierarchical data, most often we need to view at various levels of hierarchy. One of the ways of getting it done is using Data List Outline feature of Excel. To better demonstrate what I’m talking about, let me take an example.

I have the following data – the first column contains the month name, the second column contains the region name and the last column has the sales.

Raw Data

Raw Data

(more…)


While Preparing an Excel Dashboard one needs to handle a lot of hiding and unhiding of Sheets for various users. For Devloper all sheets should be unhidden and for user some of them should visible. We need different views of one workbook for different people developer, user, admin, etc.
We can handle this with using a Custom View property of the workbook with VBA

ActiveWorkbook.CustomViews.Add Viewname, PrintSettings(value True/False), RowColumnSettings(value True/False)

(more…)

Adding Metadata to a Worksheet:CustomProperties

Posted: July 29, 2013 by kulshresthazone in Excel, VBA
Tags:

Want to keep some information a bit out of reach of the user or you want to keep some information for your code support, away from general user.

One way of storing information is using constants in your code. Now, suppose you have multiple sheets and you want to associate the constant’s values with specific sheets. Then, in place of creating a mapping of sorts or creating multiple constants for each sheet, you can make use of adding metadata to a worksheet.
To Add:

Worksheets(WorksheetName).CustomProperties.Add Name:=PropertyName, Value:= SomeValue

To Read:

Worksheets(WorksheetName).CustomProperties.Item(PropertyIndex).Value

Happy Coding…  :)


We protect a worksheet so that it can not be modified and this protection applies to VBA operations and user actions both. Some VBA operations like pasting data cannot be done on a protected worksheet. Now to perform these operations on that sheet we need to unprotect it, do the operations and then protect it again.
e.g.

Sub EditSheet()

    ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="1234"
    ' Code to perform some operations to edit the sheet
    ThisWorkbook.Worksheets("Sheet1").Protect Password:="1234"

End Sub

Although the above technique works but we need to wrap our code between Unprotect and Protect statements in every procedure that performs some operations on the protected sheet. So if we need to protect our worksheet from user actions only then there is a better way of doing it. (more…)


UntitledScenario 1 When we build dashboards in Excel, we add many objects on the worksheet. We may choose to hide some of these or make them all visible, depending on the requirement. What do you do if you want to look at all the objects on a worksheet?

Scenario 2 When you copy data from a website onto an Excel worksheet, sometime certain undesirable invisible shapes and objects also get copied over. How would you work with them if you can’t see them? Or, how would you know how many and where they are?

Did you know that you can use ‘Selection Pane‘ to do this all.

(more…)