Often have to deal with MS Excel? Then do not miss a portion of tips on optimizing work in this program.
Quickly add new data to a chart
If for your already constructed diagram, new data appeared on the sheet that needs to be added, then you can simply select a range with new information, copy it (Ctrl + C) and then paste it directly into the diagram (Ctrl + V).
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 time. Suppose that you have a list of full names (Ivan Ivan Ivanovich), which you need to turn into abbreviations (Ivanov 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. You will only have to press the Enter key to confirm, and all names will be converted instantly.
Similarly, you can extract names from emails, glue the name of the fragments, etc.
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, by pulling which you can copy the contents of the cell or the 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 if immediately after pulling with a black cross, click on the smart tag - a special icon that appears in the lower right corner of the copied area.
If you select the option "Copy only values" (Fill Without Formatting), then Microsoft Excel will copy your formula without format and will not spoil the design.
Displaying data from an Excel spreadsheet on a map
The latest version of Excel 2013 has the ability to quickly display your geodata on an interactive map, for example, sales by city, etc. To do this, go to the "App Store" on the Insert tab and install the Bing plugin from there Maps. This can be done via a 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 (Insert) and place it on your worksheet. It remains to select your data cells and click on the Show Locations button in the map module to see our data on it.
If desired, in the plugin settings, you can select the type of chart and the colors to be displayed.
Quick transition to the desired sheet
If in your book the number of worksheets exceeded 10, then it becomes difficult to navigate in them. Right-click on any of the sheet scroll buttons in the lower left corner of the screen.
A table of contents will appear, and you can go to any desired sheet instantly.
You can also create on a separate sheet a table of contents with hyperlinks. It is a little more complicated, but often more convenient.
Convert rows to columns and back
If you have ever had to manually shift cells from rows to columns, then you will appreciate the following trick:
- Highlight the range.
- Copy it (Ctrl + C) or, by pressing the right mouse button, select "Copy" (Copy).
- Right-click on the cell where you want to insert 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 a special insert (Ctrl + Alt + V) and choosing the option "Transpose" (Transpose).
Drop-down list in the cell
If it is intended to enter strictly defined values from the allowed set (for example, only "yes" and "no" or only from the list of departments of the company, etc.), then this can be easily organized using the drop-down list:
- Select the cell (or range of cells) in which there should be such a restriction.
- Click the Data Validation button on the Data tab (Validation).
- In the "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 fall out as you type.
Advanced tricks from the same series: a drop-down list with filling, related drop-down lists, a drop-down list with a photo, etc.
If you select a range with data and on the Home tab click "Format as a table" (Home - Format as Table), then our list will be transformed into a "smart" table, which (except for a fancy striped coloring) knows a lot of useful things:
- Automatically stretch when adding new rows or columns to it.
- The entered formulas will be automatically copied to the whole column.
- The cap of this table is automatically fixed when scrolling, and it includes filter buttons for selection and sorting.
- On the Design tab that appears (Design), you can add a row of totals with automatic calculation to such a table.
Sparklines are miniature charts drawn directly in the cells, which graphically display the dynamics of our data. To create them, click the "Graph" (Line) or "Histogram" (Columns) in the "Sparklines" group on the "Insert" tab. In the window that opens, specify the range with the initial numerical data and the cells where you want to bring sparklines.
After clicking on the "OK" button, Microsoft Excel will create them in the specified cells. On the Design tab (Design), you can further customize their color, type, enable the display of minimum and maximum values, etc.
Recover Unsaved Files
Friday. Evening. The long-awaited end of the shock work week. Looking forward to the rest, you close the report you were busy with the last half of the day, and in the dialog that appears, "Save changes to the file?" Suddenly, for some reason, press "No".
The empty office announces your heart-rending cry, but it is too late - the last few hours of work went to the dog's tail and instead of a pleasant evening with friends, you will have to recover the lost.
In fact, there is a strong chance to correct the situation. If you have Excel 2010, then click on "File" - "Recent" (File - Recent) and find the button Recover Unsaved Workbooks in the lower right corner of the screen. In Excel 2013, the path is slightly different: "File" - "Details" - "Version Management" - "Recover Unsaved Workbooks" (File - Properties - Recover Unsaved Workbooks). A special folder will open from the depths of Microsoft Office, where temporary copies of all books created or modified but not saved are saved to such a case.
Comparing two ranges for differences and matches
Very often, when working in Excel, it is necessary to compare two lists and quickly find items that are the same or different in them. The fastest and most visual way to do this:
- Select both compared columns (while holding the Ctrl key).
- Select on the Home tab - Conditional Formatting - Cell Allocation Rules - Duplicate Values (Home - Conditional formatting - Highlight Cell Rules - Duplicate Values).
- Select the "Unique" option from the drop-down list.
Bonus: other ways to compare two ranges and find differences / matches.
Selection (adjustment) of the calculation results for the required values
Have you ever picked up input values in your Excel calculation to get the output you need? At such moments you feel like a hard-nosed gunner, right? Only a couple of dozens of iterations "undershoot - 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 accurate. To do this, click on the "Insert" tab of the "What if?" Button and select the "Parameter selection" command (Insert - What If Analysis - Goal Seek). 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 be changed. After clicking on "OK", Excel will execute up to 100 "shots" in order to select the total you require with an accuracy of 0.001.
If this detailed review does not cover all the useful MS Excel chips that you know about, share them in the comments!