RSS

Category Archives: MS Excel

MS Excel, the analytical tool

Excellent Representation of Data

Advertisements
 
1 Comment

Posted by on May 7, 2012 in MS Excel

 

ISBLANK()

The ISBLANK function’s job is to check to see if a certain cell is empty or not. If the cell is empty, a value of TRUE is returned by the function.
If data is later added to an empty cell the function will automatically update and return a FALSE value.
The syntax for the ISBLANK function is:
= ISBLANK ( Value )
Value: Refers to the cell reference that is being checked.
Example Using Excel’s ISBLANK Function:
For help with this example, see the image to the right.
  1. Type a number, such as ” 3 “, or a word of text into cell.
  2. Click on cell – the location where the results will be displayed.
  3. Click on the Formulas tab.
  4. Choose More Functions > Information from the ribbon to open the function drop down list.
  5. Click on ISBLANK in the list to bring up that function’s dialog box.
  6. Click on cell, in the spreadsheet to enter the cell reference into the dialog box.
  7. Click OK.
  8. The value FALSE should appear in the resulted cell, because value cell is not empty.
  9. The complete function = ISBLANK ( cell ) appears in the formula bar above the worksheet when you click on cell.
  10. To return a value of TRUE in cell, delete the data in value cell or change the cell reference in the function to a cell that is empty.
 
Leave a comment

Posted by on May 5, 2012 in MS Excel, My Home

 

Why do we need Excel Template ?

Create a Template in Excel 2010

A template in Excel is a spreadsheet file containing common data and formatting options that is used as a model for other spreadsheets.

  1.  Formatting can include font and layout changes, conditional formatting, color changes, and any other available options.
  2. Charts can added to the template file as can formulas, functions, look up tables, and macros.

Steps to creating a template in Excel

  1. Enter all of the necessary data.
  2. Add or remove rows and columns as needed.
  3. Create formulas, charts and other options.
  4. Apply all formatting options.

 To save the template once all changes have been made to your spreadsheet:

  1. Click on the office button to open the drop down menu.
  2. Choose the Save As option.
  3. Choose the Other formats option.
  4. Choose the Save As option to open the Save As dialog box.
  5. Click on the Save as type option to open the drop down list.
  6. Scroll through the list to find the template options.

There are three templates that can be created in Excel:

  1. Excel Template (*.xltx)
  2. Excel Macro Enabled Template (*.xltm)
  3. Excel 97 – 2003 Template (*.xlt)

If your template contains macros:

  1. choose the Macro Enabled Template (*.xltm) option
  2. If you plan to use your template with older versions of Excel:
  3. choose the Excel 97 – 2003 Template (*.xlt) option

For all other templates:

choose the Excel Template (*.xltx) option

Once saved, your template is stored in the templates folder on your computer.

 
Leave a comment

Posted by on May 5, 2012 in MS Excel, My Home

 

How to remove duplicates from Excel 2010?

Microsoft has made it quicker to remove duplicate rows in Excel 2010, all it takes now is two simple steps. The best part is that you don’t need to select any specific row before removing the duplicate rows, the build-in Remove Duplicate feature takes care of it.

If you want to get rid of all duplicate columns in an excel spreadsheet, click Select All, so that all columns are selected and then click OK.

There is also another way of doing it. It’s bit longer but more useful.

IInsert table>remove duplicates

Now you can remove duplicates or lot many things…..

see the multiple options in the menu bar under “format table”

 
Leave a comment

Posted by on May 4, 2012 in MS Excel, My Home

 

Freeze Panes

Excel Freeze Panes Overview

When working with very large spreadsheets headings located at the top and down the left side of the worksheet often disappear if you scroll too far to the right or down. Without the headings, it’s hard to keep track of which column or row of data you are looking at.

To avoid this problem use Excel’s freeze panes feature. It “freezes” or locks specific columns or rows of the spreadsheet so that they remain visible at all times. This tutorial covers the freeze panes options in Excel 2007 / 2010.

The steps in this tutorial are:

  1. Freeze the top row
  2. Freeze the first column
  3. Freeze both columns and rows
  4. Unfreeze columns and rows

Freeze the Top Row

  1. Click on the View tab.
  2. Click on Freeze Panes from the ribbon to open the features drop down list.
  3. Click on Freeze Top Row option in the list.
  4. A black border should appear beneath row 1 in the worksheet indicating that the area above the line has been “frozen”.
  5. Scroll down through the worksheet. If you scroll far enough, the rows below row 1 will begin disappearing while row 1 will stay on the screen.

 Freeze the First Column

  1. Click on the View tab.
  2. Click on Freeze Panes from the ribbon to open the features drop down list.
  3. Click on Freeze First Column option in the list.
  4. A black border should appear to the right of column A in the worksheet indicating that the area to the right of the line has been “frozen”.
  5. Scroll to the right in the worksheet. If you scroll far enough, the columns to the right of column A will begin disappearing while column A will stay on the screen.

 Freeze Panes Using the Active Cell

  1. The Freeze Panes option freezes all the rows above the active cell and all the columns to the left of the active cell.
  2. To freeze only those columns and rows you want to stay on screen, click on the cell to the right of the columns and just below the rows that you want to remain on screen.
  3. Example of freezing panes using the active cell
  4. To keep rows 1, 2, and 3 on the screen and columns A and B:
  5. Click in cell C4 with the mouse to make it the active cell.
  6. Click on the View tab.
  7. Click on Freeze Panes from the ribbon to open the features drop down list.
  8. Click on Freeze Panes option in the list.
  9. A black border should appear to the right of column B in the worksheet and below row 3 indicating that the areas above and to the right of the lines have been “frozen”.
  10. Scroll to the right in the worksheet. If you scroll far enough, the columns to the right of column B will begin disappearing while columns A and B will stay on the screen.
  11. Scroll down through the worksheet. If you scroll far enough, the rows below row 3 will begin disappearing while rows 1, 2, and 3 will stay on the screen.

 To Unfreeze Columns and Rows

  1. Click on the View tab.
  2. Click on Freeze Panes from the ribbon to open the features drop down list.
  3. Click on Unfreeze Panes option in the list.
  4. The black border(s) showing the frozen columns and rows should disappear from the worksheet.
 
Leave a comment

Posted by on April 30, 2012 in MS Excel, My Home

 

Excellent Dashboards in MS Excel

This slideshow requires JavaScript.

 
Leave a comment

Posted by on April 25, 2012 in MS Excel, My Home

 

Moving Bar chart in MS Excel

It’s very important to learn some simple tricks to go on the way of preparing “DASHBOARD”. Here we’ll learn one part of the dashboard.

The circled part is what we’ll learn here. Let’s say you want to show 50% of some target as completed. Here is how it works.

1> Create a bar chart by taking any number between 0 to 100. It looks as below image.

2> Go to format Axis and change the min, (0) max (100), Major unit and minor unit as below image.

3> “Delete” both the axis.

4> Format Chart Area.  Make fill color to “no color” and Border color to “no color”.

5> Insert New Shape, just little bigger than the graph.

6> Send the shape behind the graph. It’ll look like the below image.

7> Connect the cell marked (50) as per your requirement and see the changes on graph.

Note: restrict the cell value from 0 to 100 by “data validation”.

Hope this helped you. Thanks

 
Leave a comment

Posted by on April 18, 2012 in MS Excel, My Home

 
 
%d bloggers like this: