Excel is a proprietary Microsoft application that allows you to manage, organize, analyze and format data in spreadsheets. Whether you are a regular user, city budget manager, or small business owner, the program is bound to make life easier. The main thing is to learn how to use it.
Hot keys to speed up work
Keyboard shortcuts are a useful tool that provides quick access to various Excel features. When working with a large volume of numbers and spreadsheets, these combinations can save you a lot of time and headaches.
- Ctrl + N – creating a new document;
- Ctrl + S – saving the document;
- Ctrl + O – opening a document;
- Ctrl + W – closing the document;
- ALT + F1 – creating a diagram;
- Shift + F3 – function insertion;
- Ctrl + Shift + $ – the use of the monetary format;
- Ctrl + Shift +% – the use of percentage format;
- Ctrl + Shift + & – applying the boundary of the contour;
- Alt + H + D + C – deleting a column;
- Alt + H + B – adding borders to a cell.
Of course, these are not all combinations that can be used when working with spreadsheets. Microsoft has a complete list at the link.
Using “Conditional Formatting”
Conditional Formatting is one of the most important features of Excel. This tool provides quick graphical clues to identify specific cells. With the help of this feature, you can select an area with certain values, find critical errors, visualize the necessary information, and much more. For example, “Conditional Formatting” will help an entrepreneur with just a few clicks to identify the percentage of the most effective salespeople or track the dynamics of sales. It turns out that the simplest color change can be of great benefit. To customize this feature, you need to go to Home> Conditional Formatting> Data Bars and then select the set of colors you want to fill.
Learn more about conditional formatting by link.
VLOOKUP for matching data in tables
VLOOKUP is a tool for finding any data within a specific column of a table. It looks for information in the first column of a specific sheet and returns the corresponding value in the same row from another column. For example, a user can find out the cost of a product by searching for the model name. Although VLOOKUP is limited to a vertical orientation, it is an important tool that makes it easier to perform other Excel tasks, such as calculating the standard deviation and weighted averages. To work with this tool, you need to build a VLOOKUP formula that includes the lookup value, the range of the lookup value, the column number, and the words TRUE (if an approximate match is appropriate) or FALSE (if an exact match is required).
Learn more about this tool by link.
Using the Power View Tool
Power View is a data exploration and visualization tool that can quickly compare large amounts of information and create interactive presentation reports from tables, maps, graphs, matrices, bar charts, charts, and more. This feature first appeared in Microsoft Excel 2013. Power View can be found under Insert> Reports. That being said, it’s important to understand that a Power View sheet includes several components: the Power View canvas, filters, a list of fields, and tabs on the ribbon.
Learn more about this tool by link.
Using Pivot Tables
Pivot Tables is a tool that allows you to summarize, sort, and count large amounts of information in lists and tables. This feature makes it easy to analyze data based on specific landmarks. PivotTables are the perfect solution for teachers. For example, if an educator has a full set of grades for an entire year, this tool can narrow the circle down to one student for one month. To create a pivot table, you need to select Pivot Table on the Insert tab. Better yet, select the Recommended PivotTables option so that Excel picks the correct type automatically. You can also try PivotChart, which creates a table with a graph included for ease of understanding.
Learn more about this tool by link.
Using Cell Guard
When you need to share information with other users, it is important to prevent accidental editing. It is best to prohibit changing values. First, you need to protect the sheet itself. To do this, select the “Protect Sheet” option in the “Format” menu. After that, you should select the type of change, enter the password and confirm your action. You can also select the rows or columns you want and lock them by clicking Lock Cell from the Format menu. This tool is handy for economists who create reports and send them for review by the workers. With the help of Cell Protection, information will not be accidentally distorted or lost, which will undoubtedly have a positive effect on the productivity of the entire department.
Learn more about protecting cells by link.
Freeze row and column headers
Pinning row and column headings is one of the simplest Excel tricks to keep key information in plain sight when scrolling. To use this tool, you first need to select the cell that you want to freeze – then, on the “View” tab, click “Freeze Areas.”
Learn more about this tool by link.
Using Paste Special to Transpose
Inattentive users can enter data into columns when it was necessary to provide information in the form of rows. However, this does not mean that all the work will go down the drain – there is a simple way out of such a situation. First, you need to copy the source block of cells, then right-click the target cell and choose Paste Special> Transpose. Columns and rows are swapped.
Learn more about this tool by link.
Using Flash Fill
Flash Fill is a tool that fills a column based on the data template in the base cell. For example, if the first column contains first names and the second contains last names, you must enter the full first and last name in the top cell for the third column to connect this data. The Flash Fill function then uses this template and fills in the rest of the cells. If this process does not occur, then, most likely, this option is not enabled. To activate Instant Fill, go to Tools> Options> Advanced> Editing Options> Automatically Flash Fill.
Learn more about this tool by link.
Using the Watchpoint Window
When editing a large worksheet, you often want to see how changes in one area affect totals and other calculations in off-page cells. Instead of repeatedly moving back and forth across the screen, it is sufficient to use the watchpoint window to display cells in another part of the sheet. To configure this tool, you need to left-click within one area that you want to track. Then select “Formulas” and “Control value window” on the toolbar. And since the cell is already selected, all that remains is to confirm that the link to it in the dialog box is correct.
Read more about the control value window at the link.