Excel is an essential tool for controlling and managing the internal processes of companies. And while most of the people know about this program, very few know how to use Excel properly and get the most of it. Excel is beneficial and can be used for simple operations such as: B. a simple sum for a complex credit calculation. Processes that become difficult manually and are ruined with a simple mistake become simple processes.
The error rate in the processes carried out via Excel is minimal, as they can be resolved by the application itself without further complications. In addition to numerical information, you can also insert graphics in addition to the data that you can provide to Excel. There is a wide variety of graphics to choose from.
Quickly add new data to the chart
If the sheet for the created chart shows new data that needs to be added, you can select an area with new information, copy (Ctrl + C), and then paste (Ctrl + V) directly into the chart.
Let’s say you have a list of full names (Ivanov Ivan Ivanovich) that you need to convert to abbreviated names (Ivanov II). To do this, you must manually write the desired text in the adjacent column. In the second or third line, Excel predicts our actions and carries out further processing automatically. All you have to do is hit enter to confirm and all names will be converted instantly. You can also extract names from e-mails, glue names from fragments, etc.
Copy without damaging formats
You are most likely familiar with the magical autocomplete marker. This is a thin black cross in the lower right corner of the cell that you can use to copy the contents or formula of a cell to multiple cells at once. But there is an unpleasant nuance: such copying often goes against the design of the table, because it copies not only the formula, but also the format of the cells. This can be avoided. After dragging the black cross, click on the smart tag – a special icon that appears in the lower right corner of the copied area.
Excel will copy your formula without formatting and will not affect the layout if you select the Fill Without Formatting option.
View data from an Excel spreadsheet on a map
In Excel, you can quickly view your geospatial data on an interactive map, e.g. B. Sales by city. To do this, go to the Office Store under the Insert tab and install the Bing Maps plugin from there. A direct link from the site can do this by clicking the Download Now button.
After you’ve added a module, you can select it from the My Apps drop-down on the Insert tab and insert it into your worksheet. All you have to do is select your cells with data and click the Show Locations button in the map module to see our data. If desired, you can choose the chart type and colors to be displayed in the plugin settings.
Quick jump to the required sheet
When the number of worksheets in the file has exceeded 10, it becomes difficult to navigate. Right click on one of the scroll buttons in the lower left corner of the screen. The table of contents is displayed and you can immediately jump to any sheet.
Convert rows to columns and vice versa
If you’ve ever had to manually move cells from rows to columns, you’ll appreciate this trick:
- Highlight the area.
- Copy it (Ctrl + C) or right-click to select “Copy”.
- Right-click the cell where you need to paste the data and from the context menu select one of the “Paste Special” options – the “Transpose” icon. Older versions of Excel do not have such a symbol. However, you can fix the problem by using Special Paste (Ctrl + Alt + V) and selecting the Transpose option.
Drop-down list in a cell
When strictly defined values from the permitted quantity are to be entered in a cell. For example just “Yes” and “No” or just from the list of company departments and so on. Then this can be easily organized using the drop-down list.
- Select a cell (or range of cells) to which you want such a restriction to apply.
- On the Data tab (Data → Validation), click the Validation button.
- From the Type drop-down list (Allow), choose List.
- In the “Source” field, enter the area that contains the reference variants of the elements that subsequently fail when you enter them.
When you select a range of data and click Format As Table on the Home tab (Home → Format as Table), our list is converted into a smart table that can do many useful things:
- Automatically expands when new rows or columns are added.
- The formulas entered are automatically copied into the entire column.
- The header of such a table is automatically corrected when scrolling and contains filter buttons for filtering and sorting.
- On the “Design” tab that appears, you can add a totals line with automatic calculation to such a table.
Sparklines are miniature charts drawn directly into cells that illustrate the dynamics of our data. To create them, on the Insert tab, in the Sparklines group, click the Line or Columns button. The window that opens indicates the range with the original numeric data and the cells in which you want to display the sparklines.
After clicking the OK button, Microsoft Excel will create them in the specified cells. On the “Design” tab that appears, you can further customize its color and type, activate the display of minimum and maximum values, etc.
Recover unsaved files
Imagine: you close the report you worked on the last half of the day and in the “Save changes to file?” For some reason you suddenly press “no”. The office announces your heartbreaking scream, but it’s too late: the last hours of work have gone down the drain. There is an opportunity to correct the situation.
If you have Excel 2010, click File → Recently Used and find the Recover Unsaved Workbooks button in the lower right corner of the screen.
In Excel 2013, the path is slightly different: File → Info → Version Control → File – Properties – Recover Unsaved Workbooks.
In subsequent versions of Excel, open File → Info → Workbook Manager. In such a case, a specific folder from the bowels of Microsoft Office is opened, in which temporary copies of all books created or changed but not saved are stored.
Compare two areas for differences and coincidences
Sometimes when working in Excel you need to compare two lists and quickly find the same or different items. Here is the fastest, most intuitive way to do this:
- Select both columns being compared (hold down the Ctrl key).
- From the Home tab, select Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values → Conditional Formatting → Highlight Cell Rules → Duplicate Values.
- Select Unique from the drop-down list.
Selection (adaptation) of the calculation results for the required values
Have you ever adjusted the input values in your Excel calculation to get the output you wanted? At moments like this, you’ll feel like a skilled artilleryman: just a few 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 on the “What if” button on the “Data” tab and select the “Parameter selection” command (Insert → What if analysis → Target search). In the window that appears, specify the cell in which you want to select the desired value, result, and input cell to be changed. After clicking OK, Excel will take up to 100 “shots” to find the total required with an accuracy of 0.001.
If you need to copy something multiple times, you can click the little green dot that appears in a corner when it’s over a cell. You can do it to the side or down.
- If you want to copy to the page, you can also use the Ctrl + D keys.
- To include the time in a cell, just press Ctrl + Alt +:
- You can scroll from one sheet to another with Ctrl + Page Down and Ctrl + Page Up.
- The “COUNTIF” function counts how often a value is displayed in a selected area.
- The “SUMARIF” function adds the values in a range if one of these values corresponds to the selection made by us.
FAQ on using Excel
In addition to the data that you can make available to Excel, you can also insert graphics.
To do this, you must manually write the desired text in the adjacent column. All you have to do is hit enter to confirm and all names will be converted instantly.
To do this, go to the Office Store on the Insert tab and install the Bing Maps plugin from there. A direct link from the site can do this by clicking the Download Now button.
To create them, on the Insert tab, in the Sparklines group, click the Line or Columns button. The window that opens indicates the range with the original numeric data and the cells in which you want to display the sparklines.
You can click the little green dot that appears in a corner when it’s over a cell. You can do it to the side or down.
Conclusion on using Excel
How do I use Excel? You can use it to make an extensive list of things related to managing your business. It is therefore important that both you and your work team have at least a mastery of the critical functions of the program. After you are ready for a master class, check out how to calculate P value in Excel.