FunctionsConditional Formatting for visual data analysisLearn about data visualization & charting

Repeat fill cells

Previous to excel 2010(ie 2007 & before), there is no option of repeating labels for pivot tables. This delivers an output as given below:

But this is grossly inadequate if you want to use this pivot output for further analysis (like vlookup on this to check the forecast of Namibia on product Motor – E3). What we want is to repeat the labels for all cells for which it is applicable.

Data Bars

Data bars provide a quick way of visualizing the data at hand. In essence it provides an in-cell bar graph to represent the data of that cell. Length of the bar depends on the number contained in the cell and is relative to other cell values for which this visualization is selected.

We take you through a quick overview of this functionality through an example.

Introduction to data analysis & visualization through excel

With the advent of cheap computation power (PC), the amount of data and related graphs/charts & info-graphics has seen a tremendous growth in last few decades.  While there are hundreds of specialized tools available in the market, excel remains the most widely used tool due to its mass reach.

Excel provides a great set of tools for data analysis and visualizations. With a bit of creativity & efforts it can be extended beyond the obvious uses to produce stunning results.

Hide/Un-hide Rows

Keyboard Shortcut for Hiding & Unhiding Row/s

  1. To hide selected Row/s, select complete Row/s which we want to hide & press CTRL + 9 – see picture below.

    Hiding excel row

In fact, clicking on any cell (rather than the whole row), will also produce the same result, please give it a try.

Quick Copy command

There are certain scenarios when we may want to copy the content from other cells. While standard copy/paste (or Ctrl C/Ctrl V) work all the time, for adjacent cells there is a faster way of doing the same.

Insert Date and Time-Stamp

If we want Excel to record when we last accessed a particular workbook/ worksheet, we can leave our date and/or time-stamp in same sheet by using a short key method given below –

For inserting today’s date, Press CTRL+;

For inserting current time, Press CTRL+SHIFT+:

Fix an area(Freeze panes)

We learnt about the Excel’s functionality of freezing top row or left most columns in the previous post. Another usual requirement will be to freeze a set of rows & columns on the sheet while viewing the sheet. To explain this better, please open the attached excel (View_freeze panes.xlsx). In this, suppose we want to keep the left most Item descriptors (Column A) as well as the months visible all the time while we navigate in the sheet (down or to the right side).

Writing Formula in Microsoft Excel

Formula in Microsoft Excel begins by typing an equalto(=) into any empty cell. Exce understands equal(=) sign and assumes that you are entering a formula.

Excel also allows you to begin your formula with a equalto sign(=) or a plus sign(+) or @ symbol. Once you complete your formula and press Enter, Excel inserts a leading equalto sign in front of formula.



Fix top row (freeze) or the first column

Freeze first row:

It is common to have a list of data rows in excel, with the first row containing headers (meaning of the columns). It is quite possible that there are lots of records in this list, so that when you scroll down, the headers go out of sight.

Understanding the View tab

Views Tab holds access to certain regularly used routines & shortcuts. The most common of which has to be freeze panes. Following is a brief description of all the major command groups:

Views Ribbon menu