Excel Tips and Tricks

How to Add calculator in Excel 2013 & 2010

by 1 Comment

Excel users can customize the Quick Access Toolbar and add Calculator as part of the toolbar. This would allow users to access Calculator without leaving Excel application. Let us now see the steps for adding Calculator as part of QAT

Adding Calculator to Quick Access Toolbar in Excel 2013 & Excel 2010

Step 1: Launch Excel and click Customize Quick Access Toolbar dropdown.

Customize Quick Access Toolbar in Excel 2013

Step 2: Select More Commands from the drop down list.

Commands Not in the Ribbon

Step 3:  In the Excel Options window, select Commands Not in the Ribbon for the “Choose commands from” dropdown.

Caculator to QAT

Step 4: Scroll down the commands list and select Calculate Full or Calculator.

Add Calculator to QAT

Then Click Add button to add the command as part of the Customize Quick Access Toolbar list. Click OK button on Excel Options screen to close the Window.

Now you should see Calculator as part of QAT as shown in the below screenshot. And you can quickly access Calculator without leaving Excel 2013.

Calculator in Excel 2013

 

 

 

How to recover a workbook in Excel 2013 and Excel 2010

by 1 Comment

recycle files folder

This tutorial explains the auto recover feature available in Excel 2013 and Excel 2010. The auto recover feature regularly takes a backup of the workbook and stores it in a pre defined location. This feature is useful if Excel suddenly crashes and you want to recover the working copy.

Document Recovery

A document recovery pane will be displayed on the left hand side of the Excel worksheet when excel is opened after a crash.This would contain the workbook(s) which were kept open during the crash.

image

If you want to recover any of the displayed workbook, then select the required file from the available files list.

Change AutoRecover file location

You can specify the path for auto recovery feature using Save workbooks Options. Click the File menu then Options link and then navigate to the Save tab.

image

Again navigate to the Save workbooks section in Save Options screen. Using AutoRecover file location you can specify the path for automatic backup.

Change AutoRecover Time interval

Using Save AutoRecover information spinner field you can increase or decrease the minutes at which the auto save needs to happen.

Disable AuoRecovery

We have seen the importance of AutoRecovery feature in Excel and by default this is enabled. But Excel also provides option for disabling this feature.

image

You can disable this by navigating to AutoRecover exceptions section then un tick the checkbox with label as Disable AutoRecover for this workbook only.

Any changes made to this would get reflected after clicking on the OK button available at the bottom of the Save options screen.

Change the default file location in Excel 2013 and Excel 2010

by 1 Comment

DefaultFileLocation

We have already seen the following articles on Microsoft Word 2010 and Microsoft PowerPoint 2010.

In this tutorial we are going to see how to change the default file location in Microsoft Excel 2013 and Excel 2010.

Click the File menu on Microsoft Excel and then the options link.

Change file location excel 2010

In the Option window, navigate to Save workbooks section under Save option. Windows Vista and Windows 7 operating system will have the default file location set to their users document folder. Now change this to your preferred working folder by making the necessary changes to Default file location property. After making the change, click Ok button to confirm and save the changes.

Change undo limit in Excel 2013 and Excel 2010

by 3 Comments

Undo

As per the Excel Specifications and limits the maximum limit for undo in Excel 2010 is set to 100. In the previous version of Excel this was set to 16. The maximum limit value is fixed but you can increase or decrease this limit by modify registry settings.

Please Note:- Changing Registry Settings is not recommended and do this with at most caution while trying these changes.

To change the undo maximum limit, Open Windows Registry Editor and then navigate to the following path

HKEY_CURRENT_USER –> Software –> Microsoft –> Office –> 14.0->Excel->Options

Create a new DWORD value with the key name as UndoHistory

image

Then modify UndoHistory by setting the Base as Decimal and value as 16. Click the OK button to save the changes.

image

This would decrease the number of undo same as the previous of Excel. You can also increase the undo limit from 100 to 200 for Excel 2010 by changing the above created Windows Registry key.

Changing column width in excel 2013 and excel 2010

by 1 Comment

There are different ways for changing the column width of cell in Excel 2010 and Excel 2013. Some of them are listed below

Double Clicking Column Header

The easiest one for adjusting the column width is to double click the Column Header of cell whose width needs to be changed depending on the longest length of text entered in the Cell. The mouse pointer needs to placed on the border line before doing a double click

Using Mouse Drag and Drop

Mouse Drag and Drop Column Width

Another way for changing the column width is to use mouse drag and drop feature and adjust the cell column width depending upon the requirement.

Using Format menu option

Excel also provided option for changing the column width by using the Format menu option. In the home menu, navigate to Cells section and click the drop down arrow below the Format menu option.

image 

This would display the following list of available menus.

image

To Change the column width of the selected cell, select Column Width from the available menus and enter the desired column width for the cell in Column width text box.

image

And if you want to change the width for all the columns in the Worksheet then select Default Width menu from Format menu option and specify the Standard Width in Standard column width text box.

image

How to enable a disabled add-in in Excel 2010

by 9 Comments

Office products such as Word, Excel Outlook will automatically disable installed add-in if any add-in prevented the software from functioning correctly. And it also provides option for enabling the disabled add-in items. In Excel 2010 you can enable a disabled add-in using the add-in options.

Click the File menu –> Options link –> Navigate to Add-Ins options.

Excel 2010 Add-ins

Select Disabled Items in the Manage dropdown and click the Go button. This would display the following Disabled Items window using which you can see and also re-enable the disabled add-in. You can select the ones you wish to re-enable and click the Enable button.

image

How to change the decimal separator in Microsoft Excel

by 15 Comments

Microsoft Excel 2013 and 2010 by default uses the system defined decimal separator. In Windows 8 and Windows 7 this option is defined under the Regional and Language Settings available under Control Panel.

Step 1: Right click on the bottom end corner of Window 8 system and select Control Panel from the menu list (In Windows 7, navigate to Control Panel).

Windows 8 Control Panel

Step 2: In Adjust your computer’s settings screen, click Clock, Language and Region option.

Windows 8, Clock Language and Region

Step 3: In Clock, Language and Region screen, click Change date, time or number formats under Region section.

Change date, time or number formats on Windows 8

Step 4: Click the Additional settings button in the Region window.

Additional Settings on Windows 8

Step 5: In Customise Format window, enter the new separator for Decimal Symbol. Then click Apply and Ok button to save the changes.

Customize Decimal Symbol in Windows

Microsoft Excel also provides users can customize the separator used for Decimals and Thousands using Excel Options. Listed below are the steps to change decimal separator in Excel 2013 and Excel 2010

Step 1: Click File menu and select Options from the menu list.

Step 2: In the Options screen, click the Advanced tab and navigate to Editing section.

Decimal Separator Excel 2013 and Excel 2010

Step 3: Under Editing section, un mark the check box with caption as “User system separators”.

Step 4: Now enter your new decimal separator under Decimal separator field. For this demo we are colon (:) as the decimal separator.

Step 5: Click OK to confirm and save the changes.

Use System Separators - Excel 2013 and Excel 2010 

Also See: How to change decimal separator on Mac OS X Lion

How to use Goal Seek in Excel

by Leave a Comment

Microsoft Excel has a Goal Seek feature which can be used to derive value of a cell based on the result set for the cell containing a formula. Let us see this with an example that does a simple addition. In the below screenshot, you should notice that the sum of A3 to E3 is calculated in F3.

Simple Addition Formula in Excel

Now let us say that you want the final result in F3 as “350000” and this should be achieved by changing the value of C3. For this scenario we can use Goal Seek feature to find out the value to be entered in C3.

In Excel 2011 for Mac, Goal Seek can be accessed from Tools menu.

Goal Seek in Excel 2011 for Mac

Enter the Set cell to the cell containing formula i.e F3

Goal Seek Screen in Excel

Then enter the final result that you need to arrive in “To value” field i.e 350000

Goal Seek To Value

Click Ok button after entering the cell that needs to be changed to get final result. The following Goal Seek Status screen will be displayed with “found a solution” message.

Goal Seek Status screen in Excel

Though we have taken a simple problem do explain Goal Seek feature. This feature will be quite handy when you are working with complex formulas and want to try out different values.