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 will tell today.

Select all the cells in one click

All cells can be identified with the key combination Ctrl + A, which, incidentally, operates in 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 & nbsp; 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 function is also available on all Windows system, and can be used in many applications. For example, to switch tabs in the browser.

Adding a   new button on the Quick Access Toolbar


By default, the Quick Access Toolbar Excel are 3 buttons. You can change this number and add the ones you need it.

Go to « File» ⇒ &Laquo; Settings »   ⇒ &Laquo; Quick Access Toolbar ». Now you can select the   all 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 divide the time and date. To do this, Excel home page, click on the icon of the usual boundaries and select « other border ».

Adding a table of empty columns and rows

Insert one row or column is simple. But what if you want to insert a lot more? Select the desired number of rows or columns, and then click « Insert ». Then select the location where you want to move the cells, and you get the right amount of blank lines.

High-speed copying and moving data

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 Ctrl key.

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 « information » and click on « Filter ». Above each column will be a downward pointing arrow. Clicking on it brings you to the menu, which will help get rid of empty fields.

Advanced Search


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 «?&Raquo; 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 asterisk, and you do not want to replace them sought Excel unknown character, put in front of them «~».

Copy   unique records

Unique records can be useful if you need to select the table nonrecurring information. For example, one person every age. To do this, select the column and click the « Optional » to 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 in which only men can participate from 19 to 60, you can easily create   a similar sample using Excel. Go to the menu item « Data »   ⇒ &Laquo; Data Validation & raquo; and select the desired range or other condition. Introducing information that does not fit this condition, users will receive a message that the information is incorrect.

Quick navigation with Ctrl and arrow

Press Ctrl + arrow, you can move to the extreme points of the sheet. For example, Ctrl +  will take the cursor to the bottom of the page.

Transposition information from a column in a row


Quite a useful feature, which is necessary not so often. But if it is a sudden need, it is unlikely you will transpose the & nbsp; one. For transposition 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 select 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. Highlight the range of cells, press «Формат» ⇒ «Hide or show » and select the desired action.

Combining text with «&»

If you need to combine text from multiple cells into one, not necessarily to use complex formulas. Simply select the cell where you will be connected to text, press the «=» and sequentially select the cells, placing in front of each symbol «&».

Change capitalization

By using certain formulas, you can change the case throughout the text information to Excel. Function « UPPER » makes all the letters uppercase, and «LOWER» — lowercase. «PROPER & raquo; making capital only the first letter in each word.

Adding data with zeros at the beginning of

If you type in Excel number 000,356, the program will automatically turn it into 356. If you want to keep the zeros at the beginning, put an apostrophe before the number «’».

Ускорение ввода сложных слов

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. You can use it to replace the repetitive design abbreviations. For example, Ekaterina Petrova  ЕП.

More information

In the lower right corner you can keep track of various information. However, few people know that there pressing the right mouse button, you can remove unnecessary and add the desired line.

Rename sheets with a double-click


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


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