12 simple tricks for efficient work in Excel (9 photos + 5 gifs)
To an uninitiated person, the Excel spreadsheet program seems huge, incomprehensible and therefore frightening.
In fact, this is a convenient tool, and if you know a little tricks, you can greatly reduce the time to perform ordinary functions.
1. Quickly add new data to the chart:
If new data has appeared on your worksheet for your chart that needs to be added, then you can simply select the range with the new information, copy it (Ctrl + C) and then paste it directly into the chart (Ctrl + V).
2. Instant Fill (Flash Fill)
This feature only appeared in the latest version of Excel 2013, but it is worth it to upgrade to the new version ahead of schedule. Suppose you have a list of full names (Ivanov Ivan Ivanovich), which you need to turn into abbreviated (Ivanov I.I.).
To perform such a conversion, you just need to start writing the desired text in the adjacent column manually. On the second or third line, Excel will try to predict our actions and perform further processing automatically. All that remains is to press Enter to confirm, and all names will be converted instantly.
3. Copy without breaking formats
You most likely know about the “magic” autocomplete marker - a thin black cross in the lower right corner of the cell, pulling it you can copy the contents of the cell or formula to several cells at once. However, there is one unpleasant nuance: such copying often violates the design of the table, since not only the formula is copied, but also the cell format. This can be avoided by immediately clicking on the smart tag after a black cross, a special icon that appears in the lower right corner of the copied area.
If you select the option “Fill Without Formatting”, then Microsoft Excel will copy your formula without a format and will not spoil the design.
4. Displaying data from an Excel spreadsheet on a map
The latest version of Excel 2013 made it possible to quickly display your geodata on an interactive map, for example, sales by city, etc. To do this, go to the "Office Store" on the "Insert" tab and install the Bing plug-in from there Maps
This can be done by direct link from the site by clicking the Add button. After adding a module, you can select it in the “My Apps” drop-down list on the “Insert” tab and place it on your worksheet. It remains to select your cells with data and click on the Show Locations button in the map module to see our data on it.
5. Quick jump to the desired sheet
A table of contents will appear, and you can go to any sheet you want instantly.
You can also create a table of contents with hyperlinks on a separate sheet. This is a little more complicated, but often more convenient.
6. Convert rows to columns and vice versa
If you ever had to shift cells from row to column with your hands, then you will appreciate the following trick:
Highlight a range.
Copy it (Ctrl + C) or, by clicking on the right mouse button, select "Copy" (Copy).
Right-click on the cell where you want to paste the data, and select one of the special insertion options in the context menu - the “Transpose” icon.
In older versions of Excel, there is no such icon, but you can solve the problem by using the special paste (Ctrl + Alt + V) and selecting the “Transpose” option
7. Drop-down list in the cell
If it is supposed to enter strictly defined values from an allowed set into any cell (for example, only “yes” and “no” or only from the list of company departments, etc.), this can be easily organized using the drop-down list:
Select the cell (or range of cells) in which this restriction should be.
Click the Data Validation button on the Data - Validation tab.
In the "Type" (Allow) drop-down list, select the "List" option.
In the "Source" field, specify the range containing the reference variants of the elements, which will subsequently appear upon input.
8. Smart table
If you select a range with data and click on “Format as Table” on the Home tab, then our list will be converted into a “smart” table, which (except for trendy striped coloring) has a lot of useful things:
Automatically stretch when adding new rows or columns to it.
The entered formulas will be automatically copied to the entire column.
The heading of such a table is automatically fixed when scrolling, and the filter buttons for selection and sorting are included in it.
On the appeared tab “Design” (Design) in such a table, you can add a row of totals with automatic calculation.
Sparklines are miniature diagrams drawn directly in cells that visually show the dynamics of our data. To create them, click the “Line” or “Histogram” (Columns) button in the “Sparklines” group on the “Insert” tab. In the window that opens, specify the range with the original numerical data and the cell where you want to display sparklines.
After clicking on the “OK” button, Microsoft Excel will create them in the specified cells. On the appeared tab “Design” (Design), you can further customize their color, type, enable the display of minimum and maximum values, etc.
10. Recovery of unsaved files
Friday. Evening. The long-awaited end of the shock working week. Anticipating the rest, you close the report that you were busy with the last half of the day, and in the dialog box "Save changes to file?" That suddenly clicks "No" for some reason.
An empty office announces your heart-rending cry, but it's too late - the last few hours of work have gone down the drain and instead of a pleasant evening with friends you will have to restore what was lost.
In fact, there is a good chance to rectify the situation. If you have Excel 2010, then click on “File” - “Recent” (File - Recent) and find the “Recover Unsaved Workbooks” button in the lower right corner of the screen. In Excel 2013, the path is slightly different: “File” - “Information” - “Versioning” - “Restore unsaved workbooks” (File - Properties - Recover Unsaved Workbooks). A special folder will open from the bowels of Microsoft Office, where, in such a case, temporary copies of all created or modified but unsaved books will be saved.
11. Comparison of two ranges for differences and matches
When working in Excel, it is often necessary to compare two lists and quickly find items that match or differ in them. The fastest and most visual way to do this:
Select both columns to compare (holding down the Ctrl key).
Select on the tab “Home” - “Conditional formatting” - “Rules for selecting cells” - “Duplicate Values” (Home - Conditional formatting - Highlight Cell Rules - Duplicate Values).
Select the Unique option from the drop-down list.
12. Selection (adjustment) of calculation results to the desired values
Have you ever picked up the input values in your Excel calculation to get the desired result on the output? At such moments, you feel like a seasoned gunner, right? Just a couple of dozens of iterations "under-flight - flight", and here it is, the long-awaited "hit"!
Microsoft Excel will be able to make such a fit for you, and faster and more accurately. To do this, click on the “What if” analysis button on the “Insert” tab and select the “Select - What If Analysis - Goal Seek” command. In the window that appears, specify the cell where you want to select the desired value, the desired result and the input cell that should change. After clicking on “OK”, Excel will execute up to 100 “shots” to select the result you want with an accuracy of 0.001.