20 Excel secrets that will help simplify

Releasing Excel 2010, the Microsoft almost doubled the functionality of the program, adding many improvements and innovations, many of which are not immediately visible. Whether you are an experienced user or a beginner, there are many ways to simplify work with Excel. On some of them we now describe.

Select all the cells in one click

All cells can be identified with the key combination Ctrl + A, which, incidentally, works with all other programs. However, there is an easier way of selection. Clicking on the button in the corner of an Excel sheet, you select all the cells in one click.

Open multiple files at once

Instead of opening every Excel file separately, they can be opened together. To do this, select the files you want to open, and press Enter.

Navigating the Excel file

When you open a few books to Excel, between them can be easily navigated using the keyboard shortcut Ctrl + Tab. This feature is also available for the entire Windows system, and it can be used in many applications. For example, to switch tabs in the browser.

Adding new buttons to the Quick Access Toolbar

Standard in Excel quick access panel has 3 buttons. You can change this number and add the ones that need you.

Go to "File" ⇒ "Settings" ⇒ "Quick Access Toolbar". Now you can select any of the buttons that you need.

The diagonal line in the cells

Sometimes there are situations when you need to add to the table a diagonal line. For example, to separate the date and time. To do this, Excel home page, click on the icon of the usual boundaries, and select "other boundaries".

Adding a table of empty rows or columns

Insert one row or column is simple. But what if you want to paste a lot more? Select the desired number of rows or columns, and click "Paste". Then select the location where you want to move the cells and you get a number of empty rows.

Copying and moving speed information

If you need to move any information (cell, row, column) into Excel, select it and move your mouse over the border to change the pointer. After that, move the information in the place that you need. If you want to copy the information, do the same thing, but with pressed key Ctrl.

Rapid removal of empty cells

empty cells — Excel is a scourge. Sometimes they just appear out of nowhere. To get rid of them all at one time, select the column, click on the tab "Data" and then click "Filter". Above each column will be a downward pointing arrow. Clicking on it brings you to the menu, which will help get rid of the empty fields.

Advanced Search

By pressing Ctrl + F, we find ourselves in the search menu, with which you can search for any data in Excel. However, its functionality can be extended by using the symbols "?" And "*". The question mark is responsible for one unknown character, and the asterisk — a few. They should be used if you are not sure what it looks like the desired request.

If you need to find a question mark or an asterisk and you do not want to replace them sought Excel unknown character, put in front of them "~".

Copying unique records

Unique records can be useful if you need to select the table nonrecurring information. For example, one man for every age. To do this, select the column and click the "Advanced" on the left of the item "Filter". Select a source range (where copy) and the range in which you want to place the result. Do not forget to put a tick.

Create sample

If you do a survey, which is open only to men between 19 and 60, you can easily create a similar sample using Excel. Go to the menu item "Data" ⇒ "Data Validation" and select the desired band or another condition. Introducing information that does not fit this condition, users will receive a message that the information is wrong.

Quick navigation with Ctrl and arrow

Press Ctrl + arrow, can be moved into the extreme point of the sheet. For example, Ctrl + ⇓ will take the cursor to the bottom of the sheet.

Transposition information from a column in a row

Quite a useful feature that is needed is not so often. But if it is suddenly needed, it is unlikely you will be transposed individually. To transpose into Excel has a special insert.

Copy the range of cells that you want to transpose. After that, right click on the desired location and choose Paste Special.

How to hide the information in Excel

I do not know why it can be useful, but nevertheless such a function in Excel is. To highlight a range of cells, click the "Format" ⇒ "Hide or show" and select the desired action.

Combining text using the "&"

If you need to combine text from multiple cells into one, not necessary to use complex formulas. It suffices to select a cell, which will be connected text press "=" and sequentially select the cells, setting each character "&".

Change capitalization

By using certain formulas register can be changed throughout the text information in Excel. The "UPPER" makes all the letters uppercase, and the "LOWER" — lowercase. "PROPER" makes the capital only the first letter in each word.

Adding information with leading zeros

If you type in Excel number of 000,356, the program will automatically turn it into a 356. If you want to keep the zeros at the beginning, put an apostrophe before the number " '".

Acceleration input compound word

If you frequently enter the same word, will be delighted to learn that there AutoCorrect in Excel. It is very similar to the AutoCorrect in smartphones, so you immediately understand how to use it. It can be used to replace the duplicate construction abbreviations. For example, Catherine Petrova — EP.

More information

In the lower right corner you can keep track of various information. However, few people know that by clicking to the right mouse button, you can remove unnecessary and add the necessary lines.

Renaming sheet by double clicking

This is the easiest way to rename a sheet. Just click on it twice left mouse button and type a new name.

Do you often use Excel? If yes, then you probably have their secrets of working with this program. Share them in the comments.