Posts Tagged ‘Format’

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:


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.


Format() Function for Strings : VBA

Posted: July 2, 2013 by Theodoulus in Excel, VBA
Tags: , , , ,

Many times we encounter with such a situation when we have a “String” and from this string we need to fetch a value in fromat of  Date, Percentage, Currency, Fixed, Scientific, Yes/No, On/Off etc.

Here comes the most used function of VBA, The “Format() Function”.


Expression is the String which you pass in order to fetch the desired output. Format is the optional argument. If you don’t pass Format then you will get the same String which you passed as input.
Although, Excel has it’s own formats for the data but You can define your own [Format] too. Here is the list of the Defined Formats in the Excel 2007:

General Number     -This gives you the same string as you passed as input
Fixed              -Atleast one digit on the left and two digits after the decimal place
Currency           -With Thousand Separators along with 2 decimal places
Standard           -Thousand separator, atleast one digit at the left of decimal place and two digit at
                    the right the decimal place
Percent            -Displays the percent value with two digits at the right of decimal place
True/False         -True for zero and False for Non-Zero
On/Off             -OFF for Zero and On for non Zero
Yes/No             -Displays No for zero and Yes for non-zero


  Format("1234","General Number")            'returns 1234
  Format("1234","currency")                  'returns $1,234.00
  Format("1234","Fixed")                     'returns 1234.00
  Format("1234","Standard")                  'returns 1,234.00
  Format("1234","Percent")                   'returns 123400.00%
  Format("1234","Yes/No")                    'returns   Yes
  Format("1234","General Date")              'returns 5/18/1903
  Format("1234","Long Date")                 'returns Monday, May 18, 1903             
  Format("1234","MMM, DDDD YYYY")            'returns May, Monday 1903

Did You Know :: Format Comment Box

Posted: June 16, 2013 by MaxFraudy in Excel
Tags: , ,

UntitledWe often use cell comments to keep certain information in a spreadsheet which we may want to refer back to later. When you are creating a dashboard in Excel, and have formatted everything from a cell to a chart to look splendid, the boring-looking cell comment boxes can let your otherwise awesome report down. Did you know that you can format comment boxes just like you format a shape?

To do so, you will need to add a command to the QAT (see here how). Select ‘Shapes‘ from the list of commands and add it to the QAT.


UntitledAlthough Excel is a primarily a spreadsheet program, which means that it has been designed to store numbers, we often need to use some cells to store text. Also, at times we need to use ‘Word’-like features like line breaks or bullet points. Did you know that you could achieve it in Excel this way:

For bullet points – use [Alt] + 7 or [Alt] + 9 key from the numeric keypad on your keypad. The former combination will give you a filled-in bullet and latter will give you an empty bullet. In laptops where there is no dedicated keypad, use the [Fn] + [Num Lock] or [Shift] + [Num Lock] key to enable the numeric keypad and then use the shortcut for bullets.

Bullets Points in Excel Cells

Bullets Points in Excel Cells

For line breaks, use [Alt] + [Enter] key

Line Breaks in Excel Cells

Line Breaks in Excel Cells



Using [Alt] + any number from the numeric keypad inserts the character associated with that ASCII code. In the above case, it’s the two circles.



Paste As Link

Posted: May 27, 2013 by Theodoulus in Excel
Tags: , , ,

You have created a dashboard in which you have applied all the required formatting like border, color, cell merge, background, font- style, margins, row-column height or width etc. You have data in the dashboard and you have spent many hours in this whole creation.
What if you want to show the same dashboard in a different sheet too (means all the efforts you did in the previous sheet while creating that dashboard).
Solution: In this situation, Excel provides us “Paste Picture link” by which we can get all the desired output. You just need to copy the range of the dashboard that you created and paste it anywhere you want as “Paste Picture Link”. Now you are the GOD for the dashboard means if you are extending the row height, unmerge the merged cell, giving different background image and even your data is dynamic (getting changed automatically) or anything else you want in your Parent dashboard.

Isn’t this a smart way of doing things!
So how to accomplish the task, let’s see:

First Create you Dashboard………………………..
Now Select the range or the whole area of the dashboard and copy it.
Go to that place where you want this to be pasted and find the paste option on the HOME tab and Paste Picture Link


Now whatever change would come in the original Dashboard will be reflected in the Picture Link which you have pasted.

See You Soon…………

UntitledWhen we create presentations Powerpoint, we spend quite a lot of time in getting the look and feel right. Among other things, this involves applying similar formats to all shapes and pictures. In my experience, shapes are the trickiest part. There is so much customisation and so many options to do so, that there is a chance that a few parts may be missed in some shapes.

To avoid this, you can set defaults. To do so, insert a shape (any of the available ones), apply formatting changes that you want – fill color, shape outline, shape effects, shape style, etc. When you are sure that you have the look of the shape right, right click on the shape and select ‘Set as Default Shape‘. (more…)

Creating Powerpoint Slides: Rules

Posted: May 17, 2013 by MaxFraudy in General Tips, Powerpoint
Tags: ,

I recently came across some very poorly made presentations which prompted me to create this deck. It has some rudimentary pointers that need to be kept in mind while creating powerpoint slides.