Mini Charts Part 2: Sparklines Usage scenarios

In the previous article , we have explored a new kind of mini chart called Sparklines.  In this article, we will explore when and where to use Sparklines to quickly identify trends,  compare data visually and much more…

Quick Recap: Sparklines are mini charts drawn in a single cell based upon data from multiple cells. It is an easy yet effective way of comparing data.

Financial statements

Typical financial statements show data in reverse order. Current year first followed by past years.


You have to read each number from right to left and then compare them mentally to form a pattern. Then we read each row and try to correlate it with other rows.

Now see the same data with Sparklines.

Financial statement with Sparklines

Problem: Chart is visually in reverse order

Look at the Operating Income. At the first glance it looks as though it is decreasing over time. But in reality, it is increasing over time – data is in reverse order.

To avoid this visual confusion, choose the Sparkline Tools –
Axis options – Plot from right to left. Problem solved.


Comparing Quotations, Specifications, Scores

Any kind of comparison can be performed visually using Sparklines as long as the data is numeric.

Visual comparison with Sparklines

Notice how it helps you see a clear difference across the models without reading the numbers. It highlights the winner clearly and without ambiguity.

How does it happen? Because it takes the minimum value across the row as the beginning of the axis. Usually all bar charts start with zero. However in this case, the Power bar chart is starting at 177. Therefore the smaller value becomes visually insignificant.

Due to this visual contrast, it is easier to interpret the information. Of course, remember that this may lead you to underestimate actual values. If you want true to life representation of proportions, traditional charts are better.

Mini Charts: New method of visualizing data

The need

Visual data analysis usually means charts.  There are some limitations of using charts.

  • In some cases, traditional charts don’t work well. Primary and secondary Y axis allows two different scales of data to be shown visually. But more than three scales are impossible to plot.
  • Chart is a separate layer and needs to handled separately.
  • Charts occupy lot of space because there are so many elements to be shown like gridlines, axes, labels and so on.
  • If you try to reduce the chart size, the pattern gets flattened and difficult to interpret.

Visualize data with Mini Charts (Excel 2010 or above)

Fortunately, there is a new way of data visualization available in Excel (since Version 2010) – Mini charts.  Sparkline is a type of mini chart that is drawn in a single cell based upon data from multiple cells. Select one or more blank cells. Choose Insert Sparklines – Line. Select the input data. Click OK.





You can apply this to multiple rows or columns. Make sure you select the data correctly.

If you use this in a table, when more data is added to the table, new Sparkline will be automatically added. You can also increase the Sparkline by dragging it from the corner – just like formulas.

Powerful customization possible

When you Click inside the Sparkline, the Sparkline Tools tab appears. Make sure you go through EVERY option of this menu. You can then customize the visualization for your data quickly.


To show fluctuation across time? Use Line Sparkline

To show relative size of the data, use Bar Sparkline

Mini charts - Bar Sparkline

This diagram uses Line for monthly fluctuation and Bar for country specific values.

To show positive vs negative use Win/Loss

Mini charts - Win Loss

The last column is Win/Loss type of chart. It only depicts positive or negative outcome (Profit / Loss or Pass / Fail or Select / Reject). The line chart shows the fluctuation. In order to visually understand negative values, the Show Axis option puts a reference line at the zero position.

Mark the max or min values for easier interpretation

Mini charts - Mark Min or Max value

In case of bar style, the minimum value is always shown as a thin line. Negative minimum values look better in bars.

In the next article, we will explore some interesting practical scenarios for using Sparklines.

Are you leaking sensitive information via email without realizing it?


  • We create documents and send them to people outside the organization
  • Nobody wants to leak sensitive information intentionally. If you are caught, you will be in trouble
  • But without realizing it, inadvertently, we often leak such information all the time.
  • Don’t believe me? Read on…

Continue reading Are you leaking sensitive information via email without realizing it?

10 reasons to use OneNote

OneNote logo

Why use OneNote?

  • OneNote allows you to create many organizer diaries. Each diary can have many sections (topics) and any number of pages.
  • You can type anywhere, draw anywhere. No saving required.
  • Record audio or video while taking notes. Notes are auto-linked to the audio/video.
  • Amazing, multi-lingual OCR (optical character recognition) even for scanned documents
  • Works in shared mode with SharePoint (or SkyDrive)
  • Live, automatic updated on a single shared page while taking notes in a meeting
  • Links automatically to web pages, Word paragraphs or PowerPoint slides while taking notes
  • Synchronizes across devices
  • Available for free on Android, iOS and Windows platform
  • Tags allow notes to have more meaning

Usage scenarios

Many scenarios listed here. Just scroll. Look at the titles.
If you like it, then read it. Else, skip it.

How many notebooks to make?

  1. Analyze your work. Divide it into broad areas. Each area needs a separate notebook
    1. HR example: You handle Recruitments and Reviews. So make two notebooks to start with. Recruitments and Reviews. Recruitment notebook will have one Section for each department you work with. Each page will contain one position to be filled.
      Reviews notebook will also contain sections for each department. For each employee, create a new page in the respective department.
    2. Sales example: You handle sales for 5 large customers and 15 small customers.
      Create one notebook for each large customer. Create one notebook for small customers. Each customer gets a section. Every activity you do with them becomes a page
    3. CEO example: Create separate notebook for key strategic initiatives. Another one for research. One for competitor information. One for Shareholders. One for key partners (distributors, suppliers)

How to manage meeting notes?

  1. If you have the meeting in Outlook calendar, right click on the meeting and choose Meeting Notes. OneNote will show you all your notebooks. Choose which notebooks the meeting should go into. Now a new page will be created. That page contains all the meeting details and link to the meeting item.
  2. If it is an important meeting, take permission from all attendees and start recording – Insert – Record Audio.
  3. Type few, important notes. These will be linked to the audio automatically.
  4. If the meeting contains visual information, like brainstorming, whiteboard usage, physical objects, artwork, etc., then use Insert Video and adjust the webcam to capture the visual activity (and audio).
  5. If something confidential is being discussed, you can pause the recording.
  6. Audio recording is very lightweight. 10 min audio is usually just 1 mb in size.

Managing To Do items and Tasks

  1. While taking notes, mark action items as TO DO from Home – Tags
  2. If it has a deadline, then choose Outlook Task – and select the deadline. If you want more details – then choose Custom Task
  3. Delegate work while taking notes by choosing Outlook Task – Custom and Assign Task – specify name of the person
  4. This way, before you leave the meeting the work is already created and is easy to monitor as well as execute!

Easy way to manage visiting cards

  1. Create a new notebook called Contacts
  2. Whenever you get cards, take a photo using a simple mobile camera and put all images in that notebook
  3. You can use sections for customers, events, occasions, etc.
  4. You can now search for any name automatically
  5. You can right click on a card and choose Copy Text From Picture and paste it anywhere.

Checklists and Standard Operating Procedures

  1. Create a list of things. Add the To Do tag to all the items. Now it is a checklist.
  2. Now choose Insert – Page Templates – Separate window will open on right side showing all existing templates. At the bottom, choose the option Save current page as a template, give it a name
  3. Next time you want the same checklist, go to Insert – Page Templates – My templates – choose the template

Working with a team on a common project

  1. Create a new notebook on SharePoint. It will automatically create a local copy of the notebook as well.
  2. Send a link to that notebook to your team
  3. Ask them to go to the web page and choose Connect to OneNote
  4. Now let everyone keep typing everything related to the project in that notebook
  5. This works even when you are offline
  6. When there is internet connectivity, OneNote automatically synchronizes all changes with each other
  7. This way, you eliminate the need for sending hundreds of mails with CCs to each other!

Annotating artwork received in PDF files

  1. Usually we use Print Screen to capture the image, annotate in Paintbrush (or similar editor) and send the changes back
  2. Now, open the PDF, choose File – Print – choose the printer Send to OneNote
  3. It will ask you which notebook to print to
  4. The document will become images in the new OneNote page
  5. Open the Drawing tab in OneNote and use pen, highlighter, text to annotate the artwork
  6. When you finish, choose File – Export (or Save As) choose PDF format and send the file to the agency

Take photo notes while reading a book

  1. Take photo of important paragraphs using mobile camera
  2. Same thing works with magazines, newspaper articles, any printed matter
  3. Put photos in OneNote. Add additional notes if required.
  4. Very good idea to capture PR coverage, Competitor advertisements, useful snippets, ideas for writing… anything.

Different style of writing used here

In this article, I tried a different approach. Only Text, quick and easy steps, no graphics.

Do you like the style? Take few seconds to answer this poll. This will help me write more effective articles. Thank You!

What is OneNote? Spend 4 min to find out!


Writing this video from rooftop restaurant in HCMC, Vietnam… amazing view of the bustling city. What is OneNote? Do you know the answer?

panorama rooftop - writing what is onenote article

What is OneNote?

It is part of Office since 2003 . Till version 2007 it was a part of Office Professional edition. So many users never noticed it. But since Office 2010 it is part of standard Office.
Unfortunately, even today (Jan 2017) people don’t know What is OneNote.

OneNote joke

In every session I conduct, I always take an instant poll – how many of you use OneNote.

Usually only ONE hand goes up. Then I say “that is why Microsoft chose the brand name ONEnote!”

But trust me, after a short demo, everyone wants to use it immediately.  Amazing product – when you know it!

View this popular video

This video, created by me, was posted by MS New Zealand some time back. Now it has 150,000+ views. It is not a large number for YouTube popularity. But considering that OneNote is a fairly unknown product, it is a very encouraging sign.

Have a look and you will understand What is OneNote.
More importantly, you will think “why did I not know this earlier!”

The amazingly ad-hoc yet powerful tool : OneNote

This video was created for MS New Zealand in 2011. During those days, I was just learning the art of video creation. Therefore, the video is not as refined as I would want it to be. However, the content is very popular. 123,000+ views as on Jan 2017. It covers OneNote 2010. However all the features are still available.

Video Recap

  1. OneNote is used for storing anything which is not yet a formal document
  2. Use it for taking notes while studying, researching or during meetings
  3. It integrates very well with Outlook. Action points noted in OneNote should be converted to Outlook Tasks.
  4. Use OneNote tags to categorize your notes. Finding tags across notebooks is a great feature.
  5. Do NOT create a single new notebook and stuff everything into it. Create as many empty notebooks as you need, based upon your work.

In the next article, I will discuss How to start using OneNote

Powerpoint: Photo report in few clicks with Photo Album


This article is based upon a question asked by Chandra.

This is a common requirement.

I have multiple pictures and I want to show one picture per slide.

These could be a series of screenshots or views of a new house on sale or construction steps or manufacturing / assembling process…

Needless to say, most of us take the laborious and inefficient path of adding one slide and inserting a picture repeatedly.

As always, there is a better way. The sad part is that this method is available for at least 15 years!


Make sure all the required pictures are in the same folder – it makes this easier to select them.

  2. Make sure NO Presentation is open
  3. Now almost all options are inactive because there is nothing to edit. Don’t worry.
  4. Choose Insert tab and check which option is still active : Photo Album SmilePowerPoint insert photo album
  5. Click File /Disk and select photos (remember you could have connected a Camera or an SD card which appears as a drive – that is why the word DISK is shown here)
  6. Click Insert
  7. By default it creates one slide per picture and resizes the picture to fit the slide
  8. Click Create button

PowerPoint photo album

That’s it. The presentation is ready.

One more step required. Compress Pictures

Usually picture centric presentations tend to be too heavy on size. So here is how you minimize the size. First, save the original slide show. Click on any picture – in Picture Tools – choose Compress Pictures button.

Remove the checkbox from Apply only to this picture, choose 96 ppi resolution and Compress.

This way ALL the pictures are compressed in one go.

Now save the presentation as a copy and compare the file sizes. If the photos are already compressed then you may not achieve significant size reduction.

What more can you do?

Remember that the Photo Album has a dropdown which shows another option – Edit Photo Album.

So if you feel like changing anything from the album, don’t discard the current one and create a new one. Just go to edit, change the desired option and choose Update.

  1. Decide how many pictures per slide
  2. Choose if you want the filename to be displayed
  3. Decide if you want to have a title for each slide
  4. Choose a specific theme
  5. Choose Frame Shape
  6. Do some quick photo editing like rotation, brightness, contract
  7. Reorder the pictures
  8. Decide if you want pictures to be black and white

Generic Learning

Look at the Photo Album button. This is a composite button. It has upper part which does the action – creates photo album and lower part which shows a dropdown and more options. Notice such buttons.

For example the New Slide button is like this. Paste button is another common example. Start noticing the dropdowns and explore. In short:

Drop the Dropdowns and learn more

Excel data cleanup with Flash Fill


One of the participants (Mr. Amjad) in a seminar I conducted asked me this question. The scenario is common enough. But the solution is not even noticed – it is called Flash Fill. Instead, we struggle to solve it manually (and inefficiently).

There is data in one column which contains name followed by amount. We want to split the amount.

flash fill

Doing it using a formula is tricky. Because in some cases there are two spaces, in some cases there is only one space. And may be if the data is large, there could be three spaces as well.

Easy solution: Excel 2013 – Flash Fill

This is an amazing new feature. I will cover it in detail in a separate article. However, here is the way it works.

In column E, specify what you want. Just type the value. NO formula.

flash fill

Then choose Data tab – Flash Fill

flash fill

Do the same thing for the name in column F. That’s it. Excel has an amazing pattern matching engine which does the job for you.

flash fill

What if you don’t have Excel 2013? Use Word!

Then you will have to resort to complex formulas or VBA. But not really. We often get too focused on the product and forget that Office has other tools as well.

Remember. Excel is great with Numbers and Dates. But not text.

Who is the text expert? Word Logo

In fact what Excel 2013 Flash Fill does is technically called pattern matching (Regular Experssions). But Word had this feature since many years.

Let us see how to solve complex Excel data handling problems in Word.

  1. Copy the column data and paste it in Word
  2. It will be pasted as a table. But the table borders will not be visible.
  3. Click inside the pasted data, you will see the Table Tools tab
  4. Click the Layout tab and choose View Gridlines
    Now you can clearly see the tableimage
  5. We want to select only the numbers
  6. Press Ctrl H to open the Find – Replace Dialog
  7. We don’t want to replace anything so click the Find tab
  8. In the Find textbox, type this exactly as shown here [0-9]{1,}
  9. Click the More>> button and choose Use Wildcards optionimage
  10. Now try Find Next multiple times. Notice that it is selecting all the numbers one by one
  11. But we want to select all numbers. So open the Find In dropdown and choose Main Document
  12. Now all the numbers will be selected. Close the Find dialog.image
  13. Press CTRL X to cut the numbers and put them in clipboard
  14. Only the names are left in the first column.
  15. Now add a new column.
  16. Select the second column
  17. Press CTRL V to paste the numbers
  18. Now select the whole table and paste it into Excel
  19. Job done!

For the sake of completeness, the names column will have an extra space at the end. Add a new column and use the TRIM function to remove the trailing spaces if required.

Sample Word file for practice Download

How did this work?

The magic happened in that complex looking search criteria


Wildcard option means do not search what is typed literally. Try to interpret it.

[0-9] Means search for any number between the given range. But that would select only one digit.

{1,} Means that Word should search for one or more instances of number. That is how it selects the entire number

Want to know more?

Click in the Find textbox in Word, enable Wildcards and click the Special button.

You will see the entire syntax of what is possible. This is infinitely powerful and easy to use once you understand the concept. Try it out.

Character in Range


Arrange multiple pictures in PowerPoint in Picture Tools


Yesterday, I conducted sessions for 650+ professionals at a leading automotive manufacturing company… Very smart audience, amazing grasping power.


Here is one of the demos I used in the session…


I have multiple pictures. I want to put them into a SmartArt diagram quickly. But if I use Insert – SmartArt – Picture SmartArt, I have to insert each picture one by one.

That is repetitive. Repetition = Inefficiency Sad smile

Is there a faster, smarter way?


Of course there is. Here are the steps.

  1. Choose Insert – Pictures
  2. Select all pictures and add them
  3. As soon as the pictures are inserted, they are already selected.
  4. DO NOT unselect them.image
  5. Notice that pictures are of different sizes and randomly arranged.
  6. While all pictures are selected, click the Picture Tools tab
  7. Open the Picture Layout dropdown
  8. Notice that all types of Picture SmartArt appear herePowerPoint Picture Tools
  9. Choose the one you want
  10. The size of the diagram will be determined by the size of the picture selection
  11. Adjust the diagram size as neededimage
  12. Open the text editor and add titles if required
  13. That’s it!

From Boring to Exciting – Use PowerPoint SmartArt

In an  earlier post, I explained how to convert a Word document to a PowerPoint presentation in one click (ok… few clicks!). Unfortunately, the resulting presentation is guaranteed to put your audience to sleep. The presentation contains only text. And lots of bullets. Everyone in the world knows that …

PowerPoint SmartArt turns boring presentation into exciting Most presentations are boring! Still we contribute to that boredom by creating more boring presentations. If you want to succeed in your presentations, you should strive to be in the 1% image There are thousands of ways to make your presentation less sleep inducing. But one method stands out. Which is quick and easy.


Convert those boring bullets into interesting and easy-to-understand diagrams.

Boredom to excitement… in few clicks

  1. Right click in your bulleted text and choose Convert to SmartArt.
  2. PowerPoint shows you 20 diagrams to choose from.
  3. Move the mouse cursor over each diagram – it is temporarily drawn … move to next till you find the most suitable diagram to explain your concept.
  4. Remember that most people don’t do this. So you are already in the 5%
  5. But you don’t want to stop there. You want to be exclusive…
  6. So have the courage of clicking on More SmartArt Graphics… (nobody has that courage… so that is your competitive advantage)
  7. Now you will have a big problem

135 diagrams to choose from!

Don’t get disheartened. In a given business context, only one or two of these diagrams are most suited. The real skill is finding the right diagram for your need. Unfortunately, there is no easy way there. You have to put effort.

Homework: Learn 135 diagrams

Spend 30 minutes of your precious life to do this:

  1. Create a new blank presentation
  2. Create a new slide (usually it will be Title and Text type of slide)
  3. Open Insert tab and choose SmartArt
  4. Look at the categories on the left. These will tell you overall categories of diagrams from a business point of view.
  5. Click on each category. Then diagrams of that category are shown.
  6. Now click on each diagram.
  7. On the right side, a bigger version of that diagram is shown. Just imbibe that visually.
  8. Look below… Microsoft has taken the trouble to explain the business context in which the diagram should be used. Read that.
  9. Move to the next diagram.

Spend image minutes of your precious life and see the difference! To stimulate your thought… here are some nice diagrams.

Risk Management

Risk Management SmartArt

Pricing is determined by demand and supply

Supply and Demand SmartArt

Input and Output


Colors, Styles, Animation and Effect options

Try all options on the SmartArt Tabs – Design and Formatting to know more about what you can do. Also apply some simple animation and choose Effect options to see interesting ways in which you can control the animation.

How to get full control?

SmartArt is very nice. But you have one limitation. You cannot change one item independent of others, you cannot group items and so on. Don’t worry. Use SmartArt first to create a base diagram quickly. Then click Design tab and Choose Convert to Shapes. Now it is no longer a SmartArt diagram. It becomes a simple diagram with grouped objects and shapes. Now you can ungroup it and control each shape exactly the way you want. Now you get best of both worlds. Quick diagram and full control.

Homework 2

Take any of your existing presentations. Save it as a copy. Look at existing verbose slides and see if you can improve them using appropriate usage of SmartArt

Pending issue

Creating Picture SmartArt is not easy. You insert the SmartArt first and then you have to choose one picture at a time, repeatedly. And as you know, repetition means inefficiency. In the next post we will see how to create SmartArt containing many pictures (of dissimilar sizes) in two simple steps. Another miracle coming up…

9 benefits of using Excel Tables

If I have to teach ONE thing to the world, it would be Excel Tables.

Excel tables

I recently created this video for a Microsoft Webcast.
Excel Tables were introduced in Office 2007.
This video explains the importance of using Excel Tables.

Prevent Wrong Decisions using Excel Tables

Outdated formulas in Excel are a big operational risk

Tables eliminate that risk proactively.

Additional Benefits of Excel Tables

This in itself can become a series. But here is a short description of the benefits

Auto-update of formulas and references

All dependent formulas are auto-updated across sheets. If you have a linked file, the file must be open for the auto-update to work.

Automatic formatting of tabular data

If you have manual formatting applied, the Table formatting cannot override it. To override, right click on the desired format and choose Apply and Clear Formatting option.

Automatic copying of calculated column formulas

Any formula added in new columns in the table is auto-copied across the table. The formula is copied all the way to the end of the table even if the left column has empty cells.

This saves you the trouble of using time consuming DRAG or error prone DOUBLE CLICK for copying formulas.

If you don’t like this behavior then use the SmartTag to UNDO the auto-copy action.

Automatic copying of calculations when more data is added

This is very useful. If more data is added, calculated columns are auto-filled.

To take advantage of this feature, make sure that you add calculated columns at the end of the raw data. This way, when you get more raw data, you can simply paste it at the bottom of the Table.

Automatic display of headings

Headings are shown in place of regular column headings. It also offers Auto-Filter dropdown (2010 onwards). This eliminates the need for Split and Freeze Pane.

Automatic copying of formatting, conditional formatting and validations

All attributes of cells are auto-copied when you add more rows in a Table.

Ability to use Table and Column names in formulas

Ability to integrate with SharePoint and Visio

You can upload an Excel table to a SharePoint site. It automatically creates a list, creates relevant columns, uploads the data to SharePoint and finally creates a one way (SharePoint to Excel) link with the data.

In Visio you can create a visualization based upon a Visio based Pivot Table functionality.

Achieve more with less effort by using Office 365 platform efficiently. Learn from the Efficiency Guru, Dr. Nitin Paranjape @drnitinp