Excel 2016

Prevent users from adding new worksheet in Excel

January 25, 2016 By Ravi Shankar 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 import text file in Excel

November 17, 2015 By Ravi Shankar Leave a Comment

This tutorial is provided step by step instruction to import a text file in to Worksheet in Excel 2016 and 2013. For this demo, let us take the following sample data with the columns separated from tabs and spaces.

Step 1: Launch Excel Workbook and click the Data menu. Then navigate to Get External Data section.

Step 2: Click the From Text option under Get External Data section.

Step 3: In the Import Text File window, select Text file and click Import button. This should display the following Text Import Wizard.

Step 4: The columns in the text file have been separated using the tabs. Hence we have selected Delimited option for “Choose the file type that best describes your data”.

Step 5: After selecting an appropriate value for Start import at row and File Origin, click the Next button.

Step 6: In Step 2 of Text Import Wizard, choose appropriate delimiter under Delimiters section. You can preview the selection using Data preview section.

I have selected “Treat consecutive delimiters as one” as the text file used for import has more than one consecutive delimiters. Now you can see the Data preview section display the of data as 2 columns.

Step 7: Click the Next button to access the data format screen in Text import Wizard.

Here you can set the data format for the columns to either General, Text or Date. Similarly if you do not want to import any column then highlight it under Data preview select and select Do not import.

Step 8: Click Finish button on Text Import Wizard for completion.

Step 9: Now you will be show the above dialog box to choose “Where do you want to put the data”. You can either specify range on existing worksheet or New worksheet.

Step 10: After selecting the appropriate option under Import Data screen, click OK button to insert data from the text file.

How to disable auto fill feature in Excel

November 9, 2015 By Ravi Shankar Leave a Comment

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 Ravi Shankar Leave a 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.

How to hide row and column headers in Excel

October 19, 2015 By Ravi Shankar Leave a Comment

Excel Worksheet by default displays the rows and columns header. For some reason if you want to hide these headers then you can use Excel Advanced Options. Listed below the steps to hide or unhide row and column headers in Excel 2016, Excel 2010 and Excel 2010.

Step 1: Click the File menu and then options link.

Step 2: In The Excel Options windows, select Advanced tab and scroll down to Display options for this worksheet section.

Step 3: To hide the rows and columns headers in a Excel 2010 worksheet, un mark the checkbox labelled as Show row and column headers.

Click OK button available at the bottom of the screen to confirm the changes.

Also See: How to hide header/footer while typing in Excel

How to check version of Excel

October 17, 2015 By Ravi Shankar Leave a Comment

In Excel 2016, 2013 and 2010, the version of excel can be checked using the Help or Account menu.

Click the File menu and select Help menu.

Navigate to the About Microsoft Excel section displayed on the right hand side. This would display the Excel Version details, as shown below.

Screenshot in Excel 2013

And if you want to see additional information then you can click on the Additional Version and Copyright Information. This would display the following About Microsoft Excel Window

Also See: Disable automatic conversion of hyperlink in Excel

How to enable auto filter for protected sheet in Excel

October 10, 2015 By Ravi Shankar 11 Comments

We had already seen the following tutorials related with protection of worksheet in Excel 2016, 2013 & 2010.

Prevent users from adding new worksheet.
Password Protect Worksheet in Excel.
Unprotect Worksheet in Excel.

And if a worksheet with auto filter is protected then users will not be able to use auto filter unless it is enabled while protecting the worksheet. This tutorial is about the steps required for enabling auto filter for a protected worksheet in Excel 2010.

Click the Protect Sheet option available under Review menu or Info menu.

Review Menu

Click Review menu –> Changes –> Protect Sheet

Info Menu

Click File menu –> Info menu –> Protect Workbook –> Protect Current Sheet.

On clicking the Protect Sheet option would display the following Protect Sheet window.

Scroll down the “Allow all users of this worksheet to” list and make sure to tick the check box with caption as “Use AutoFilter”. Click OK button to confirm and save the changes.

Now excel users will be able to use the auto filter option even in a protected worksheet.