RSS

Tag Archives: Excel Tricks

Wanna work faster in Excel !!

Keyboard Shortcut to Go To Formula Bar

 To enter formulas in an Excel worksheet, just select a cell and start typing. To edit an existing formula, you probably make changes in the Formula bar (above the column headings) which you can access with a click of the mouse. That method’s fine but some people (like me) prefer working with the keyboard as much as possible. If that describes you, press the [F2] function key instead of reaching for the mouse the next time you want to edit an existing formula.

Excel Shortcuts for Editing a Cell

When you use [F2] to edit a cell, by default, [F2] positions the cursor in the cell (for “in place editing”) instead of the Formula bar. To change the [F2] function key so it moves directly to the Formula bar:

Excel 2007:

  • Click the Office button and then pick Excel Options (at the bottom right).
  • Select Advanced in the left pane.
  • Uncheck the Allow editing directly in cell option. OK to apply.

Excel 2010:

  •  Click the File tab and then pick Options (at the left).
  • Select Advanced in the left pane.
  • Uncheck the Allow editing directly in cell option. OK to apply.

Turn Formula AutoComplete on or off

Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.

Under Working with formulas, select or clear Formula AutoComplete.

TIP   You can also press ALT+DOWN ARROW.

Enter an item from the drop-down list by using an insert trigger

 As you are typing a formula, even after using an insert trigger, don’t forget to type the closing parenthesis for a function, closing bracket for a table reference, or closing quotation mark for an MDX text string.

To insert the selected item into the formula and put the insertion point directly after it, press TAB, or double-click the item.

Hope this would help you to make your work more faster. Feel free to drop mails at thinkandbegin@gmail.com

 
Leave a comment

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

 

Tags: , , , , ,

The One-Click Chart Method

You have to create a bunch of charts. You usually select all of the defaults in the Chart Wizard. Can you speed up the process?

The Excel Chart Wizard is a very useful tool, especially if you’re making several changes to the standard chart format or you want a little guidance on what type of chart to create from your data. At other times, the Chart Wizard just seems to get in the way and it can slow you down. This is particularly true if you need to create several Excel charts, and you want the task done as fast as possible.

If you’re faced with this latter scenario, there is a much quicker way to get the job done – you actually can create a chart from your data with a single click.

You can create a chart with one keystroke! Select the data, including the headings and row labels, as shown in Figure.

Press the F11 key or (Alt+F1). Try both and see the difference. The data is charted (on a new chart in case of F11)  sheet, as shown in Fig

Hope this will help you in some context.

 
Leave a comment

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

 

Tags: , ,

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: , ,

Loan payments Calculation in Microsoft Excel

Formulas that calculate loan payments, principal, interest and more are found in the financial category in the Paste Function dialog box. In the screen shot is a list of functions and necessary syntax for loan calculations.

Syntax for loan calculation functions

  • Rate – The interest rate per period.
  • Per – The period for which the interest rate is calculated.
  • Nper – The total number of payments.
  • Pv – The present value, the total amount that a series of future payments is worth now.

ScreenShot

Type – The timing of the payment, either at the beginning or end of the period. Numbers 0 or 1 represent the payment date. The number 0 represents payment at the end of the period, and the number 1 represents payment at the beginning of the period. The default (empty argument) is 0. The calculation is at the end of the period.

PMT (Rate, Nper, -Loan Amount)

Calculates the payment for a loan based on constant payments and a constant interest rate. Returns the regular monthly payment on the loan (principal + interest) when the interest for each of the monthly payments is constant. Example: The principal of a loan is $100,000, and the term of the loan is three years. The monthly payment during the term of the loan is calculated at $3,227; see column B in the figure below.

PPMT (Rate, Which Period, Nper, -Loan Amount)

Returns the amount on the principal for a given period for a loan based on periodic, constant payments and a constant interest rate.

Returns the sum of the principal within the monthly payment (the monthly payment is comprised of the principal + interest). See various examples of calculations in column C, rows 8:10, and the formula syntax in rows 14:16.

IPMT (Rate, Which Period, Nper, -Loan Amount)

Returns the interest payment for a given period for a loan based on periodic, constant payments and a constant interest rate. Returns the amount of the interest within the monthly payment (the monthly payment is comprised of the principal + interest). See the calculation in cell G11 and the formula syntax in G13.

NPER (Rate, Pmt, -Loan Amount)

Returns the number of loan payments with a constant interest rate. See the formula syntax in D12.

RATE (Nper, Pmt, -Loan Amount)

Returns the interest rate per period of a loan. RATE is calculated by iteration and can have zero or more solutions. Returns the percentage of interest on the loan, when the number of payments is constant.

PV (Rate, Nper, Pmt)

PV is the present value — the total amount that a series of future payments is worth now. Returns the current value for a series of payments with a constant interest rate.

Hope this is helpful. Feel free to reach me at thinkandbegin@gmail.com

 
Leave a comment

Posted by on March 3, 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 thinkandbegin@gmail.com

 
Leave a comment

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

 

Tags: ,

“Consolidate” in MS Excel

To summarize and report results from data on separate worksheets, you can consolidate the data from each separate worksheet into one worksheet (or master worksheet). The worksheets you consolidate can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data in one worksheet, you can more easily update and aggregate it on a regular or ad hoc basis.

For example, if you have a worksheet of expense figures for each of your regional offices, you might use data consolidation to roll up these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.

There are two main ways to consolidate data:

Consolidate by position    Use this method when the data from multiple source areas is arranged in the same order and uses the same row and column labels. For example, when you have a series of expense worksheets that are created from the same template.

Consolidate by category    Use this method when the data from multiple source areas is arrange differently, but the same row and column labels are used. For example, you can use this method when you have a series of inventory worksheets for each month that use the same layout, but each worksheet contains different items or a different number of items.

You can consolidate data by using the Consolidate command (Data tab, Data Tools group). You can also consolidate data by using a formula or a PivotTable report.

For more details pls visit youtube or reply me for reference.

 
Leave a comment

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

 

Tags: , , ,

Very Useful MS Excel Short-Cuts

Here are very few but very useful MS Excel short-cut keys which will not only make your work easier but also make your work more faster and accurate….. Just try it out…….

Ctrl+H

Find&Replace
Ctrl+F4, Alt+F4 Close, Close Excel
Ctrl+Arrow Move to edge of region
Ctrl+* Select current region
Ctrl+Home Ctrl+End Select A1, Select last cell in used range
Ctrl+Shift+End Select from active cell to last cell in used range.
Ctrl+Shift+Home Select from active cell to A1
Ctrl+Page Down Ctrl+Page Up Move to the next sheet, Move to the previous sheet
Ctrl+Tab Move to next open workbook
Ctrl+N Open new workbook
Shift+F11 Insert new worksheet
Shift+F3 Paste function window
Ctrl+Spacebar Shift+Spacebar Select columns, Select rows
Ctrl+; , Ctrl+shift+: Current date, Current time
 
2 Comments

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

 

Tags: , ,

 
%d bloggers like this: