Tag Archives: High Profiency

Useful Tricks in MS Excel

1. Change the shape / color of cell comments

Just select the cell comment, go to draw menu in bottom left corner of the screen, and choose change auto shape option, select a 32 pointed star or heart symbol or a smiley face, just wow everyone

2. Filter unique/ Custom Sort/ Advanced Filter items from a list

Select the data, go to data > filter > advanced filter and check the “unique items” option.

3. Sort from Left to Right

What if your data flows from left to right instead of top to bottom. Just change the sort orientationfrom “sort options” in the data > sort menu.

4. Hide the grid lines from your sheets

Go to Options dialog in tools menu, uncheck the “grid lines” option to remove gridlines from your worksheets. You can also change the color of grid line from here (not recommended)

5. Add rounded border to your charts, make them look smooth

Just right click on the chart, select format chart option, in the dialog, check the “rounded borders”. You can even add a shadow effect from here.

6. Fetch live stock quotes / company research with one click

Just enter the stock symbol (MSFT, GOOG, AAPL etc.) in a cell, alt+click on the cell to launch “research pane”, select stock quotes to see MSN Money quotes for the selected symbol. You can fetch company profiles in the same way.

7. Repeat rows on top when printing, show table headers on every page

When you are on the sheet view, just hit menu > file > page setup, go to the last tab, specify “rows to repeat”. You can “repeat columns while printing” as well from the same menu.

8. Remove conditional formatting / all formatting with one click

Just go to Menu > Edit > Clear > All to remove all the formatting from selected cell / range.

9. Auto sum cells with one click








Select a bunch of cells and click on the Sigma symbol on the standard tool bar. Alternatively you can use Alt+= keyboard shortcut

10. Find width of a column with formula, really!

Just use =cell("width") to find the width of the column to which that formula cell belongs. Width is returned as the nearest integer.

11. Find total working days between any two dates, including holidays

If you work on project plans, gantt charts alot, this can be totally handy. Just type=networkdays(start date, end date, list of holidays) to fetch the number of working days. In the above sample you can see the number of working days between New years day and September first of this year (labor day).

12. Freeze Rows / Columns in your sheet, Show important info even when scrolling

Select the cell diagonally beneath the row / columns you want to freeze (for eg. if you wan to freeze row 1&2 and columns A&B, click in C3), go to menu > window and click on freeze panes.

13. Split sheets in to two, compare side by side to be more productive

Just click on this little vertical bar on the bottom right corner of the sheet (see below) and drag it to create a vertical split. You can do the same way for a horizontal split as well

14. Change the color of various sheet name tabs

Right click on sheet and select “Tab color” option to change the worksheet tab colors. Group them with similar colors if you have lot of sheets, it looks nice.

15. Insert a quick organization chart

Click on menu > insert > diagram to open the above dialog, just select the organization chart option, enter node values and you have a pretty organization chart.

16. Filter : the most used feature of MS Excel.

Leave a comment

Posted by on March 6, 2012 in MS Excel, My Home


Tags: , ,

Pivot Table

PivotTables are an interactive table that allow the user to group and summarize large amounts of data in a concise, tabular format for easier reporting and analysis. They can sort, count, and total the data, and are available in a variety of spreadsheet programs. One advantage of this feature in Excel is that it allows you to rearrange, hide, and display different category fields within the PivotTable to provide alternate views of the data. Read on to find out how to create your own pivot table in Excel.

Pivot Table

Brush up on key terms. There are several terms which may seem unclear upon cursory glance of the Excel pivot tables. Report filter. This area contains the fields that enable you to page through the data summaries shown in the pivot table by filtering out sets of data. They act as the filters for the report. For example, if you designate the Year field from a table as a Report Filter, you can display data summaries in the pivot table for individual years or for all years represented in the table.

Pivot table

  • Column labels. This area contains the fields that determine the arrangement of data shown in the columns of the pivot table.
  •  Row labels. This area contains the fields that determine the arrangement of data shown in the rows of the pivot table.
  •  Values. This area contains the fields that determine which data are presented in the cells of the pivot table — they are the values that are summarized in its last column (totaled by default).
  •  Page field. A field from the source data that you assign to a page (or filter) orientation in a PivotTable report.

Pivot Table

  •  Data field. A field from the source data that contains values to be summarized. For example, Sum of Sales is a data field.
  •  Column field. A field from the source data that you assign to a column orientation in a PivotTable report. For example, Type is a column field.
  •  Item. A subcategory of a row, column, or page field.
  •  Row field. A field from the source data that you assign to a row orientation in a PivotTable report. For example, Region and Salesperson are row fields.
  •  Data area. The cells in a PivotTable report that contain summarized data.

Hope it helped you. Plesae feel free to coment or drop mail on

Leave a comment

Posted by on February 26, 2012 in MS Excel, My Home


Tags: ,

%d bloggers like this: