12 Simple Yet Effective Tricks To Get Things Done Faster In Excel

Advice 2023

Table of contents:

12 Simple Yet Effective Tricks To Get Things Done Faster In Excel
12 Simple Yet Effective Tricks To Get Things Done Faster In Excel

Video: 12 Simple Yet Effective Tricks To Get Things Done Faster In Excel

Отличия серверных жестких дисков от десктопных
Video: Top 25 Excel 2016 Tips and Tricks 2023, January
Anonim

Do you often have to deal with MS Excel? Then do not miss a portion of tips for optimizing work in this program.

Quickly add new data to a chart

If for your already built chart on the sheet there is new data 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 chart (Ctrl + V).

e.com-save
e.com-save

Flash Fill

This feature only appeared in the latest version of Excel 2013, but it's worth upgrading to the new version early. Suppose you have a list of full names (Ivanov Ivan Ivanovich), which you need to turn into abbreviated names (Ivanov I.I.). To perform such a transformation, you just need to start writing the desired text in the adjacent column by hand. On the second or third line, Excel will try to predict our actions and perform further processing automatically. All you have to do is press the Enter key to confirm, and all names will be converted instantly.

Flash Fill
Flash Fill

Similarly, you can extract names from emails, glue names from 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 a cell, by pulling on which you can copy the contents of a cell or a 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 clicking on the smart tag, a special icon that appears in the lower right corner of the copied area, immediately after stretching with a black cross.

If you select the Fill Without Formatting option, Microsoft Excel will copy your formula without formatting and will not spoil the layout.

e.com-save (1)
e.com-save (1)

Displaying data from an Excel spreadsheet on a map

In the latest version of Excel 2013, it became possible to quickly display your geodata on an interactive map, for example, sales by city, etc. To do this, you need to go to the Office Store on the Insert tab and install the Bing plugin from there Maps. This can be done by a direct link from the site by clicking the Add button. After adding a module, you can select it from 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.

If desired, in the plugin settings, you can select the type of chart and colors to display.

Quick jump to the desired sheet

If your book has more than 10 worksheets, it becomes difficult to navigate them. Right-click on any of the sheet tabs scroll buttons in the lower left corner of the screen.

Quick jump to the desired sheet
Quick jump to the desired sheet

The table of contents will appear, and you can jump to any desired sheet instantly.

You can also create a table of contents with hyperlinks on a separate sheet. This is a little more difficult, but often more convenient.

Convert rows to columns and vice versa

If you have ever had to move cells from rows to columns by hand, then you will appreciate the following trick:

  1. Highlight the range.
  2. Copy it (Ctrl + C) or, by clicking on the right mouse button, select "Copy" (Copy).
  3. Right-click on the cell where you want to paste the data and select one of the Paste Special options - the Transpose icon from the context menu.
e.com-save (2)
e.com-save (2)

Older versions of Excel do not have this icon, but you can fix the problem by using Paste Special (Ctrl + alt = " + V) and selecting the Transpose option.

Dropdown list in a cell

Dropdown list in a cell
Dropdown list in a cell

If in any cell it is supposed to enter strictly defined values ​​from the allowed set (for example, only "yes" and "no" or only from the list of company departments, etc.), then this can be easily organized using the drop-down list:

  1. Select a cell (or a range of cells) that should have such a limitation.
  2. Click the Data Validation button on the Dat-Validation tab.
  3. In the drop-down list "Type" (Allow) select the option "List" (List).
  4. In the field "Source" (Source) specify the range containing the reference variants of the elements, which will subsequently drop out when typing.

Advanced tricks from the same series: dropdown with content, linked dropdowns, dropdown with photo, etc.

Smart table

Smart table
Smart table

If you select a range with data and on the "Home" tab click "Format as Table" (Home - Format as Table), then our list will be converted into a "smart" table, which (besides the trendy striped coloring) can do a lot of useful things:

  • Stretch automatically when new rows or columns are added to it.
  • The entered formulas will be automatically copied to the entire column.
  • The header of such a table is automatically fixed when scrolling, and it includes filter buttons for filtering and sorting.
  • On the appeared tab "Design" in such a table, you can add a row of totals with automatic calculation.

Sparklines

Sparklines are miniature charts drawn right in cells that visualize the dynamics of our data. To create them, click the Line or 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 cells where you want to display the sparklines.

After clicking on the "OK" button, Microsoft Excel will create them in the specified cells. On the "Design" tab that appears, 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. Anticipating a break, you close the report you were busy with for the last half of the day, and in the “Save changes to file?” Dialog box that appears. suddenly for some reason you press "No".

The empty office announces your heart-rending cry, but it's too late - the last few hours of work went down the drain and instead of a pleasant evening in the company of 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 button "Recover Unsaved Workbooks" in the lower right corner of the screen. In Excel 2013, the path is slightly different: "File" - "Info" - "Version Control" - "Recover Unsaved Workbooks" (File - Properties - Recover Unsaved Workbooks). A special folder from the bowels of Microsoft Office will open, where temporary copies of all created or modified, but unsaved books are saved in such a case.

Comparison of two ranges for differences and coincidences

Quite often, when working in Excel, it becomes necessary to compare two lists and quickly find items that are the same or different in them. The fastest and most intuitive way to do this:

  1. Select both compared columns (hold down the Ctrl key).
  2. Select Home - Conditional formatting - Highlight Cell Rules - Duplicate Values ​​on the Home tab.
  3. Select the Unique option from the dropdown list.

Bonus: other ways to compare two ranges and find differences / matches.

Selection (adjustment) of calculation results for the required values

Have you ever tweaked the input values ​​in your Excel calculation to get the output you want? At such moments you feel like a hardened artilleryman, right? Just a couple of dozen iterations of "undershoot - overflights", and here it is, the long-awaited "hit"!

Microsoft Excel can do this for you, faster and more accurately. To do this, click the "What If" button on the "Insert" tab and select the "Insert - 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 be changed. After clicking on "OK" Excel will execute up to 100 "shots" in order to find the required total with an accuracy of 0.001.

Selection (adjustment) of calculation results for the required values
Selection (adjustment) of calculation results for the required values

If this detailed review did not cover all the useful MS Excel tricks that you know about, share them in the comments!

Popular by topic