Flash Fill is a tool available in Excel 2016 Basics that is similar to AutoFill and Text to columns.
In contrast to AutoFill,Flash Fill is not restricted to a single row or column, but it also looks at the surrounding cells to find more complicated patterns.
It differs from the Text to columns tool in that, it can extract numbers that are separated by different delimiters and can also be located in different positions inside the cell.
You can find Flash Fill in three different places:
HOME >> Editing >> Fill >> Flash Fill,
DATA >> Data Tools >> Flash Fill,
Ctrl + E keyboard shortcut.
Let’s use the following example.
Suppose that you want to extract the number from each cell and insert it into the cell in the right column.
Not all of them are separated by space (e.g. $399) and they are located in different places. This is a perfect example to demonstrate how the Flash Fill works.
Enter number 5 in cell C2 and press Ctrl + Enter to stay inside the cell. Use Flash Fill to fill other cells.
Notice that the data inside cells C5, C7, C9, C11 doesn’t contain decimal fractions but integers. Now, change the value 141593 in cell C5 to value 3.141593.
Look that the Flash Fill tool „learns” from your corrections, so it is very important for the data to be consistent.
Excel 2016 Basics Spell checking
Spell checking is well known to those who work with text editors, such as Microsoft Word.
Because you use text not only in text editors, spell checking can also be useful in other programs. That’s why Microsoft added this tool to other Office applications.
To open the spell checker, select REVIEW >> Proofing >> Spelling or use the F7 key. Excel checks the spelling cell by cell, row by row. If it encounters value that seems to him to be incorrect, it stops there and displays a list of suggestions where you can decide what you want to do in the current situation.
If Excel doesn’t encounter any suspicious words it displays the message informing you that the spell checking is complete.
Excel 2016 Basics Undo and Redo
When you work with a worksheet, Excel records every change you make. For example changes in cells, cell and text formatting, cutting, copying, pasting, editing, etc.
Excel can successfully save up to 100 changes, without slowing down the computer.
You can control these changes by using two commands located in the Quick Access Toolbar: Undo and Redo.
If you want to go back to the particular state, click the triangle next to the Undo icon, then choose a position you want to go back to. The changes are described, so you can go back to the exact state, without guessing.
The Redo icon becomes active when you go back at least one position. With Redo as well as with Undo you can click the triangle (that is located in the same button) and select an item from the list.
Instead of clicking the arrows, you can also use the Undo (Ctrl + Z) and Redo (Ctrl + Y) keyboard shortcuts.
Some actions cannot be undone. One of them is removing a worksheet from the workbook.
Excel 2016 Basics Hyperlinks
A Hyperlink is a text, which references to a particular place. It is usually highlighted and underlined in a way that it can be distinguished visually from normal text. You often deal with hyperlinks when you view web pages. But you can also find them in Excel and they have a similar purpose to those found on web pages.
To insert a new hyperlink, go to INSERT >> Links >> Hyperlink. After you click the button, note that on the left side there are four different buttons, which you can use to insert hyperlinks.
Excel 2016 Basics : Existing file or web page
You can create the hyperlink that points to a website (which will open in a browser) or a file (which will be opened by the default program).
The text of the hyperlink will be the name of the file or the page address you entered. To change this, click the text box, which is located next to the Text to display.
Click the OK button to insert the hyperlink.
When you click the link, it will be opened by the default browser or default application, depending on whether you entered a website address or a link to a file.
By default, Excel converts text to link when you enter the address of a website directly into a cell or the formula bar (for example „http://google.com” or „www.google.com”). If you want to enter a web address as a text, use the Ctrl + Z keyboard shortcut, just after you confirm the entered value.
Excel 2016 Basics : Place in this document
The second button is the button called Place in This Document. It is used to insert a reference to cells which are located in the worksheet.
If you have multiple worksheets in the workbook and you want to place the hyperlink that points to a different worksheet, you can do it by selecting it from the list.
Excel 2016 Basics : Create a new document
At this point, Excel allows you to create a hyperlink to a document. The document is automatically created after you click the OK button.
Here, you can choose whether a new file should be opened for editing or saved as a blank document.
When you click this link you can send an e-mail. If your mail client is not configured then a message will inform you that you cannot perform this operation.
If you want to quickly insert the hyperlink, press Ctrl + K shortcut.
Excel 2016 Basics Data validation
There may be situations when you want to prevent users from entering data that doesn’t meet the conditions you specified.
Suppose that you have the cell in your worksheet where you want a user to enter his age. Therefore, as a condition, you require the integer between 18 and 99.
To set a condition, first, select the cell or the range of cells, then click DATA >> Data Tools >> Data Validation. After you do that, a new window will appear.
Here, you can specify which kind of data and range you want to set for the selected cells.
You can use one of the following data types:
Numbers that are not fractions (-12, 0, 4, etc.).
These numbers can also be fractions (1.2, -1, 5, 4.6, etc.).
With this type of validation, you can choose from the drop-down menu one of the several items.
If you choose List, the new textbox, called Source will appear. In this textbox, you can select cells from the worksheet or enter values from the keyboard- for example “one,two,three” (or “one;two;three” depending on your system configuration).
Here, you can enter date and time. (“3/14/2014”, “14-Mar”, “14”).
If you enter a date without a year- for example, “14 March”, Excel will treat this record as “13 March 2014” (current year). If you enter only a day, for example, “14”, it might seem that it is a day of the current month and current year. However, in this case, it will be regarded as “14 January 1900”. That’s because the text “March 14” is treated as a date and “14” as a number. You will learn more about dates in future lessons.
Here, you can enter an hour. For example: “12:00”, “1:14:50 p.m.”.
If you want to see which date and time formats you can use, right-click the selected cell and choose Format Cells…. After you click the position from the category on the left side, a list of different formatting options will appear.
Here, you can specify the number of characters that must be entered. You can enter plain text, as well as numbers.
There may be some confusion when you deal with dates. If you set the length of the text range for 3 to 5 characters, and then you enter the date “March 14, 2014” (14 characters), format it to “14-Mar-14” (9 characters) Excel won’t return an error. That’s because a date in Excel is stored as a number and is only formatted as text. If you want to see that number, right-click this cell and select Format Cells … from the contextual menu. Select Number and click OK. As you can see this number is “41712”. It consists of 5 characters, which are inside the specified range, so this number won’t return the error.
Custom validation is more complicated than other types of validation. Here, you can insert a formula with the specific parameters.
At the bottom of the window, you will find the checkbox called Apply this changes to all other cells with the same settings. It means that if you click the cell and select this option, Excel will check in a worksheet (not a workbook) whether there are other cells with the same validation parameters. If so, all these cells will be selected and the changes applied.
Here, you can set a pop-up window with a title and input message. Now, when you click the cell, a window with an entered message will appear.
Excel 2016 Basics : Error Alert
Excel displays an error message with the Retry and Cancel buttons. If you select the Retry button you’ll get a chance to re-enter the value. If you choose the Cancel button, the value will be restored to the one that was previously entered.
In this case, Excel displays a warning message. There are three options available: Yes, No and Cancel. If you choose Yes, Excel will enter the value that doesn’t meet the conditions. If you click No, Excel will let you change the data. If you click Cancel then the data will be restored to the previous value.
When information window appears, you can click OK to accept the value or Cancel to return to the previous state.
Excel checks the value only after you create the validation rule. If you apply validation to the cells that already contain values, Excel will not complain, even if those values are incompatible with the rule.
I am a blogger since 2010 and I’m the author of this website I'm a systems/network administrator and I enjoy solving complex problems and learning as much as I can about new technologies. I write tutorials based on my work experience and other IT stuff I find interesting.
since 2006 in online world also I am a troubleshooter for the well-known website like http://www.fixya.com and many more groups