Home‎ > ‎Categories‎ > ‎

MS-Excel

For better representation of graph in a cell, create Sparkline in Excel 2010. 

To create a set off figures in a cell with Sparklines, on the Insert tab on the Ribbon, locate the Sparklines group. Select the type of Sparklines you want to insert, such as Line, Column, or Win/Loss. In the Create Sparklines dialog box, define your Data Range and Location Range, and then click OK.
Import an Access Database into Excel 2010. 

In Office 2010, you can import MS Access database files in MS Excel. To import Access file, open a new Excel file, click on the Data tab > Get External Data click on From Access. Browse the location of the MS Access database file you want to import. It will prompt for selecting the database, in case you have several tables.
Insert screenshots in PowerPoint 2010 documents. 

Open PowerPoint, go to Insert tab in the ribbon, select Screenshot under Images group. Available screenshots will be displayed, user can select one of those or if user wants to have his own screenshot click on Screen clipping option and select the area for the screenshot. Ensure that the document from which one want to clip the screenshot should be right below the office document. Now your office document is ready with screenshot of your own choice.1
Highlight positive and negative values in an excel using conditional formatting in Excel 2010. 

Open Excel application, select the row or column having positive and negative values, go to home tab in the ribbon, select Conditional Formatting in Styles group, select Data bars, from there click on More Rules. New Formatting Rule dialog box will pop up. Select a rule type, edit rule description on how to display positive values, define minimum and maximum values. For negative values click on Negative Value And Axis option Negative Value and Axis Settings dialog box will pop up, here user can select the color by which cells having negative values will be filled. Also select Axis settings and color. Axis is the thin dotted line separating positive and negative bar in the cells. Click OK to close the dialog box and user is now able to easily distinguish between positive and negative values in the cells.
Customize your own Ribbon in Excel 2010. 

Open the Excel application. Click on Office button on left top corner of the application. In this view in last tab click Options. Different options are been displayed, select Customize Ribbon, Customize Ribbon and keyboard shortcuts view will be opened at the right hand side. Click on the New Tab button to create your own tab, New Tab branch will be created. From Choose comands from list select and add your own commands in this new tab. Use remove button to remove the command. Click on rename button to give your own name to this tab. Click OK to close the Rename dialog box and click OK to save your changes. Now when office button is closed and user is into the Excel application, user will be able to view set of useful commands of his choice on new tab just created. 

Kindly note, this tip is posted before release of MS Excel 2010. This tip might change at the time of release.
Safegaurd your workbook to interact with other applications to protect sensitive data in Excel 2003

Open your workbook which need to be protected. Go to Tools menu > Options > General tab. Select the checkbox Ignore other applications. Click OK to close the dialog box and save your changes. To open the workbook first open excel application and then open your workbook i.e. double clicking on workbook directly may not open the document.
Password protect your workbook to aviod any modifications in Excel 2003. 

Open your workbook which need to be password protected. Go to Tools menu, select Options, go to Security tab. Enter a password next to Password to open and Password to modify labels. Click OK to close the dialog box and save the changes. Your workbook is now protected from any unauthorized access not only for opening but require password also to modify the workbook.
Close Excel files at once. 

If you want to close all files at once then then hold Shift key and select File > Close All .
Add a background to your sheet. 

To add a background in your sheets, select Format > Sheet > Background.
When you type a number it automatically takes it in decimal. 

To control this, select Tools > Options > Edit and check the option Fixed Decimal.
Want a precise result when you sum up your numbers. 

When you want a precise result then select Tools > Option > Calculation tab and select the option Precision as displayed under Workgroup Options. Now they will make calculations on numbers as they are displed on screen.
How to handle multiple sheets, if you want a common footer or header. 

If you want to handle multiple sheets, select your sheets by holding ctrl key and click on the sheets. In this way you select the sheets. Now edit the header and footer. By this you have common Header and Footer. To ungroup them, righ-click on any sheet tab and select Ungroup Sheets.
Want to format a cell with the currency, unable to find Rupees Rs

MS Excel uses Windows Regional Settings, to display currency symbol change setting from Control Panel > Regional Settings > Currency here change the Currency Symbol to Rs. Now you have Rs symbol in Excel.
View Excel sheets without Ms Excel. 

You can still view Excel Sheet by opening it in Internet Explorer. Drag file from explorer to IE window.
Long text to be entered, use Wrap text

When you have to type long lines of text, then text generally troubles you in adjusting cell size. Select that cell after typing text, right click on it, select on Format Cells > Allignment tab, here check the Wrap text option. This will definitely help you.
Protect your file with a password. 

While saving your file using File > Save As you will find another button of Option. Here you can enter the password for the file protection.
Change the default sequence. 

Generally when we press the Enter key, it takes you to the cell below it, but you can change this sequence. Select Tools > Options > Edit here under check box, Move Selection After Enter you can find options to change direction in drop-down list.
Repair Excel files. 

Excel Xp does an automatic repair to corrupt files. It tries to open and repair them. However you can also do a manual repair. Select File > Open select a file, and click an arrow button next to Open button and choose Open and Repair from drop-down list. It gives an option to Repair, Extract data or cancel.
Want to have math operations to evaluate formula in Excel 2003

Open Excel in which you want to evaluate formula > Go to the cell where formula is been entered > Go to Tools option in menu > Click Formula Editing > Select Evaluate Formula > Click Evaluate option to watch as excel evaluates each step of the formula.
Hiding merge cells. 

If you try to hide a column when one of the cells in that column is merged with other, some of the adjacent cells are also hidden. To solve this, first unmerge the cell then merge cell. Format the cells dialog box and click Merge cells to remove the check-mark.
Comments are useful in Excel. 

Comments are useful in sheets, so that you can easily make notes, right click on the cell that contains the comments and choose Show Comment. You can also hide this by right-click the cell and choose .
Functions need not necessarily be limited to adjacent cells

Commas indicate individual cell addresses and colons indicate contiguous groups of cells.
Customise your recently used files list

You can customise the Recently used file list, by this you can increase or decrease the number of files that will be displayed. Select Tools > Options > Recently use file list, Specify the number you want to see the files in the list. Click Ok.
Share workbook in Excel 2003. 

Open Excel 2003 > Go to Tools menu > Select Share Workbook option > Navigate to Editing tab, select the checkbox besides the option Allow changes by more than one user at the same time. This also allows workbook merging > Now navigate to Advanced tab and in Update changes option select Automatically every n number of minutes > Click OK.
Hide a WorkSheet in Excel. 

Open Excel > Click Worksheet that you want to hide > Go to Format menu, point to sheet and then click hide. To unhide a worksheet, in format menu, point to sheet and click unhide > Select the sheet you want to unhide and click OK.
Enter data into multiple excel sheets in Excel 2003

Open excel 2003 document > Hold down ctrl key while selecting worksheets you want to group. After this action you will notice worksheets turn white > click on any grouped worksheet and enter your data. Data will automatically be added in other grouped worksheets > To ungroup right click any sheet and select Ungroup sheets option.
Form a simple formula in Excel 2003

Open Excel 2003 > Select the cell where you want to display the result of the formula, type equal sign (=) in the cell > Click the first cell that you want to use in the formula > Type one of the operator based on which mathematical operation you want to perform (+,-,*,/) > Now click second cell you want to use in the formula > Press enter.
Share workbook in Excel 2003

Open Excel 2003 > Go to Tools menu > Select Share Workbook option > Navigate to Editing tab, select the checkbox besides the option Allow changes by more than one user at the same time. This also allows workbook merging > Now navigate to Advanced tab and in Update changes option select Automatically every n number of minutes > Click OK.
Hide and Unhide columns in Excel.

Open Excel > Select the entire cloumn that you wish to hide > Right click > Select Hide > To unhide column that has been hidden > Select the column to the right and column to the left of the hidden column > Right click and select Unhide.
Adding a Cell to Watch Window in Excel 2007 Open Excel 2007 > Select the cells you want to watch > Go to View tab > Toolbars > Select Watch Window > Click Add Watch > Click Add. You will see watch window appear and it will remain on top as you work or it may be dragged and dropped to it's new location.
Open spreadsheet at start up. 

To start the spreas sheet as soon as you start Excel, then save that spread sheet in folder Program Files\Microsoft Office\Office\XLStart, if you have made changes in intalling Excel at different location, then make changes accordingly.
Enter data into multiple excel sheets in Excel 2003

Open excel 2003 document > Hold down ctrl key while selecting worksheets you want to group. After this action you will notice worksheets turn white > click on any grouped worksheet and enter your data. Data will automatically be added in other grouped worksheets > To ungroup right click any sheet and select Ungroup sheets option.
Remove those gridlines from sheet. 

To remove gridlines from sheets, select Tools > Options > View under Windows options, uncheck Gridlines. Now the gridlines will be removed.
Hide and Unhide columns in Excel

Open Excel > Select the entire cloumn that you wish to hide > Right click > Select Hide > To unhide column that has been hidden > Select the column to the right and column to the left of the hidden column > Right click and select Unhide.
Adding a Cell to Watch Window in Excel 2007. 

Open Excel 2003 > Select the cells you want to watch > Go to View tab > Toolbars > Select Watch Window > Click Add Watch > Click Add. You will see watch window appear and it will remain on top as you work or it may be dragged and dropped to its new location.
Locate last cell on a worksheet. 

Open worksheet and then press CTRL + END.
Want the column headings be displayed always in Excel irrespective of lot of scrolling. 

Open Excel sheet > Select entire row directly below your column headings > Go to Windows menu > Click Freeze panes option, now even if u are at the end of excel sheet column headings will be displayed always.
Paste data into non-adjacent cells. 

Select the data you want to copy. Hold Ctrl key and select destination cells. When all destination cells are selected, press Enter to copy the data.
Want to copy the formula. 

To copy the same formula in more than one cell, select the cells, type the formula and press Ctrl + Enter.
Form a simple formula in Excel 2003

Open Excel 2003 > Select the cell where you want to display the result of the formula, type equal sign (=) in the cell > Click the first cell that you want to use in the formula > Type one of the operator based on which mathematical operation you want to perform (+,-,*,/) > Now click second cell you want to use in the formula > Press enter.
Comments