Speeding up work in Excel: useful tips, functions, keyboard shortcuts

Microsoft Excel makes working easier and faster. The program has many little-known functions and commands that will greatly facilitate the work of even a confident user. Complex and routine calculations in Excel can be performed by setting commands with one click instead of five.

Here are useful tips and shortcuts that will replace complex operations with simple ones and help save time and effort. By taking courses from Ginger School you can improve your skills. 

Instant Fill

This feature is definitely in Excel 2013. For example, in a list with full names, you need to abbreviate names and patronymics. For example: from  Alekseev Aleksey Alekseevich  to  Alekseev A.A.  To do this, in the next column, you need to write 2-3 lines of such abbreviations manually, and then the program will prompt you to automatically repeat the actions with the remaining data - you just need to press enter.

Converting rows to columns and vice versa

You can save time by translating cells from row to column in 3 steps instead of a long routine rewriting:

1. Select area
2. Copy data
3. Right-click on the cell where the data should be moved, in the window that appears, select the “Transpose” icon

In older versions of Excel, there is no such icon, but this command is performed by pressing the key combination  ctrl + alt + V  and selecting the  "transpose" function . And with such simple mouse movements in the hand, the columns and rows are swapped.

Conditional Formatting

Something like a chart, only the indicators are not in the next cell, but right on the background of the data. This feature is enabled to highlight some data in a table.

For example, a teacher at a school makes a table of the average performance of each student. Then conditional formatting right inside the cells with grades will build a “graph” on which the average scores of each student will be highlighted, for example, from low to high. Or it will highlight cells in which the average score, for example, is lower or higher than 6. The parameters can be different.

In order for the function to work, you need to open  the Home tab, select the field with cells, find the Conditional Formatting  icon  in the Styles tool group   and select the appropriate option - it can be a bar chart, a color scale, or a set of icons. With the same command, you can independently set the rules for selecting cells.

Sparklines

This tricky word in Excel refers to a chart created right in the cell and showing the dynamics of row data. It is created like this:

1. Click "Insert"
2. Open "Sparklines"
3. Select "Graph" or "Histogram"
4. In the window that opens, specify a range with numbers and cells in which sparklines based on these numbers should appear

Macros

And this feature is designed to make life and heavy work tasks easier for users. It fixes the sequence of actions performed, and then does them automatically, without anyone's participation.

You need to enable the macro by opening  the "Developer" tab and finding the "Macro"  icon on the toolbar  , and next to it - a  similar icon with a red circle in the upper left corner . If you need to perform the same action many times, the macro recording will start, and then the computer will do it itself.

Forecasts

This option is not advertised by developers for a reason - it can predict the future! In fact, the function can predict, calculate future values ​​based on already known data that has already been written. Access to it is simple:

1. Specify  at least 2 cells  with source data
2. Open  "Data"  -  "Forecast"  -  "Forecast Sheet"
3. In the "Create forecast sheet" window  ,  click on the graph or histogram
4. In the  "End forecast" area  , set the end date, click  "Create"

Fill empty list cells

This will save you from the monotonous input of the same phrases in many cells. Of course, you can use the good old copy-paste (copy ctrl + c, paste ctrl + v), but if you need to fill in not 10 cells, but, for example, a hundred or two, and in some places the text will be different, the next hint will definitely come in handy.

Let's say there is a huge to-do list for the whole week, and you need to put a certain day of the week in front of each task. Do not prescribe for 20 Mondays, Tuesdays and Fridays. Each day of the week is placed in the column at the place where the to-do list for that day should begin. 

Next, a column with our days of the week is highlighted, in the  "Home" tab, the  buttons  are pressed "Find and select" ,  "Select a group of cells" ,  "Empty cells" . Then in the first empty cell you need to put the sign  "=" , use the "up" arrow   on the keyboard to return to the filled cell (for example, in the table - "Monday"). Press  ctrl+enter . Done, now all empty cells should be filled with duplicated data.

Find errors in a formula

It happens that the formula does not work, but the reason for the "breakdown" is not clear. Sometimes, in order to understand a complex formula (where other functions are taken as a function argument) or to find an error in it, you need to calculate only part of it. Here are two hints:

Part of the formula is calculated directly in the formula bar. To do this, select the desired area and press F9. Everything is simple, but there is one "but". If you forget to return everything to its place, that is, cancel the calculation of the function, and press enter, the calculated part will remain as a number.

Click on  "Calculate formula" in the "Formulas"  tab  . A window will open where you can calculate the formula step by step and thereby find the moment where the error appears, if, of course, there is one.

"Smart" table

If the user and MS Excel have a different understanding of the concept of a table, this can deprive him of many convenient functions and stretch the work time. Drawn with a pencil or with a border - the program will consider it a regular set of cells. In order for the data to be perceived exactly as a table, this field must be formatted.

It is necessary to select the desired area, in the  "Home" tab,  click the button  "Format as table" . In the list of different shapes and colors of design options, find the one that suits you.

Now this is a real table, and if you want to add new formulas to it, you need to insert them in border cells, then they will automatically spread to the entire column. Plus, the table header will always be visible when the cursor scrolls down.

Size-preserving copying

Surely everyone is familiar with the autocomplete marker. It's the little black cross in the right corner of a cell that allows you to copy content or a formula from one cell to multiple cells by dragging it down or to the side. So, in this case, copying can disrupt the “appearance” of the table, because the cell format is also copied. If you drag the cross and click  the smart tag  (the icon will appear at the bottom right of the copied range), the format will not be copied. Another option is the option  "Copy values ​​only" , in this case the design of the table will not be damaged either.

Delete empty cells

Quickly deal with all unnecessary empty cells is very simple:

1. Select column
2. Data Tab
3. Click "filter"

An arrow will appear above the columns, which you need to click on, and then in the menu that opens, under the numbers 1-9, check the box next to the item  "Empty cells" .

Find differences and matches between two areas

If you need to quickly find the same or different data in 2 lists, Excel will do this work itself. A few clicks and the program will highlight similar or different elements:

It is necessary to select the lists (while holding down ctrl). In the "Home" tab  ,  go to the button  "Conditional Formatting" . Then click  "Cell Selection Rules"  -  "Duplicate Values"  or  "Unique" . Ready.

Quick selection of values

It is known what value should be “output”, but there is uncertainty in the initial numbers. Of course, you can select data for a long time and recalculate the result manually, or you can save time and effort by using the parameter selection function.

1. Open  "Data"  -  "Working with data"  -  "What if analysis"  -  "Parameter selection"
2. In the  "Set in cell" area,  insert a link to the cell with the desired formula
3. In the  "Value" area,  write the desired result of the formula
4. In the  "Changing the value of the cell" area,  insert a reference to the cell with the corrected value and click  OK

fast travel

In Excel, you can instantly move any data: cells, rows, columns. The sequence of commands is as follows: select the desired area or cell, move the cursor over the border line of the selected field so that the pointer changes, then simply drag this data to where you need it.

Quickly add new values ​​to a chart

New data can be placed in a ready-made chart simply using the usual copy-paste: select the necessary values, copy them ( ctrl + c ) and paste them into the chart ( ctrl + v ).

Advanced Search

The ctrl + f combination  , as everyone knows, leads to a search menu that will find any information in this program. The function has several secrets - the signs  "?"  and  "*"  will include the real detective in the search: they can be used to find data if you are not sure about the accuracy of the request. The question mark will replace one unknown character, and the asterisk (this sign is commonly called an asterisk) will replace several unknowns at once.

When it is these characters that need to be found in the data heap, they are preceded  by the “~” sign . Then the program will not take them for unknown characters.

Restoring an unsaved file

In case the end of the world comes.

For many users, most likely, this situation will be familiar: they were so happy at the end of the working day that they clicked “no” on the caring question of MS Excel “save the last changes”. And the work of all the last hours has gone into oblivion. Salvation is possible, there is hope.

MS Excel 2010. The sequence of commands is as follows:  "File"  -  "Recent"  -  "Restore unsaved books"  (button at the bottom right).

MS Excel 2013.  "File"  -  "Details"  -  "Version Control"  -  "Restore Unsaved Workbooks" . And the program will open the secret world of all temporary copies of books that were created or changed, but were not saved.

Hot key combinations

Excel itself will give a good hint if you press the alt key. Letters appear above the various buttons on the toolbar. When you click on them, certain functions will be launched, this will also help you remember some hot keys. Also, most of  the useful keyboard shortcuts  can be found on the official page of the program.

At a time, such a number of functions and combinations cannot be remembered, but they will be stored in memory themselves if you periodically apply some of them while working with data and use excel course. This will make the whole job much faster and easier.