Microsoft Excel is an integral business tool. Some people relish the capabilities of Excel, finding it to be a helpful tool that allows them to manage reports on and illustrate tables of data efficiently. Others, however, find it tedious and cannot discern what Excel can do for them aside from keeping things in neat columns and rows. Nevertheless, whether you’re analyzing vast amounts of data or trouncing timesheet templates, Microsoft Excel management can provide the technological advantage you need to get the job done.
Like you may already know, most people try to limit their Excel exposure to the bare minimum (and even actively avoid it like that one weird relative we all have). But whether it is a project at work or just a personal budget, you need to know basic Excel to get things done right and done quickly. So to help you get the most out of Excel, we’ve put together a batch of the best Excel tips for beginners.
Adding frequent actions to the quick access toolbar
Looking around any version of Excel, you’ll notice there’s an endless array of tools at your fingertips. But most beginners use just a few of these repetitively. So instead of switching between the different tabs of the Ribbon every time, you can add your personal favorites to what’s known as the Quick Access Toolbar.
Microsoft offers several ways to make this happen, but the easiest way is to right-click on what you want to add and select “Add to Quick Access Toolbar.” One of my personal favorites in my QAT is the Sort & Filter function, as I usually work with many data.
Once you have your favorites added, you can rearrange your QAT shortcuts by right-clicking on them and then clicking “Customize Quick Access Toolbar…” in the menu. With your customized QAT, you’ll be blazing through your following spreadsheet with ease.
When you’re looking at massive data sets, you don’t usually need to look at every single row simultaneously. Sometimes, you only want to look at data that fit into specific criteria.
That’s where filters come in.
Speaking of a lot of data, Microsoft has terrific tools to handle it all, which probably explains why it became the most critical workplace computer program worldwide. Excel’s primary function for taking all this information is the Sort & Filter function. This is so useful because it allows you to rearrange and summarize data in a helpful way.
To do this, highlight all the data you want to be included in the filter (a quick way to do this is Ctrl + A). Then, click the funnel icon in the top left of the Home ribbon.
Now there will be a dropdown arrow at the top of each column in the filter. Here, you can select different values in the table and sort them. For example, let’s say you wanted to see how many orders were made in a specific time frame. Just filter on the date column and select your desired time frame.
Use conditional formatting to make cells automatically change color based on data.
Conditional formatting allows you to change a cell’s color based on the information within the cell. So, if you want to flag specific numbers above average or in the top 10% of the data in your spreadsheet, you can do that. Likewise, if you’re going to color code commonalities between different rows in Excel, you can do that. This will help you quickly see information that is important to you.
To get started, highlight the group of cells you want to use conditional formatting on. Then, choose “Conditional Formatting” from the Home menu and select your logic from the dropdown. (You can also create your own rule if you want something different.) A window will pop up that prompts you to provide more information about your formatting rule. Select “OK” when you’re done, and you should see your results automatically appear.
Paste Special settings
There are so many different ways to accomplish tasks in Excel. For example, consider the simple copy and paste functions. As if Ctrl + C and Ctrl + V didn’t make copying easy enough, Microsoft built Paste Special.
Here’s how it works. For example, say you want to copy and paste something but not everything, like just a number or format. Instead of copying everything and removing what you don’t want, you can use Paste Special. To do this, copy your information as usual, but instead of using Ctrl + V, right-click and select Paste Special from the menu.
Some of the more popular Paste Special options include:
Values – use this option if you only want to paste the text you see in the cells. It will leave all formatting unchanged.
Formulas – Need to keep that formula but don’t need the formatting? Procedures allow you to do just that.
Formats – Use this option to duplicate designs while leaving existing values and formulas.
Column Widths – When all your columns need to be a uniform width, this option saves a ton of time instead of manually adjusting them.
Group/ungroup columns to hide detailed data.
Spreadsheets with a bunch of complicated and detailed information can be hard to read and analyze. Luckily, Excel provides an easy way to collapse and expand the messy details to create a more compact and legible view.
Grouping in Excel works best for structured worksheets with column headings, no blank rows or columns, and data sorted by at least one column.
Select all the data you want to summarize in the group. Then, go to the Data tab > Subtotal. This will bring up a pop-up where you can select how the data should be grouped and summarized. In the example below, we grouped by the change in Order Year and summed on Total. This will show us total sales for each year and in Total.
With just a few clicks, you can go from meaningful summaries to fine detail on the same spreadsheet.
Protect sheets and workbook
When it takes time to send your Excel spreadsheet, it’s essential to protect the data that you’re sharing. Of course, you might want to share your data, but that doesn’t mean it should be changed by someone else. Luckily, Excel has built-in features to protect your spreadsheets.
To protect a sheet, click on the Review tab in the Ribbon, then Protect Sheet. This will bring up a pop-up box where you’ll add the unlock password and what functions users can still perform while the Sheet is protected.
Once you click Ok, you are prompted to confirm the password and save the workbook. Now, if anyone tries to alter the information, they will need that password. If you have a bunch of sheets you want to be protected, click on Protect Workbook follow the same procedure.
Trace precedent/dependent formulas
Have you ever used a worksheet that someone else set up? If you need to update the formulas and functions but do not know what other calculations will be affected you can spend a lot of time clicking around in the workbook with nothing to show for. Or you’re looking for a miscalculation and need to see where the data is flowing from.
Excel offers a simple way to review which cells depend on others with Trace Precedents and which cells contribute to others using Trace Dependents.
Also Read: 10 Best Franchises You Can Buy From
Both functions only work on the active cell selected, so, unfortunately, it’s one cell at a time. Click the Trace Precedents or Trace Dependents button in the Formula Auditing group of the Formulas tab to generate the blue arrows. Data flow follows the direction of the needle, with the blue dot being the precedent and the hand being the dependent.
Below, Trace Dependents for cell E2 shows it only flows to cell G2.
And Trace Precedents for cell I4 show cells E2 and F2 are the only cells flowing to it.
These functions work across tabs of the same workbook and different workbooks with one exception. Trace Dependents won’t work for external links for workbooks unless they are open.
Data validation for dropdown menus in cells
Using the dropdown list is a great way to impress your co-workers and boss with your Excel skills. At the same time, it’s a very user-friendly way to make sure custom Excel sheets function correctly.
This tool is used to enter data in a spreadsheet from a predefined list of criteria. The primary purpose of using dropdown lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and makes data input faster.
This also gives you the possibility of controlling exactly what can be entered into a cell. This makes it perfect for validating inputs—First Select Data on the Ribbon and the Data Validation.
Next, select your parameters. For example, below, we used the months of the year to populate Order Month.
Once you click Ok, click on the dropdown arrow next to the cell and select from the list.
Note: now that one cell is set up, it can be copied and pasted in the remaining cells below.
Text-to-column – splitting data from one cell to multiple cells.
Have you seen data in excel that you need, but there’s other information in those cells that won’t work with your formulas? While some complex recipes will help you split your text into new columns, they can consume a lot of valuable time. The speedy way to split this up is Text to Columns which breaks all the selected cells at precisely the same point and puts the results in separate columns.
You can use Text to Columns in two different modes: fixed-width and delimited. Fixed-width is useful if you can separate your data using a straight line through all the rows while delimited splits the text-based upon the text such as every comma, tab, or space.
For example, let’s look at a delimited Text to Columns to remove cents from our total column.
Like most functions on the Data Ribbon, first, highlight your data and then click Text to Columns. Once here, you’ll choose between fixed-width or delimited.
On the next screen, set your splitting criteria. In our example, we used the period.
On the final screen, you have the option to exclude sections and change the formatting. This will save you crucial steps later. Then click Finish.
And finally, the results! All of the cents were placed in the column just to the right.
Creating simple graphs
One of the best ways to improve your Excel skills is to communicate your results to others. Graphics, images, and charts are great ways to visualize and represent your data to end-users, and Excel does a great job of automating this process. Let’s take a look at how to set up a simple chart using our sample sales data.
A simple graph starts with two sets of data, your independent (date) and dependent (results) variables. For example, below, we have selected OrderDate and Total.
Clicking on Insert in the Ribbon, you’ll see all the graphing options. For this example, a simple line graph will show us the total sales for each date.
Also Read: Franchise Opportunities How To Locate Them
Excel makes graphing so simple the results even include the title! So the next time you’re called into a last-minute meeting, you can create a simple graph on the fly in the forum.
Now that you’ve learned more about Excel, it’s time to put that knowledge into action. These helpful tricks will make your work so much faster. You’ll wish you knew about them so much sooner.