Format in Excel 2016

Check invalid data

Excel has a useful Data Validation feature to display messages and prevent incorrect cell entry. This feature works fine with manual data typing. However, validation messages will not be displayed if invalid data is entered into a cell by copying or filling; a formula in a cell evaluates to an invalid result; a macro enters invalid data into a cell; etc.

Using a form for data entry

Usually, the data in Excel spreadsheets is presented in lists of rows, and each row represents a data record. Excel offers an easy way to work with this data type through a data entry form that Excel can automatically generate. This form of data works with a normal data range or a table.

Apply or remove cell borders

Another way to highlight data in Excel, along with the use of colors, is to use borders. Borders and lines within borders are often used to group a range of similar cells or to delimit rows or columns. Excel offers 13 predefined border styles and tools to create your styles.

Splitting text strings without using formulas

When working with data in Excel spreadsheets, it is sometimes necessary to split text lines into columns. Excel offers many different functions for working with text strings, but you don't have to use formulas for this task:

Check data entry for invalid entries

You can greatly reduce data-entry problems in your workbooks by making Excel check entries before entering them in specific cells. To do so, you need to define restrictions and data-validation rules for those cells.

Removing excess spaces and nonprinting characters

Data imported into an Excel spreadsheet often contains extra spaces or strange (often unprintable) characters. There are two handy functions, TRIM and CLEAN, to cleanup such data:

Adding and removing page breaks

Usually, all workbooks are opened in the Normal view in Excel. Thus, page breaks aren't displayed until you print (or preview) the spreadsheet or entire workbook at least one time. In a preview, horizontal and vertical dotted lines indicate the page breaks:

Using a custom sort order

Excel proposes different ways to sort data. You may sort your data in alphabetical order, according to the background or text color of the data, etc.
In some cases, you may want to sort your data in non-standard ways. For example, if your data consists of month names, you usually want it to be ordered by month numbers rather than alphabetically.

Recalculating the worksheet

By default, Excel automatic recalculation feature is enabled. Thus, all formulas in the workbook sheets recalculate immediately after data dependencies change. Automatic recalculation of all formulas also occurs when opening a workbook.

How to insert fractions

Fractions represent equal parts of a whole object or a collection of items. This tip describes how to use fractions simply. For example:
  • I paid the third part, that is, I paid 1/3 of the transaction,
  • Five of our group of 17 wore yellow dresses, which means 5/17 of our group were in yellow,