How to quickly add data, create a smart table or save a disrupted file.
Nikolai Pavlov author of the Planet Excel project, developer and IT trainer.
1. Quickly add new data to the diagram
If new data that needs to be added to the sheet has appeared on the sheet, then you can simply select the range with new information, copy it (Ctrl + C) and then insert it directly into the diagram (Ctrl + V).
2. Instant Fill (Flash Fill)
Suppose you have a list of full name (Ivanov Ivan Ivanovich), which you need to turn into abbreviated ones (Ivanov and. AND.). To do this, you just need to start writing the desired text in the next column manually. On the second or third line, Excel will try to predict our actions and perform further processing automatically. It remains only to press the Enter key to confirm, and all names will be transformed instantly. Similarly, you can extract names from email, glue the name from fragments, and so on.
3. Copying without violation of formats
You most likely know about the magical marker of auto -filling. This is a thin black cross in the lower right corner of the cell, pulling the contents of the cell or the formula for 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 format of the cell. This can be avoided. Immediately after they pulled the black cross, click on a smart tag-a special icon that appears in the right corner of the copied area.
If you select the “Copy Without Formatting) option, then Excel copys your formula without format and will not spoil the design.
4. Data display from the Excel table on the map
In Excel, you can quickly display your geodata on an interactive map, such as sales by cities. To do this, you need to go to the “Appendix” store on the Insert tab and install the Bing Maps Plugin from there. This can be done by direct link from the site by clicking the Get it now button.
After adding the module, it can be selected in the drop -down list “My Apps” on the “Insert” tab and place on your working sheet. It remains to allocate your cells with data and click on the Show Locations button in the card module to see our data on it. If desired in the plugin settings, you can choose the type of diagram and color for display.
5. Quick transition to the right sheet
If in the file the number of work sheets exceeded 10, then it becomes difficult to navigate them. Click with the right mouse button on any of the buttons of scrolling leaf label labels in the lower left corner of the screen. There will be a table of contents, and it will be possible to go to any desired sheet instantly.
6. Converting lines into columns and vice versa
If you ever had to shift the cells from the lines into the columns with your hands, then you will appreciate the following trick:
- Highlight the range.
- Copy it (ctrl + c) or, by clicking on the right mouse button, select “Copy” (Copy).
- Click with the right mouse button on the cell where you want to insert data, and select one of the options for a special insert in the context menu – the “Transpose” icon (Transpose). There are no such icon in old versions of Excel, but you can solve the problem using a special insert (Ctrl + Alt + V) and the choice of the “Transpose” option (Transpose).
7. False
If any cell is supposed to enter strictly defined values from the permitted set (for example, only “yes” and “no” or only from the list of departments of the company and so on), then this can be easily organized with the help of a drop-down list.
- Highlight the cell (or the range of cells) in which there should be such a restriction.
- Click the “Data Verification” button on the “Data” tab (Data → Validation).
- In the drop -down list “Type” (allow), select the “List” option (LIST).
- In the Source field, set the range containing the reference options for elements, which will subsequently fall out when entering.
8. Smart table
If you highlight the range with data and click on the “Home” tab “Format as a table” (Home → Format as Table), then our list will be converted into a smart table that can do a lot of useful things:
- Automatically stretches when adding new lines or columns to it.
- The introduced formulas automatically will be copied to the entire column.
- The hat of such a table is automatically fixed during scrolling, and the filter buttons for selection and sorting are included in it.
- On the “Design” tab (Design) to such a table, you can add a line of results with automatic calculation.
9. Sparclayans
Sparclayans are miniature diagrams drawn right in cells that clearly display the dynamics of our data. To create them, click the “Graph” button (Line) or “Histogram” in the “Sparkhalines” group on the “Insert” tab (Insert). In the window that opens, indicate the range with the initial numerical data and the cells where you want to bring sparclayans.
After clicking on the “OK” button, Microsoft Excel will create them in these cells. On the “Design” tab (Design), you can additionally configure their color, type, enable the display of the minimum and maximum values and so on.
10. Restoration of disrupted files
Imagine: you close the report with which the last half of the day was fiddled, and in the dialog box “Save changes in the file?”Suddenly, for some reason, press” no “. The office announces your heart -rending cry, but it’s too late: the last few hours of work went under the tail.
There is actually a chance to rectify the situation. If you have Excel 2010, then click on the “File” → “File → Recent) and find the“ Restore Unsaved Workbooks ”button in the lower right corner of the screen.
In Excel 2013, the path is slightly different: “File” → “Information” → “Management of Versions” → “Restore Unfinished Books” (File – Properties – Recover Unsaved Workbooks).
In subsequent versions of Excel, you should open the “File” → “Information” → “Book Management”.
A special folder from the bowels of Microsoft Office will open, where in such a case, temporary copies of all created or changed, but disrupted books are preserved.
11. Comparison of two ranges for differences and coincidences
Sometimes when working in Excel it becomes necessary to compare two lists and quickly find the elements that coincide or differ in them. Here is the fastest and most visual way to do this:
- Select both compared columns (holding the Ctrl key).
- Select on the “Home” tab → “Conditional Formatting” → “Rules for the Selection of cells” → “Repeating values” (Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values).
- Select the option “unique” in the opening list.
12. Selection (fitting) of the calculation results for the desired values
You someday selected the input values in your Excel calculation to get the desired result at the output? At such moments, you feel like a seasoned artilleryman: just a couple of dozens of iterations “short-lived-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 “Data” tab “Analysis“ What if ”and select the command“ Selection of the Parameter ”(Insert → What if Analysis → Goal Seek). In the window that appears, set the cell where you want to choose the right value, the desired result and the entrance cell that should change. After clicking on OK, Excel will perform up to 100 “shots” to choose the result you need up to 0.001.
If this review did not cover all the useful chips of MS Excel, which you know about, share them in the comments!
Nikolai Pavlov author of the Planet Excel project, developer and IT trainer.
1. Quickly add new data to the diagram
If new data that needs to be added to the sheet has appeared on the sheet, then you can simply select the range with new information, copy it (Ctrl + C) and then insert it directly into the diagram (Ctrl + V).
2. Instant Fill (Flash Fill)
Suppose you have a list of full name (Ivanov Ivan Ivanovich), which you need to turn into abbreviated ones (Ivanov and. AND.). To do this, you just need to start writing the desired text in the next column manually. On the second or third line, Excel will try to predict our actions and perform further processing automatically. It remains only to press the Enter key to confirm, and all names will be transformed instantly. Similarly, you can extract names from email, glue the name from fragments, and so on.
3. Copying without violation of formats
You most likely know about the magical marker of auto -filling. This is a thin black cross in the lower right corner of the cell, pulling the contents of the cell or the formula for 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 format of the cell. This can be avoided. Immediately after they pulled the black cross, click on a smart tag-a special icon that appears in the right corner of the copied area.
If you select the “Copy Without Formatting) option, then Excel copys your formula without format and will not spoil the design.
4. Data display from the Excel table on the map
In Excel, you can quickly display your geodata on an interactive map, such as sales by cities. To do this, you need to go to the “Appendix” store on the Insert tab and install the Bing Maps Plugin from there. This can be done by direct link from the site by clicking the Get it now button.
After adding the module, it can be selected in the drop -down list “My Apps” on the “Insert” tab and place on your working sheet. It remains to allocate your cells with data and click on the Show Locations button in the card module to see our data on it. If desired in the plugin settings, you can choose the type of diagram and color for display.
5. Quick transition to the right sheet
If in the file the number of work sheets exceeded 10, then it becomes difficult to navigate them. Click with the right mouse button on any of the buttons of scrolling leaf label labels in the lower left corner of the screen. There will be a table of contents, and it will be possible to go to any desired sheet instantly.
6. Converting lines into columns and vice versa
If you ever had to shift the cells from the lines into the columns with your hands, then you will appreciate the following trick:
- Highlight the range.
- Copy it (ctrl + c) or, by clicking on the right mouse button, select “Copy” (Copy).
- Click with the right mouse button on the cell where you want to insert data, and select one of the options for a special insert in the context menu – the “Transpose” icon (Transpose). There are no such icon in old versions of Excel, but you can solve the problem using a special insert (Ctrl + Alt + V) and the choice of the “Transpose” option (Transpose).
7. False
If any cell is supposed to enter strictly defined values from the permitted set (for example, only “yes” and “no” or only from the list of departments of the company and so on), then this can be easily organized with the help of a drop-down list.
- Highlight the cell (or the range of cells) in which there should be such a restriction.
- Click the “Data Verification” button on the “Data” tab (Data → Validation).
- In the drop -down list “Type” (allow), select the “List” option (LIST).
- In the Source field, set the range containing the reference options for elements, which will subsequently fall out when entering.
8. Smart table
If you highlight the range with data and click on the “Home” tab “Format as a table” (Home → Format as Table), then our list will be converted into a smart table that can do a lot of useful things:
- Automatically stretches when adding new lines or columns to it.
- The introduced formulas automatically will be copied to the entire column.
- The hat of such a table is automatically fixed during scrolling, and the filter buttons for selection and sorting are included in it.
- On the “Design” tab (Design) to such a table, you can add a line of results with automatic calculation.
9. Sparclayans
Sparclayans are miniature diagrams drawn right in cells that clearly display the dynamics of our data. To create them, click the “Graph” button (Line) or “Histogram” in the “Sparkhalines” group on the “Insert” tab (Insert). In the window that opens, indicate the range with the initial numerical data and the cells where you want to bring sparclayans.
After clicking on the “OK” button, Microsoft Excel will create them in these cells. On the “Design” tab (Design), you can additionally configure their color, type, enable the display of the minimum and maximum values and so on.
10. Restoration of disrupted files
Imagine: you close the report with which the last half of the day was fiddled, and in the dialog box “Save changes in the file?”Suddenly, for some reason, press” no “. The office announces your heart -rending cry, but it’s too late: the last few hours of work went under the tail.
There is actually a chance to rectify the situation. If you have Excel 2010, then click on the “File” → “File → Recent) and find the“ Restore Unsaved Workbooks ”button in the lower right corner of the screen.
In Excel 2013, the path is slightly different: “File” → “Information” → “Management of Versions” → “Restore Unfinished Books” (File – Properties – Recover Unsaved Workbooks).
In subsequent versions of Excel, you should open the “File” → “Information” → “Book Management”.
A special folder from the bowels of Microsoft Office will open, where in such a case, temporary copies of all created or changed, but disrupted books are preserved.
11. Comparison of two ranges for differences and coincidences
Sometimes when working in Excel it becomes necessary to compare two lists and quickly find the elements that coincide or differ in them. Here is the fastest and most visual way to do this:
- Select both compared columns (holding the Ctrl key).
- Select on the “Home” tab → “Conditional Formatting” → “Rules for the Selection of cells” → “Repeating values” (Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values).
- Select the option “unique” in the opening list.
12. Selection (fitting) of the calculation results for the desired values
You someday selected the input values in your Excel calculation to get the desired result at the output? At such moments, you feel like a seasoned artilleryman: just a couple of dozens of iterations “short-lived-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 “Data” tab “Analysis“ What if ”and select the command“ Selection of the Parameter ”(Insert → What if Analysis → Goal Seek). In the window that appears, set the cell where you want to choose the right value, the desired result and the entrance cell that should change. After clicking on OK, Excel will perform up to 100 “shots” to choose the result you need up to 0.001.
If this review did not cover all the useful chips of MS Excel, which you know about, share them in the comments!