12 tricks for working in Excel
Excel seems incomprehensible and frightening systemPeople who are just beginning to work with him. But it is worth learning a little more about this instrument, as it becomes clear - it is incredibly convenient. These 12 techniques will help you to work much more effectively in Excel. Increase the productivity of your work and make it a real holiday - thanks to our advice it's very simple! It is useful to read even to those who are well versed in the legendary program.
Tricks for working in excel
- Add new data to the chart quickly
Select a range with new information, copy it (ctrl + c) and insert new data into the chart (ctrl + v).
- Instant filling
You have a list of names and surnames thatIt is necessary to transform into abbreviated ones: for example, Levchenko, Oxana Viktorovna - Levchenko, Fr. at. Start writing the desired text in the next column manually. After 2 lines excel will anticipate your action and change the remaining names to similarly abbreviated ones. Press enter to confirm the command.
- Copy without breaking the formats
AutoFill marker - a thin black cross inThe lower right corner of the cell - copies the contents of the cell, but does so in violation of the format. After dragging a black cross, click on the smart tag - a special icon that appears in the lower right corner of the copied area. Choose the option "fill without formatting", and the design will not be spoiled!
- Display data from the excel table on the map
Go to the "store store" on theTab "insert" (insert) and install from there the plugin bing maps. The interactive map will display the geodata you need. By clicking the add button, you can add the module by a direct link from the site.
It will appear in the "my applications" drop-down list(My apps) on the "insert" tab and will now be on your worksheet. Now select the cells with the data and click the show locations button in the map module - the data will appear directly on it.
- Go to the new sheet quickly
View all the sheets is possible, just by making one click with the right mouse button. You can still create a list of hyperlinks on a separate sheet - all at your fingertips.
- Convert rows to columns and back
Select the range you need and copy it. Right-click on the cell where you want to transfer the data. In the context menu select the icon "transpose" (transpose). Ready!
- Make a drop-down list in the cell
A drop-down list is needed when you need to enterStrictly defined values from a specific set (for example, a or c). Select a cell or range of cells in which there should be such a specific value. Click the "data validation" button on the "data-validation" tab.
In the drop-down list "type" (allow), select the option "list" (list). In the "source" field, specify a range containing the reference variants of the elements, which will subsequently drop out when entering.
- Make a "smart" table
Select the range with the data. On the "home" tab, click "format as table". The list will be converted to a "smart" table, which contains many useful functions. For example, when you add new rows or columns to it, the table will be stretched, and the entered formulas will automatically be copied to the entire column. In this table you can add a line of totals with automatic calculation in the "designer" tab.
- Create sparklines
Sparkline is a miniature chart thatDisplays the dynamics of the data entered by you. Click the "line" or "histogram" (columns) button in the sparklines group on the insert tab. In the opened window specify a range with numerical data and cells in which you want to see sparklines. After clicking the "ok" button everything will be ready!
- Restore unsaved files!
If you did not keep the job you were doingLast time, there is a chance to recover lost data. The recovery path for excel 2013: "file" - "information" - "version control" - "restore unsaved workbooks" (file - properties - recover unsaved workbooks). For excel 10: "file" - "last" (file - recent) and the button in the lower right corner of the screen "recover unsaved workbooks". A folder will be opened in which temporary information is stored.
- Compare 2 ranges
Holding down the ctrl key, highlight the ones you are interested inColumns. Select on the "home" tab - "conditional formatting" - "cell selection rules" - "home values" (home-conditional formatting - highlight cell rules - duplicate values). In the drop-down list, select the option "unique".
- Select the results for the desired values
Excel will make a quick and accurate fitResults for you! Click on the "insert" tab, "analyze what if" button and select the "insert-what if analysis-goal seek" command. A window will appear where you can specify a cell with the desired value, the desired result, and the input cell, which should change. After pressing "ok" excel instantly with an accuracy of 0.001 will produce about a hundred suitable results.
Work with pleasure, and let these advice to youHelp! Excel is one of the most popular applications in the world and many do not represent their activities without this program. Share useful information with your friends, it can come in handy!