RSS

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

 

Enter Same Data in to Multiple Cells

Here is a quick Excel tip to kick start your time.

Sometimes, we want to enter same data in to several cells. You can use CTRL+Enter to do this in a snap.

  1. Select all the cells where you want to enter the same data.
  2. Type the data
  3. Press CTRL+Enter
  4. Done!

Method 2

  1. Copy the cell
  2. Drag up to the cell you want
  3. Press Ctrl+D

Hope this helped you. Stay tuned to know more.

 

 
Leave a comment

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

 

Remove Duplicate Rows / Values

Getting Unique Items from a List of Cells

There are 3 simple ways to do this:

  • Using Advanced Data Filter
  • Using countif() and auto filter
  • Using formulas as described here

Assuming you have data as shown in the picture aside (and wishing you will have customers like those):

First add a column to the left of the list. Here we will use formulas to fill numbers based on the uniqueness of the cell next to it. Essentially our formula should generate numbers in increasing order as long as the corresponding item is unique and not increase the number otherwise. So the formula for order column can be like this: =IF(COUNTIF(list-upto-that-point, current element)=1,previous-order+1, previous-order)

Now add another column to the right of the list, here we will fetch unique items. We will use vlookup() to fetch each of the 12 unique items. The formula goes like this:

=VLOOKUP(running number,$B$4:$C$22,2,FALSE)

You can wrap the vlookup() with if() formula to avoid seeing #value errors. That is all. Using this method you can extract unique items from a list.

Eliminating Doubles from a List

There are 2 ways in which you can find and remove duplicates(doubles) in excel lists with ease:

  • Using countif() and then auto-filter
  • Using formulas

The process for finding duplicates using formulas is same as that of finding unique items.

Instead of writing COUNTIF(list-upto-that-point, current element)=1, we now write COUNTIF(list-upto-that-point, current element)=2. Also the first element’s count should be changed to zero.

Once done the list should look like what you see on the side.

Reference: www.chandoo.org

 
Leave a comment

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

 
 
%d bloggers like this: