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:
So without further ado, let’s learn how to use watermarks in Office applications.
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.
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.
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.
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:
The method for un-protecting each is described below.
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:
Read further for the procedure – which applies with minor variations to Microsoft Office versions starting 2010.
This technique is specially useful when interacting with data which is delimited by commas (CSV files) , semi colons, tabs, spaces and so forth.
Thanks for reading
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:
In same cases, however, you might want to lock more than one row or column. This is best shown by using the example below.
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.
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:
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:
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.
For completeness, in this section we’ll go ahead and review how the spell checking correction options work on Microsoft PowerPoint and Word.
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.
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
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:
More tips :
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.
Here’s how to vertically split cells in Excel (we have also a post covering dividing text into columns on Excel 2016 and 2019):
Once you are done, click on OK and now you get the cells split as you specified.
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:
Once you have enabled the Developer tab, you can go a head and create your custom form very easily.
Dave, who read my post on how to open a pptx file approached me with a similar question.
Few days ago, a friend sent me a file with the xlsx suffix as an attachment on GMail. I access my email online via my Samsung Galaxy phone, iPAD and MAC OS X High Sierra computer. I believe that that’s file authored with Microsoft Excel 2013. My question is which program i need to use in order to access that file from my mobile devices and from my MAC OS X.
Thanks for the question Dave. Before we start, let’s define what does xlsx stand for. Starting Office 2007, Microsoft introduced new file types,that used the XML format. This specific file suffix is used for Microsoft Excel 2007, 2010, 2013, 2016 and 2019 files. Similar suffixes were introduced for Word, Powerpoint and Visio files.
Now to your specific question:
I believe that the easiest way to access xlsx spreadsheets for editing and viewing from your computer is via the Microsoft Office Online suite. In your case just go ahead and register a Microsoft Onedrive account, then save the xlsx workbooks to OneDrive and open those using Microsoft Office online.
If someone sends you such a spreadsheet and you don’t run Excel 2007 or older but you do have previous versions of Excel installed in your PC, your best shot is to head towards the Microsoft web site and download the Microsoft Compatibility pack. This neat software package will allow you to open and edit your new Excel file. Follow a similar process for opening .docx, .pptx and .vsdx files.
Other option you might have here is to ask your friend to save the xlsx document as xls (Office 2003 format). This is easily achievable using the Save As dialog in Excel. Note that formatting of the spreadsheet is likely to change as you are effectively “downgrading” the file.
If you are using Google Docs/Sheets, just go ahead and import/upload the xlsx file to Drive, from which you can open it for editing in Google Sheets (which will automatically take care of the conversion) or as a View only document, which will retain the Microsoft Excel formatting.
On Android phones (Samsung, LG, Huawei, Xiomi and many others), you could use Office Online or navigate to the Android Play Sore application and download the Microsoft Excel application or other office packages, like Polaris for example.
Two options here as well. Use Office Online, available from any browser (Chrome, Firefox, Safari, Opera and others) or download the Excel application or similar office viewer from the Apple Store.
In case of any issues, kindly leave us a comment.