How to add a drop-down menu for data validation in Excel 2016?

Capturing user input using drop down menus in your Excel spreadsheet can be leveraged as a very powerful data validation technique. In today’s tutorial we’ll take you through the process of creating the drop down menu, validating user input and showing an error message if a invalid data is entered. Read further for the procedure.

To create a drop down menu in your Excel worksheet:

• Open your Excel spreadsheet, then select the cell where drop down menu has to be inserted.
• Now, click on Data in the ribbon and select Data validation in the data tools menu.

• Click on the Allow drop down menu and select List.

• In the source, enter the values which has to be displayed in the cell drop down menu.

• We can also enter the values for the source by click on the icon right to source and select the cells with different data’s.

• Click OK to insert a drop down menu.

Data Validation:

• To insert a input message when the drop down menu cell is clicked, click on Input Messages in the data validation dialog box.
• Make sure the “Show input message …” box is checked on.
• Then, enter your user dialog title and the information in the space provided and select OK.

Displaying Error Messages:

• To show a error message when an invalid data is entered in the drop down menu cell, click on Error Message in the data validation dialog box.
• Make sure the “Show error alert …” box is checked on.
• Now enter the title and the error message respectively to be displayed if an invalid data has been entered.

• Also we can insert the style symbol with the error message like Stop, warning, information symbols.

You are all set.  Thanks for reading

How to embed watermarks in Office 2016 / 2019 Excel spreadsheets and PowerPoint presentations?

Last updated: September 2018

Applicability: Office 2019, 2016,2013,2010; Windows and MAC OSX

Few weeks ago we received a note from a reader:

Every quarter, our team creates handouts for management meetings. The process of developing the handouts is pretty elaborated and requires to collaborate in development a series of workbooks, presentations and documents. In order to clearly determine the status of those documents we wanted to use watermarks. Can you shed light about how to use watermarks, either textual (plain or smart texts) or  graphical (pictures from our computers and the web).

Thanks for the question. In this post you will learn how to insert or embed watermarks in Office 2016 applications like PowerPoint and Excel (you could potentially use similar techniques in Word and Visio). Note that you can use the same procedure in order to either add or remove a watermark.

There are several use cases that can be accomplished by inserting watermarks into Office files:

1. Indicating the document status (“Draft”,”Released”, etc’)
2. Specifying the confidentiality level of a document
3. Indicating the copyrights of the file content
4. Indicating that the content being published is a sample

So without further ado, let’s learn how to use watermarks in Office applications.

Insert watermarks into PowerPoint slides

• Open PowerPoint 2016,
• Navigate to your presentation.
• Go to View and select Slide Master.

Picture/Gif/Jpg Watermarks

• To add a picture watermark into your slide, click Insert and select Pictures.

• Browse to anyone of your desired pictures and click Insert.

• Now, in the ribbon select the Send backward drop down box and hit Send to back.

• Then right click on the picture and select Format picture.

• Select Picture in the box, click on Recolor and select a lightened picture.

• Go to Slide master and hit Close master view.

Text Watermarks

• To insert a text watermark in your slide, click insert and select lightened WordArt.

• Enter the text that want to be your watermark.

• Now, right click on the text and select Format text effects.

• Then select text fill & outline and increase the transparency accordingly in the Text fill.

Insert watermarks to Excel worksheets

• The process of creating Excel watermarks is quite similar to the one we highlighted for PowerPoint below.
• To insert a text watermark in your slide, click Insert and select WordArt.
• Next, type the text that want to be your watermark (that would be “draft”, “in-process”, “copyright” and so forth).
• Next, right click on the text and select Format text effects.
• In the Format Shape dialog, pick the Text Options tab
• Then under text fill & outline and increase the transparency accordingly using the Text fill slider, as shown below (screenshot taken from Excel 2019 on MAC – looks similar in Windows).

Note:

• In Excel a picture watermark cannot be inserted, as the values are entered in the cell the picture transparency cannot bring the cells front to make it visible.
• When adding text watermarks, you are able to set not only the transparency level, but also use the Text Format pane to determine the watermark color gradient, size, format, rotation and so forth. See below a different version of our Draft watermark, this time rotated, shadowed and different WordArt style.

Suggested next steps

• Got additional Office related questions? If so, then i would suggest to take a look into our guide on how to get your questions answered.

How to embed a Word document into an Excel 2016 / 365 / 2019 workbook?

Last update: April 2019

Applies to: Excel 2019, 2016, 2013; Office 365; Windows 10, 7 and macOS.

Dorothy wanted to understand how to insert objects into Excel spreadsheets:

I believe i have seen a Microsoft Excel worksheet that had a Word document embedded in it. Can you explain how can i insert Word files into Excel? I am using Excel 365.

Thanks for the question. One of the key benefits of an integrated productivity suite, such as Office, is the ability to insert file objects , in this case Word document files, into other Microsoft Office applications, mainly Excel and PowerPoint.

So without further ado, here’s a quick tutorial that is aimed at explaining how you can embed an object (being a document, presentation, diagram, notebook)  into Excel. We’ll demonstrate the process by adding a Word file into a workbook.

Inserting Word documents into Excel spreadsheets

1. First off, open Microsoft Excel.
2. Then hit File, then navigate to the Open tab, search and open for your Workbook. (Tip – consider pinning files for easier access in the future).
3. Navigate to the relevant worksheet in your Excel file.
4. From the Ribbon, hit Insert.
5. In the right hand side of the Ribbon, hit Object (located in the Text group of the Insert tab).
6. At this point, you can either add a new Word file to your worksheet or an existing one. Select Create a new file and pick Microsoft Word as the object type from the drop down list to add a brand new document or select Create from file to add an existing file  to the spreadsheet.
7. Now, go ahead and adjust the look and feel of your embedded object so it will fit your spreadsheet layout.
8. Next, hit OK.
9. And obviously, don’t forget to save your Excel spreadsheet on your computer or OneDrive.

Adding Word attachments into Excel

In a similar fashion you are able to insert your Word doc as an attachment to the worksheet.

Follow steps 1-6 above, but be sure of highlighting the Display as Icon and Link to File check-boxes before moving to step 8. Your document will displayed as an icon on your spreadsheet, which you can double click to open it.

Linking to a file from Word and Excel

As shown above, by using the Link to File feature, you can easily link to any embedded file or icon in your spreadsheet or document.

Notes:

1. As shown above, embedded files can also be displayed as links or icons in your spreadsheet.
2. The process we just outlined applies for adding any type of files (including if needed, image, graphs, equation objects and so forth) into an Excel spreadsheet.

Embedding Word docs into Excel on macOS

1. Open Excel for macOS.
2. Navigate and open your spreadsheet.
3. Go to the Insert tab.
4. Now, go ahead and hit Select Object.
5. The Insert Object form will appear:

5. Select Microsoft Word document to insert a brand new file, or hit the From file button to add an existing doc to your worksheet.
6. Last hit OK, and don’t forget to save your file.
   

Note:

1. If you are using Microsoft Office on MAC, you’ll be able to embed Word docs, but not PowerPoint presentations nor Visio diagrams.

How to remove password protection from Excel 2016 / 2019 workbooks?

Updated: August 2018

Applicable: Excel 2019/365/2016; Windows operating systems.

Here’s a question we got:

Dear Team – some time ago i have created a quite sophisticated Excel spreadsheet for my manager which i password protected for increased security. The thing is that it seems that i have forgotten the password which i previously set. So i guess my questions is whether i can reset the workbook password protection since i have forgot it? Many Thanks!

As we just saw, in Microsoft Office we are able to set a password protection for our Excel files. In this tutorial , we’ll learn how to reset those spreadsheet protection password so you can edit the file contents. Although we are focusing on Excel in this post, the explanation below is mostly applicable to Word and PowerPoint files as well.

In Excel 2016, there are three main ways to secure contents in a spreadsheet:

1. We have the option to encrypt the entire file: so that a password is required to open the spreadsheet.
2. Protect the workbook structure and contents: here you can either protect the workbook from editing changes such as inserting or deleting sheets, rows and columns, formatting changes, ability to lock cells, etc’
3. Protect the worksheet structure and contents: same as above, however applicable at the sheet level.

The method for un-protecting each is described below.

Remove  password from an Excel file

• Open the password protected document.

• Hit File and select the Protect workbook.
• In the drop down box, select Encrypt with Password.

• Delete the letters in the password and click OK.

• Now, your document password is removed.

Un-protect Excel Workbook

• Click on the selected protect Workbook under Review.
• Enter the respective password in the Unprotect Workbook dialog.

To Un-protect specific Excel Sheets

• Click on the Unprotect Sheet in the Review.
• Enter the appropriate password to Unprotect sheet.

Suggested next steps

• Got any follow up questions? If so, look at our guide on how to get your Excel questions answered.

How can i divide or split text between cells in Excel 2016 and 2019?

Last updated: August 2018

Applicability: Excel 2019, 2016, 2013; Office 365 and standard; Windows 7, 10 and  MAC OS X.

When analyzing significant amounts of raw data in Microsoft Excel, you might find yourself in need of dividing or splitting text between different column cells. In today’s post we’ll cover three useful techniques that you will be able to use:

• Text to Columns – Delimited
• Text to Columns with Fixed Width
• Un-merging cells

Read further for the procedure – which applies with minor variations to Microsoft Office versions starting 2010.

Dividing to columns using Text to Columns – Delimited:

This technique is specially useful when interacting with data which is delimited by  commas (CSV files) , semi colons, tabs, spaces and so forth.

• Open your Excel and open the database in which the Columns has to be separated.
• If you have Data’s in the next cell, select the cell and right click on it.

• Click on Insert and select Shift Cells Right.

• Now, select the whole column which has to be separated.
• Click on Data in the ribbon and Select Text to Columns.

• Select Delimited and click Next.

• Click on any one of the option in the Delimiters and select Next.
• Say for Example I have selected Space for separation.

• In the Column Data format Select your option if needed. It will be General as Default.
• To select in which cell the separation has to be done, Click on Destination and select the cells.

• After you have done with the selection, click Finish.

Dividing to Columns using Fixed width

• Open your spreadsheet and select the column cell which has to be divided.
• Again make sure no data is available next to the dividing cell.
• In your Excel header menu, click on Data and select Text to Columns.
• Select Fixed width and Click on Next.

• For creating a break line, click on desired position
• For deleting, double click on the break line.
• For moving, click and drag the break line.

• In the Column Data format Select your option if needed. It will be General as Default.
• To select in which cell the separation has to be done, Click on Destination and select the cells.
• Click Finish after selecting your format.

Un-merging cells

• To split merged cells, click on Merge & center and select Unmerge cells.

Thanks for reading

How to freeze rows and colums headings on Microsoft Excel 2016?

From time to time i receive or develop Excel spreadsheets which contains huge data tables. One of the challenges in this case is how to ensure that the first rows and columns stay visible when you scroll through the table, so it’s easier to correlate the data itself with the row/column heading.

In order to overcome this challenge, i use the Freeze Panes command. Here’s how it goes:

• Open the relevant Excel worksheet.
• From the Excel 2016 ribbon, hit View.
• Hit Freeze Panes.
• From this menu you can lock your first column or top row pretty easily

In same cases, however, you might want to lock more than one row or column. This is best shown by using the example below.

• If i highlight cell C3, and hit Freeze Panes. Rows 1and2 and will be locked, as well as columns a and b.

Note:

• You can always unlock the frozen columns and rows by selecting Unfreeze Panes.

How to auto spell check in Excel 2019 / 365, 2016 and 2013?

Last updated: February 2019

Applicability: Office 2019, 2016, 2013; Office 365 and Standard. Windows and MAC OSX operating systems.

Here’s a question that we received a few days ago:

Using Excel 2016 on Windows 10. As i am quite a fast writer i accidentally make quite a bit of spelling mistakes. For some reason i would expect that Excel would spell check as i type into the cell. If i recall correctly, Word does by default auto corrections in documents, so i would expect that Excel will behave similarly or i am wrong here? To me it seems that the Excel 2016 speller doesn’t work… How to spell check in my Excel spreadsheet?

Thanks for your question. It’s a good one. Spelling check is indeed one the key capabilities of the Office suite. As far as Text proofing, there is a fundamental difference between Excel and Word (or PowerPoint and OneNote). Turns out that Excel doesn’t have the ability to auto correct your spelling and grammar as you type text in the spreadsheet unless defined in the AutoCorrect Options (see below how to do it); whereas Word and PowerPoint do auto correct as you type. Let’s get started with Excel.

Spell Checking in Excel

The spell checker in Excel is located in the Review tab.

In order to proof check your worksheet text, go ahead and proceed as following:

1. Open your workbook and navigate the worksheet/
2. Inthe Ribbon, open the Review tab.
3. Hit on Spelling.
4. Your worksheet will be analyzed for errors, and Excel will provide suggested corrections in the Spelling dialog.
5. In case of false-positives, you could Add to Dictionary relevant words or terms.
6. Alternatively, hitting the F7 button will achieve the same results.
7. Once done, hit on OK.
8. Save your work as needed.

Enable AutoCorrect in Excel

The Spelling dialog which we just reviewed in the section above also allows you to trigger an AutoCorrect action. AutoCorrect automatically amends your content according to the rules you have defined under Options>>Proofing.

Some of the auto-correction rules you can define:

• Automatic capitalization of first letters in a sentence.
• Capitalization of Days
• Correction of Caps Lock accidentally pressed.
• And any automatic text replacement you might have

Note: Remember,  the AutoCorrect capability spell check is not triggered automatically in Excel, you have to invoke it on your own. That’s the reason that Excel can’t spell check as you type your text.

Spell Checking in Word and PowerPoint

For completeness, in this section we’ll go ahead and review how the spell checking correction options work on Microsoft PowerPoint and Word.

Manual proof checks

1. Open the Word or PowerPoint file that you want to proof check.
2. You can always find the Spelling & Grammar option under Review tab in the Proofing group.

3. This will check the entire document for spelling errors.
4. Alternatively, you can do this by just pressing F7 key, which ‘ll do the same job.

Turning on Auto spell checks

As mentioned above, Word automatically checks your content and grammar as you type those. If for some reason, this capability is not enabled in your system, proceed as following.

1. Click on Options under File menu.
2. In the pop up window that opens, select Proofing.
3. Under When correcting spelling and grammar in Word/Excel, check your desired options and click on OK.

Disabling Auto proofing

If for some reason, you would like to turn off ongoing auto spelling and grammar corrections as you type in your document, just go ahead and uncheck the first two boxes in the section highlighted in the section above.

In case of any questions, feel free to leave as a comment

How to secure Excel worksheets and Powerpoint presentations in Office 2013 and 2016?

On the other day, one of my colleagues was asking me if there is a way to secure  Excel spreadsheets and Powerpoint presentations in Office 2013 so that others will not have access to those files. Here’s my answer:

• Open the Excel worksheet/Powerpoint Presentation that you would like to secure.
• Under File menu, select Info.
• In Excel 2013, you’ll find Protect Workbook drop down.Click on it and select Encrypt as password.

•  In Powerpoint 2013, you can find this option under Protect Presentation drop down.

• In the pop-up window that opens, type in your desired password and click on OK.

More tips :

• In Excel 2013, you can control what type of changes people can make by selecting Protect Current Sheet in the Protect Workbook drop down.
• This option is also available under the ribbon Review -> Protect Sheet.

How to split worksheet cells in Excel 2013 / 2016 / 2019?

Last update: February 2019

Here’s a questions that we have received from a reader that wanted to divide a column vertically:

I am working on a statistical report in Microsoft Excel, using version 365. I just imported a comma separated value file  into the A column and  nowneed to split a column cell vertically into two “halfs”. Any simple way to get that accomplished? If it helps i am using Windows 10.

Thanks for the good question. For these scenarios i typically use the Text to Column feature, which serves my purpose pretty well. See below the full details.

Splitting cells in Excel

Here’s how to vertically split cells in Excel (we have also a post covering dividing text into columns on Excel 2016 and 2019):

• Open Excel and navigate to your worksheet
• Highlight the cell(s) that you would like to split in halfs.
• Now under Data tab, click on Text to Columns, that opens a dialog box.
• In the dialog box, choose the best option to split your text. You have two options

• Delimited – Splits text with a specified delimiter like comma, space, tab etc.
• Fixed Width – Splits cells with a fixed value that is set.
• If you select Delimited, you need to specify the delimiter in the next page. With the latter, you can drag the pointer to a particular width under Data Preview and click on Next.

Once you are done, click on OK and now you get the cells split as you specified.

More tips:

• You can specify the type/format of the new column under Column data format.
• Also, you can modify the destination of new column(which is the next cell by default) under Destination.

How to insert a form in Microsoft Excel 2013?

Sometimes you would like obtain user feedback and use it as input in your Excel worksheet logic. Using form controls such as buttons, radio buttons, labels, check boxes, combo boxes, text-field labels etc’ allows you to capture information in an easy and intuitive way.

First off, you’ll need to enable the Excel 2013 Developer tab:

• Select File menu and click on Options.
• In the dialog box that opens, select Customize Ribbon.

• Check Developer under the Main tab and click on OK.

Once you have enabled the Developer tab, you can go a head and create your custom form very easily.

• Under the Developer tab, you will find an Insert option with which you can insert form controls.

• Click on a form control and drop into your worksheet and you can build a form in Excel.
• Then author your data entry form and assign it buttons, check boxes and other controls as required.
• Your last action will be to either write custom macros or VBA code to automate form action as needed.