Excel is an essential tool to control and manage the internal operations of companies. And although most people know about this program, very few know how to use Excel correctly and make the most of it. Excel is beneficial, and it can be used for simple operations, such as a simple sum to a complex loan calculation. Processes that manually become difficult and that with a simple mistake are ruined, they become simple processes.
The margin of error in the processes carried out through Excel is minimal because the application itself allows them to be solved without further complication. In addition to numerical information, you can also include graphics apart from the data you can provide to Excel. There is a wide variety of graphics to choose from.
Quickly Add New Data to the Chart
If new data appears on the sheet for the constructed chart, which needs to be added, then you can select a range with new information, copy it (Ctrl + C), and then paste it directly into the chart (Ctrl + V).
Assume you have a list of full names (Ivanov Ivan Ivanovich), which you need to turn into abbreviated names (Ivanov I.I.). To do this, you need to start writing the desired text in the adjacent column manually. On the second or third line, Excel will 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. Similarly, you can extract names from email, glue names from fragments, and so on.
Copy Without Breaking Formats
You are most likely aware of the magic autocomplete marker. This is a thin black cross in the lower right corner of the cell, pulling on which you can copy a cell’s contents or formula to several cells at once. However, there is one unpleasant nuance: such copying often violates the table’s design, since not only the formula is copied, but also the cell format. This can be avoided. After pulling 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 not spoil the layout if you select the Fill Without Formatting option.
Displaying Data From an Excel Table on a Map
In Excel, you can quickly display your geodata on an interactive map, such as 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 Get It Now 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 choose your cells with data and click on the Show Locations button in the map module to see our data. If desired, you can select the type of chart and colors to display in the plugin settings.
Quick Jump To The Required Sheet
If the number of worksheets in the file has exceeded 10, it becomes difficult to navigate them. Right-click on any of the sheet tabs scroll buttons in the lower-left corner of the screen. The table of contents will appear, and you can jump to any desired sheet instantly.
Convert Rows to Columns and Vice Versa
If you’ve ever had to move cells from rows to columns by hand, 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).
- Right-click on the cell where you must paste the data and choose from the context menu one of the Paste Special options – the Transpose icon. Older versions of Excel do not have such an icon, but you can fix the problem by using Paste Special (Ctrl + Alt + V) and choosing the Transpose option.
Drop-Down List In a Cell
If it is supposed to enter strictly defined values from the allowed set into any cell. For example, only “yes” and “no” or only from the list of company departments, and so on. Then this can be easily organized using the drop-down list.
- Select a cell (or a range of cells) that should have such a limitation.
- Click the “Validation” button on the “Data” tab (Data → Validation).
- In the drop-down list “Type” (Allow), select the option “List” (List).
- In the “Source” field, specify the range containing the elements’ reference variants, which will subsequently drop out when typing.
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 converted into a smart table that can do a lot of useful things:
- Automatically stretches when new rows or columns are added to it.
- The entered formulas will be automatically copied to the entire column.
- Such a table’s header is automatically fixed when scrolling, and it includes filter buttons for filtering and sorting.
- On the “Design” tab that appears, you can add a totals row with automatic calculation to such a table.
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. The window that opens will 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, and so on.
Recover Unsaved Files
Imagine: you close the report that you were messing with for the last half of the day, and in the dialog box that appears “Save changes to file?” suddenly, for some reason, you press “No.” The office announces your heart-rending scream, but it’s too late: the last few hours of work went down the drain. There is a chance to rectify the situation.
If you have Excel 2010, then click on “File” → “Recent” (File → Recent) and find in the lower right corner of the screen the button “Recover Unsaved Workbooks.”
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 Management. A particular 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
Sometimes, when working in Excel, you need to compare two lists and quickly find the same or different items. Here’s the quickest and most intuitive way to do it:
- Select both compared columns (hold down the Ctrl key).
- Pick Home → Conditional formatting → Highlight Cell Rules → Duplicate Values on the Home tab → Conditional Formatting → Highlight Cell Rules → Duplicate Values.
- Select Unique from the drop-down list.
Selection (Adjustment) of the 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 seasoned artilleryman: 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 “Data” tab and select the “Parameter selection” command (Insert → What If Analysis → Goal Seek). In the window that appears, specify the cell where you want to choose the desired value, the desired result, and the input cell to be changed. After clicking on “OK,” Excel will execute up to 100 “shots” to find the required total with an accuracy of 0.001.
When you need to copy something many times, then you can click on the little green dot that appears in a corner when it is over a cell; you can do it to the side or down.
- If you want to copy to the side, 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 using Ctrl + Page Down and Ctrl + Page Up.
- The “COUNTIF” function will count the number of times a value appears in a selected range.
- The “SUMARIF” function adds the values in a range when one of these corresponds to the selection we have made.
FAQ on How to Use Excel
You can also include graphics apart from the data you can provide to Excel.
To do this, you need to start writing the desired text in the adjacent column manually. All you have to do is press the Enter key to confirm, and all names will be converted instantly.
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 Get It Now button.
To create them, click the Line or Columns button in the Sparklines group on the Insert tab. The window that opens will specify the range with the original numerical data and the cells where you want to display the sparklines.
You can click on the little green dot that appears in a corner when it is over a cell; you can do it to the side or down.
Conclusion on How to Use Excel
How to use Excel? You can use it to carry out an extensive list of things regarding your company’s management, so it is essential that both you and the people in your work team can master at least the program’s crucial functions. Now that you are ready for a masterclass, check out how to calculate the P-value in Excel.