10+ Excel features in Home tab that every beginner MUST learn

1 Don’t just Paste, Paste Special!

Position in tab: Clipboard group>Paste button drop down | Shortcut: Alt+Ctrl+V

This one is definitely a speciality in Excel’s arsenal of features. Normal paste option only put what you copy in place. Whereas paste special gives you total command over what exactly you want to paste and how do you want to paste it.

There are over 20 options paste special offers from which you can make selection. The 20 options are divided in three parts that work exclusively.

Format painter Spent time formatting the cell? Like making text bold, selection font type, having the right font size, text color, cell color and so on and want the same format on another cell or cells well you don’t have to repeat the whole process as you can do the painting of the same with Format painter. Simply select the cell or cells the format of which you like and click the format paint button to turn it on and click at the cell and the format will pasted or painted there as well. If you have multiple cell or cells to paint then double click the format painter button and it will not deactivate automatically on painting the format once.

Checkout our tutorials where Paste Special shows its magic

2 Office Clipboard

Position in tab: Jump link at the bottom right corner of clipboard group | Shortcut: Ctrl+C Twice / Alt+H+F+O

For instance you have several things to copy and paste but it can become a cause of frustration because once you copy new data the old data is not available for pasting anymore and you will have to copy it again. Well this problem is catered quite magnificently by office clipboard which can hold up to 24 discrete items in the repository and you can paste any of them as and when you want them without copying again. So you can easily go paste paste paste! To access clipboard with ease you can turn the option on from clipboard options whether it must appear every time you press Ctrl+C or even better only when you press Ctrl+C twice in which case you will have the flexibility.

3 Formatting, borders Wrap Text / Merge & center

Position in tab: Alignment group

When ever you populate cell with text and if the next cell(s) in the same row is(are) empty then text will spill over to such cell(s). And even more, if the next cell is filled then text of previous cell gets hidden. To solve this problem we can use wrap text. It basically put the text in next line if it approaches the right wall of cell and this way whole text is visible by automatically adjusting row height. Where wrap text let you manage text within a cell, you might think of merging multiple cell as one.

This is essential in cases where you data divided in subcategories. To merge cell you can use merge option by clicking merge and center button in the home tab>Alignment group. By default cells are merged and content is center aligned as well. Using this function you can merge cells spanning on multiple columns, rows or both. Even if you have merged multiple cells as one, the address of the newly merged cell will be the address of top left cell merged with others.

4 Number Formatting

Position: Number group | Shortcut: Ctrl+1

Number of things you can do in Number group This one has numerous things to be discussed. And this area alone is discussed in several chapters in Excel books. The reason is that you can just do so many things with it.

Excel has a collection of ready made number formats as well which user can chose from drop down menu and most of the time it suffice majority of needs. However, if you are thinking of going overboard you are still covered!

Special formats Custom formats – where the real game begins Available options not enough? Make your own! Though it require some knowledge of underlying concept but if you are able to harness it, you can put this to use in many ways.

Checkout articles on Custom Number Formatting to learn how this concept is applied

5 Styles: Conditional Formatting / Tables / Cell styles

Position: Styles group | Conditional Formatting [Alt+H+L]

Want to highlight results that are off target? Or want excel to highlight debtors that are past due for over 6 months? For all this and similar needs you have conditional formatting. What it does, it changes the color of cell or formats it the way you want it if a certain conditions gets fulfilled or otherwise. Check out Birth date heat map which is based on conditional formatting.

Format as table [Alt+H+T]

Format as table At first I thought that this options just let you cloak the data as if it is a table but it turns out that simple data range gets turned in to full featured table. Why tables are such a big joy to have you can find that out by reading:

• Introduction to Excel Tables – Data beyond just formatting
• 20+ Excel Table tricks to turbo charge your data

6 Format button in cells group

Position: Cells group | Shortcut: Alt+H+O

Don’t be misguided by the name of this button it is home to some of the powerful features that every Excel pro uses. Change tab color Rename sheet Move or copy sheet Want to move the worksheet before or after certain tabs or want to make a copy of it within the same workbook or in another workbook. This will help you do it. protect sheet / lock cell Let you protect the worksheet by enabling or disabling features of workbook for those who do not know password. You can make only selected part secure and rest of the workbook or worksheet unprotected Sale price per unit column and total column where e

7 Fill

Position: Editing group | Shortcut: Alt+F+H+I

Want the same thing in multiple cells? Use Mr. Fill, I mean Fill feature. It lets you fill the cells with a particular value or series of value in any direction of current cell, right, left, up or down. Mostly Excel PROs know the shortcuts to do it but until you memorize the shortcut you can access it from the ribbon under Home tab>Editing group.

8 Clear

Position: Editing group | Shortcut: Alt+H+E

By default if you have formatted the cell and later deletes the content by pressing DEL key then only the contents gets deleted. Excel still remembers the format settings for that specific cell and next time if you type anything it will gets formatted the same way. Same is the case with cell color. By pressing delete you cannot delete format. But Clear feature gives you the flexibility of doing several things.

• Clear All: Removes everything, literally stripping cell to bare default. Formula, formatting of any type, value etc gets deleted.
• Clear formats: only deletes formatting without affecting the value or formula within the cell
• Clear comment: deletes the comments appended to cell Clear hyperlink: any kind of hyperlinks in the cell will be removed however the text and formatting will be kept as is.
• Remove hyperlink: this removes the hyperlink and formatting as well.

9 Sort and Filter

Position: Editing group | Shortcut: Alt+H+S

Sort your data ascending or descending way or in any way  you want using custom sort. To learn about how sorting works and how to do custom sort the read:

• One Minute Excel Tutorial – Sorting
• Multi level or Multi column sort in Excel – How To
• Custom Autofill series and Custom sorting with Custom lists in Excel
• Access collection of discussion on Sorting in Excel visit this page

Filter the data for specific values using certain criteria

Shortcut: As the name suggests it let you filter the data based on specific condition and shrinks the data temporarily to display only such values that fulfill the criteria A range that has filter option enabled gets a downward pointing arrow in its first row (mostly the header row). Clicking the arrow one can access different options and this includes sorting options as well. The options in the menu are contextual i.e. if the range contains text then options are a little different. If the range has numbers then additional options in the form of number filters becomes available.

10 Find and Replace

Excel’s find is no normal find. Its full power packed practical feature that can help you in number of ways if you know how to use it. Wildcards, find using format of cell, case sensitive, within formula, comments, Its not just find its find and select i.e. once you find something you can select that as well and quickly change the content of all such cells immediately in few steps. To speed up things you can search something within worksheet instead of whole workbook

Replace is an additional step to find. First it finds and the replaces it with what you provided in the replace field. All the options that are available for find are also available for replace and works the same way.

Checkout our Find and Replace articles to see this powerful took showings its colors

11 Go to and Go to Special

Position: Editing group > Find and Select | Shortcut: Alt+H+F+D+S

My own and personal favourite of almost every Excel head out there. One amazing feature that can come to rescue big time and in many cases the way it handles the situation no other feature does.

To learn how to apply this feature we have some examples and tutorials for you. Check out our Go To Special articles

12 Data Validation

Position: Editing group > Find and Select | Shortcut: Alt+H+F+D+V

A life saver for many especially if you have routine task that needs to be done on repetitive basis with accuracy. This feature is for you! As the name suggests Excel helps you validate the data before it becomes part of information or report.

Checkout our Data Validation articles to learn this feature in more detail