Excel For Dummies

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 be a project at work or just a personal budget, you need to know basic Excel to get things done right and done quickly. To help you get the most out of Excel, we’ve put together a batch of the best Excel tips for beginners.

• Excel For Dummies Free Download
• Excel For Dummies Youtube
• Excel For Dummies Free Download

Download your free Excel practice workbook

Section: Excel Basics Tutorial: Excel Made Easy Excel Made Easy - A Beginner's Guide. In this tutorial, you'll learn about workbooks and the different parts of an Excel worksheet (spreadsheet), such as rows, columns, and cells. We'll discuss how to insert rows and columns,. Excel comes with so many powerful data-analysis tools and features that you might be wondering why you need to learn yet another: the PivotTable. The short answer is that the PivotTable is a useful weapon to add to your data-analysis arsenal. Dummies has always stood for taking on complex concepts and making them easy to understand.

Follow along with this sample workbook.

1. Adding frequent actions to 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. 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 a lot of data.

Before:

After:

Once you have your favorites added, you can also rearrange your QAT shortcuts by right-clicking on it and then clicking “Customize Quick Access Toolbar...” in the menu. With your customized QAT you’ll be blazing through your next spreadsheet with ease.

2. Filtering data

Speaking of a lot of data, Microsoft has amazing tools to handle it all, which probably explains why it became the most important workplace computer program around the world. Excel’s basic function for handling all this information is the Sort & Filter function. This is so useful because it allows you to rearrange and summarize data in a way that’s actually useful.

To do this, highlight all the data you want 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 drop-down 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 certain time frame. Just filter on the date column and select your desired time frame.

From our example, you can quickly see there were only two orders in September 2014.

Excel For Dummies Free Download

3. Adding dynamic header/footers

While it seems everything is going paperless, sometimes there’s still a need to print. One of the best ways to keep track of what's printed out of Excel is by adding page numbers, timestamps and file paths to the header or footer. To make sure you don’t have to update these values every time you need to print a spreadsheet, you can add formulas that update automatically.

First, change your view of Excel so you can see the header and footer.

Next, add the following text to your header footer:

Before:

After:

4. Setting print areas on sheets

Now that you know how to automatically update what prints in the margin, another time-saving trick is set up print areas for your tabs to update what prints within the margins. If you like to keep all your work on one spreadsheet but only need to print out a certain portion, setting the print area is a great way to save time when you hit Ctrl + P.

To set your print area, highlight the cells you want printed. On the Page Layout ribbon, click the drop-down under Print Area and select Set Print Area.

In the same drop-down menu, there’s the option to clear the print area. This is useful if you’ve changed your spreadsheet and want to add more to printable area.

5. Paste Special settings

There are so many different ways to accomplish tasks in Excel. 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. Say you want to copy and paste something but not everything like a 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? Formulas allow you to do just that.
• Formats - Use this option to duplicate formats while leaving existing values and formulas.
• Column Widths - For when all your columns need to be a uniform width, this option saves a ton of time instead of adjusting them manually.

6. Group/ungroup columns to hide detail 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 that have column headings, no blank rows or columns, and data is 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 in which you can select how the data should be grouped and summarized. In the example below, we grouped by the change in OrderYear and summed on Total. This will show us total sales for each year and in total.

Here is the result:

Now with just a few clicks, you can go from meaningful summaries to fine detail on the same spreadsheet.

7. Protect sheets and workbook

When it comes time to send your Excel spreadsheet, it's important to protect the data that you're sharing. 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 protected, click on Protect Workbook follow the same procedure.

9. 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 are dependent upon others with Trace Precedents, and which cells contribute to others using Trace Dependents.

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 arrow with the blue dot being the precedent and the arrow being the dependent.

Below, Trace Dependents for cell E2 shows it only flows to cell G2.

And Trace Precedents for cell I4 shows 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.

9. Data validation for drop-down menus in cells

Using the drop-down 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 properly.

This tool is used to enter data in a spreadsheet from a predefined list of criteria. The main purpose of using drop-down 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 at validating inputs. First Select Data on the Ribbon and the Data Validation.

Next, select your parameters. Below we used the months of the year to populate OrderMonth.

Once you click Ok, click on the drop-down arrow next to the cell and select from the list.

Note: now that one cell is set up, it can be copy and pasted in the remaining cells below.

10. Text-to-column - splitting data in 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 there are some complex formulas that will help you split your text into new columns, it can consume a lot of valuable time. The speedy way to split this up is Text to Columns which splits all the selected cells at exactly 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 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.

11. Creating simple graphs

One of the best ways to improve your Excel skills is being able 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. 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.

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 meeting.

Bonus: Check out this lesson on how to create column charts in Excel:

Conclusion

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.

For more helpful Excel tips for beginners, check out our articles on the best Excel hacks, Excel essentials, and the best Excel shortcuts. You can even take our free crash course to quickly cover some Excel basics.

Want to improve your Excel skills even more? Don’t be the last person in your office to learn Excel - sign up for the comprehensive GoSkills Basic Excel course today!

Ready to start learning? Become an Excel ninja today with GoSkills

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

I can’t tell you how much time I’ve spent (ahem, wasted) doing the very same thing over and over and over again in Excel. Formatting my workbook, plugging in formulas, formatting cells, and highlighting rows. The list goes on and on.

But, what if I told you that there was a way you could automate that all? What if—with just the click of a button—you could easily complete those routine tasks? Well, this is another area where Excel really shines. It’s time to meet your new best friend: macros.

Let’s jump in to our Excel macro tutorial for beginners.

Step up your Excel game

Download our print-ready shortcut cheatsheet for Excel.

What is a macro?

A macro is a piece of code within Excel that you can use to automate many routine tasks—just like the ones I mentioned above.

You may have heard the terms 'macro' and 'VBA' used interchangeably, but there is a distinction. Put simply, VBA is the programming language used to write macros.

Check out this explanatory video for more background on the differences between macros and VBA from the GoSkills Excel Macros & VBA course.

When you have some experience under your belt, you can use VBA to create more complex macros as shown in this tutorial on advanced filtering.

Before you get intimidated and write macros off as yet another Excel feature that’s far too complex and advanced for you, take a breath.

The concept of a macro might seem complicated. But, learning to use them to your advantage is actually surprisingly simple.

Where to access macros

To get started, the first step in your macro Excel journey is to ensure that you have access to the various buttons and functions you’ll need to build your macro.

Those are all found within Excel’s “Developer” tab. However, by default, Excel hides that tab. If you aren’t currently seeing it, you’ll need to take a few steps to have that tab as part of your toolbar. How you’ll do that will depend on whether you’re using a PC or a Mac.

If you’re using a PC, visit “File” and then select “Options.” Click “Customize Ribbon” within the menu, and then check the box for the “Developer” tab. Hit “OK” and the “Developer” tab will appear in your ribbon.

If you’re using a Mac, click “Excel” in the upper left corner of your screen (next to the Apple logo) and then “Preferences.” From there, hit “View” and then check the box for the “Developer” tab.

How to build a macro: A case study

Meet Sophia. Sophia works as an event coordinator at a large tech company, and she’s currently working on putting together an annual luncheon for the company’s vendors, investors, board members, and other stakeholders.

Sophia used Excel to organize and track her entire invite list—and, right now, she has about 400 different rows of data that list the attendee’s name, role, table number, and his or her dining choice.

In order to recognize the company’s board members, Sophia has requested that the caterer (who will use Sophia’s Excel spreadsheet when setting up the event) puts something special at the place setting of each and every board member.

To distinguish who on that massive Excel spreadsheet is a board member (without screwing up her order and table numbers!), Sophia and the caterer agreed that she should do the following to obviously note who at each table is a board member (so it isn’t missed by the setup crew):

• Bold the board member’s name
• Change the text of the board member’s name to blue
• Highlight the cell containing the board member’s name in yellow

Sophia’s happy to do so. But, she’s realizing that it’s taking her forever to find the board member at each table and then go through those three different formatting steps for each and every board member cell.

With a little Excel knowledge under her belt, she decides to build a simple macro—so, when she finds a cell for a board member, she can apply those three formatting rules with just a single click of a button.

Let’s build this macro right along with Sophia.

Excel For Dummies Youtube

1. Setup your macro

To start, click in a cell that does not belong to a board member, go to the “Developer” tab, and then click “Record Macro.”

At that point, you’ll see a window that gives you the option to name your macro (important note: Excel will not allow you to include any spaces in your macro name). Click to store the macro in that workbook (since that’s where Sophia is working with the data).

You can also enter a keyboard shortcut to apply that macro (in Sophia’s case she’ll use option+command+b for board member) and a description (which is helpful if you record and store a lot of macros).

When you’re done entering the necessary information, hit “OK.” At that point, you’ll see “Stop Recording” in the “Developer” tab. This means that you’re actively recording your next steps.

2. Record your macro

Recording a macro is exactly what it sounds like—Sophia will use Excel to record the steps she’s taking to format one cell. When those steps are recorded and the macro is built, she’ll then be able to format a board member cell totally painlessly.

Now that we’ve set up our macro and are actively recording, Sophia will select the name of a board member and then apply her three formatting rules: bolding the text, changing the font color, and highlighting the cell.

When she’s done, she’ll hit her “Enter” key and then head back to the “Developer” tab to hit “Stop Recording.”

3. Edit your macro

Here’s an important thing you need to remember: Going through those steps means that Sophia has just recorded her macro. However, Excel thinks it only needs to apply those rules to that one cell.

In order to use that macro other places, she’ll need to adjust the range of the macro. Fortunately, that’s easily done.

To do so, click “Macros” within the “Developer” tab. Within that macros menu, select “Step” (which will be referred to as “Step Into” on PCs).

After doing so, you’ll be met with a rather intimidating window, which displays the VBA (that stands for Visual Basic for Applications and is Excel’s programming language) you just recorded.

Within that window, you’ll see two different lines that begin with the word “Range.” Those lines are telling Excel that you only want those formatting rules applied to cells within that range—which isn’t what Sophia wants, since she’s planning to use this macro for other cells.

So, it’s as simple as just deleting those two lines from that code and then closing out of that window.

4. Use your macro

With that range deleted, Sophia is now able to apply that macro to other cells.

In order to do so, she will just select the next name of a board member and use the keyboard shortcut she created (remember, that was option+command+b) to easily apply all three of those formatting rules to those cells.

What if she didn’t create a keyboard shortcut? She could also click the desired cell, select “Macros” on the “Developer” tab, and then hit run.

She (and you!) did it—we created a simple macro that Sophia can use to quickly format her board member cells.

However, there’s one more important thing to note: When saving the workbook, Sophia would need to make sure to save it as a .xlsm file.

Put simply, you need to tell Excel that you want your workbook to host a macro, and using that file format ensures that your workbook is macro-enabled.

Ready to become a macro master?

There you have it—a basic macro to remove some of the drudgery of the repetitive task of formatting cells.

There’s so much more that Excel macros are capable of and we’ve barely scratched the surface here. For example, we could’ve added to this simple macro we built in order to have it automatically find all board members and apply those formatting rules—removing even more of the manual effort required.

Does that pique your interest? You’re in luck. Our beginner’s course on Excel macros will walk you through everything else you need to know to become a macro master and truly use that handy feature to your advantage.

Ready to become a certified Excel ninja?

Take GoSkills' Macros & VBA course today!

Excel For Dummies Free Download