Excel 2010

Disable conversion of two numbers with dash as date in Excel

March 10, 2023 by Admin 3 Comments

Microsoft Excel has a feature that automatically coverts two numbers separated by dash in to a date formatted value. For example if you type 6-7 in a cell, Excel would reformat this in to a date value as 7-Jun. But if you do not want to reformat then you can use any of the below mentioned solutions to disable this feature.

Solution 1: Prefixing the values with apostrophe

The simplest solution is to add apostrophe before 6-7. This would display the typed value without reformatting it.

Solution 2: Change format to Text Format

Another alternate solution is to change the formatting of the cell to Text format. Select the cell (or columns), right click and pick Format Cells from the menu list.

In the Format Cells window, select Text Format under Number tab and click OK button to apply the changes.

Also See: How to add country code to phone numbers in Excel

How to increase number of worksheets Excel

March 18, 2016 by Admin 1 Comment

Microsoft Excel by default contains 3 worksheets while creating a new Workbook. For creating a new workbook you can specify the number of Worksheet that a Workbook needs to contain. This can be modified using Excel Options. Listed below are steps for increasing number of Worksheets for a new Workbook in Excel 2013, Excel 2010 and Excel 2007

In Excel 2013 and Excel 2010 –> Click on File menu and then Options.

In Excel 2007 –> Click on Excel Options and then Popular menu.

Under When creating new workbooks section there is include this many sheets fields. This can be used for increasing the number worksheets while creating a new workbook. The minimum value is 1 and the maximum is 255. Check out the below video demo to increase worksheets size in Excel 2013

Also See: How to move or copy worksheets in Excel 2013

A trick to flip a column upside down in Excel

February 17, 2016 by Admin Leave a Comment

Excel users can sort a column using the Sort feature available as part of Sort & Filter menu. But what if you want to flip a column upside down. Let us see this with an example column having some text values as shown below.

We will not be able to apply sort feature directly on this column to flip the data instead we can add a dummy column and use that for sorting the text values.

Step 1: Insert a new column before the existing column.
Step 2: Now use Auto Fill feature to fill the temp with numbers as shown below

Step 3: Now you can use the Sort feature on column1 and make sure to sort by descending order. Also include the column2 in Sort sleection list.

Step 4: Once the columns are sorted, delete the temp column with numbers.

Prevent users from adding new worksheet in Excel

January 25, 2016 by Admin 2 Comments

Excel 2016, 2013 and 2010 provides an option to prevent users from adding new worksheet to existing Workbook structure. So if you are the owner of Excel file and you do not want to allow other users to add any new worksheet, rename or move worksheet then you can use Protect Workbook Structure option to change the permission. You can access Protect Workbook Structure from the Info menu.

Click the File menu and then navigate to Info menu and click the drop down arrow under Protect Workbook option. From the list of available option select Protect Workbook Structure. This would display the following Protect Structure and Windows. Mark the check box with label as Structure. If you want to password protect it then enter a password in the Password field. Click Ok button to save and confirm the changes.

How to disable auto fill feature in Excel

November 9, 2015 by Admin 15 Comments

Auto fill in excel is quite handy feature, using this you can fill series of cells based on the entries of other excel cells. But if you want to turn off or disable auto fill feature then you can use Excel options for doing this. Listed below are the steps to turn off auto fill in Excel 2013, Excel 2010 and Excel 2007

Excel 2016, 2013 and 2010

Click on the File menu –> Options link and navigate to Advanced tab on the left hand side.

Under Editing options, un tick the check box with label as Enable fill handle and cell drag-and-drop. This will turn off the auto fill feature in Excel 2010.

Excel 2007

In Excel 2007, this feature can be turned on or off using the Excel Advanced Options.

Click on the Office button –> Excel Options

Under Excel Options select Advanced section

By checking or un checking “Enable fill handle and cell drag-and-drop”, you can turn on or off the Auto fill feature in Excel 2007.

Also See: How to disable autocomplete suggestion feature in Excel 2010

How to change default ruler units in Excel

October 19, 2015 by Admin 1 Comment

The default ruler units in Excel 2016, 2013 and Excel 2010 is set to Centimetres and users can change this default units using the Settings available as part of the Excel options.

View Ruler in Excel 2013

The Page Layout view will be display the ruler in Excel. And in case if the ruler is not displayed then make sure to mark the checkbox with label as Ruler.

Change ruler units in Excel

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

Step 2: In the Excel options window, click Advanced tab and navigate to Display section.

Step 3: In the Display section, click the Ruler units drop down and select your preferred unit.

Step 4: Click Ok button to apply and save the changes.