Microsoft Excel: Easy guide for beginners with formulas and more Microsoft Excel know-how is so expected that it hardly warrants a line on a resume anymore. But how well do you really know how to use it? Marketing is more data-driven than ever before. At any time you could be tracking growth rates, content analysis, or marketing ROI. You may know how to plug in numbers and add up cells in a column in Excel, but that's not going to get you far when it comes to metrics reporting. Do you want to understand what pivot tables are? Are you ready for your first VLOOKUP? Aspiring Excel wizard, read on or jump to the section that interests you most: Table of Contents What is Microsoft Excel? Microsoft Excel Spreadsheet Basics Keyboard Shortcuts Pivot Tables IF Functions VLOOKUP INDEX MATCH Data Visualization What is Microsoft Excel? Microsoft Excel is a popular spreadsheet software program for business. It's used for data entry and management, charts and graphs, and project management. You can format, organize, visualize, and calculate data with this tool. How to Download Microsoft Excel It's easy to download Microsoft Excel. First, check to make sure that your PC or Mac meets Microsoft's system requirements. Next, sign in and install Microsoft 365. After you sign in, follow the steps for your account and computer system to download and launch the program. For example, say you're working on...

How to Use Power Queries in Excel Though tech tools offer a wide variety of data management solutions, consolidating information from multiple sources remains a challenge. Making sense of disparate datasheets often relies on manual effort. This is where power query can help by wrangling data from various origins into an integrated view. As a marketing consultant, I work with multiple teams across a client’s business. To get a true picture of what’s going on with a SaaS company’s revenue funnel, for example, I typically need data from marketing, sales, customer success, and product. However, the data I need typically gets collected across multiple locations and in different formats. Piecing everything together means getting everything in one place, in the same format, and able to be manipulated in whichever way is needed. So, power query has been very useful to me over the years, and it’s not as complicated as you might think. With power query, you can import, clean, transform, and merge datasets from multiple sources. Once you know how to use it, gathering and interpreting data from diverse sources becomes a whole lot easier. What is a power query? How to Use Power Queries in Excel Power Query Examples What is a power query? Power query is essentially a technology you can use within Excel to connect datasets together within an Excel spreadsheet. You can use power query to pull data from a...

How to Use the SUBTRACT Function in Excel Excel is a powerful spreadsheet program from Microsoft that makes it easy to work with numbers and other values. While Excel contains a lot of power, it’s also quite useful for simple addition, subtraction, multiplication, and division. In fact, Excel’s simple formulas make it easy to add and subtract numbers and cell values without breaking out a calculator. How to Use Subtract Function in Excel Many of Excel’s most powerful capabilities come via the use of functions, which are a kind of predesigned formula. For example, the SUM function automatically sums or totals a range of cells without you having to manually add each cell to the previous one.  Unfortunately, there is no SUBTRACT function in Excel. That’s because subtracting is one of the easiest things you can do in a spreadsheet.  I‘ve found that performing subtraction in Excel is as simple as entering a simple mathematical formula. Excel does the rest.  The formula for subtracting one number from another starts with an equal sign and looks like this: =value1 – value2 It doesn’t get much easier than that. You can subtract discrete values or the values contained in individual cells.  For example, let’s say I want to put an aquarium in my home so I can enjoy tropical fish. I made a spreadsheet of the different tasks I need to complete and how much I expect each purchase to cost. I then gather the receipts to note what I...

How to Password Protect an Excel File If you’re like millions of other users, you use Excel spreadsheets to plan your budgets, track sales or expenses, and organize all sorts of data. You may even use Excel to work with private or confidential information. If you have data in a spreadsheet that you don’t want just anyone else to see, you can password protect that Excel file. I’ve found that requiring people to enter a password to access an Excel file keeps your private information private — while still allowing you, and anyone else with the password, proper access. Let’s go through how you can password protect your Excel files. How to Password Protect an Excel File In my decade-long use of Excel, I’ve used spreadsheets to house all sorts of personal and business information. If I don’t want to share that information with others, all I have to do is password protect the document — and then only share the password with those who need access. For example, I’ve worked at many businesses that use Excel to store confidential employee information — social security numbers, salaries, and the like. You don’t want random employees to access this private info, so the best approach is for the human resources (HR) department to password protect that particular file. HR can then share that password with key management, who need to see the data but use password protection to keep the data away from other employees’ prying eyes. Here’s how...

How to Use the COUNTIF Function in Excel Excel can do more than just simple math. That's thanks to its bevy of built-in functions and min-formulas that simplify the creation of more complex formulas. In my decade-long experience with Excel, I’ve found that one of the more useful functions is the COUNTIF function. You can use COUNTIF to count the number of cells that contain a specific value or range of values. It’s easier to use COUNTIF than to manually count yourself. How to Use the COUNTIF Function in Excel The COUNTIF function in Excel counts the number of cells in a range that meet the given criteria. It doesn’t total the cells; it simply counts them. I’ve found it useful for counting cells that contain a specific value or range of values. For example, let’s say you have a spreadsheet that contains customer contact information, including street addresses and ZIP codes. You can easily use the COUNTIF function to count how many customers live in a given ZIP code — and you don’t even have to sort the addresses by ZIP code to do it. Let’s work through the process step-by-step. 1. =COUNTIF() Begin by entering the following into the cell where you want to place the answer: =COUNTIF() For this example, we’ll use a grocery list that I’ve written. The different items I want to buy are sorted by type, like vegetables and fruit. 2. Define a range of cells. For the COUNTIF function to...

How to Use Excel Like a Pro: 29 Easy Excel Tips, Tricks, & Shortcuts Sometimes, Excel seems too good to be true. All I have to do is enter a formula, and pretty much anything I'd ever need to do manually can be done automatically. Need to merge two sheets with similar data? Excel can do it. Need to do simple math? Excel can do it. Need to combine information in multiple cells? Excel can do it. In this post, I’ll go over the best tips, tricks, and shortcuts you can use right now to take your Excel game to the next level. No advanced Excel knowledge required. What is Excel? Microsoft Excel is powerful data visualization and analysis software, which uses spreadsheets to store, organize, and track data sets with formulas and functions. Excel is used by marketers, accountants, data analysts, and other professionals. It's part of the Microsoft Office suite of products. Alternatives include Google Sheets and Numbers. Find more Excel alternatives here. Excel is primarily used for creating financial documents because of its strong computational powers. You’ll often find the software in accounting offices and teams because it allows accountants to automatically see sums, averages, and totals. With Excel, they can easily make sense of their business’ data. While Excel is primarily known as an accounting tool, professionals in any field can use its features and formulas — especially marketers — because it can be used for tracking any type of data. It...

How to Create a Heat Map in Excel Heat maps in Excel help businesses to visualize large data sets, transforming a pool of numbers into easily interpretable graphs. For example, a retailer can use a heat map to analyze sales data and find products that sell more during specific seasons. In short, heat maps help you find and color-code correlations that may have been difficult to discern from raw data. This guide will show you how to create heat maps in Excel of different types: A simple heat map with conditional formatting. A heat map with a custom color scale. A geographic heat map. Let’s get started. What is a heat map in Excel? A heat map in Excel is a color-coded snapshot of your data, which helps you analyze thousands of data points and spot negative and positive trends and correlations at a glance. Lighter colors represent lower values. Darker shades stand for higher ones. Though, you can invert them. For example, you can depict higher conversion rates in green and lower in red. What falls in between will be colored in orange and a gradient with different shades of the three colors based on the value. Or you can fill cells in gradient shades, as shown in the example below. The Benefits of Using Heat Maps. Let’s go over four core benefits of Excel heat maps tailored to business needs. 1. Quick Data Interpretation One of the primary benefits of using...

How to Create Gantt Charts in Excel Microsoft Excel remains the go-to option for many businesses to perform data calculations and create charts based on the results. The Gantt chart is a useful guide that isn’t naturally available in Excel. Great for project management and milestone tracking, Gantt charts can help companies better visualize operations and streamline current processes. In this piece, we’ll dive into the basics of Gantt charts and explore their benefits, then provide a step-by-step guide to creating them in Excel, along with some useful examples. Before long, you’ll be able to use Excel like a pro. What are Gantt charts? The Benefits of Gantt Charts How to Create Gantt Charts in Excel Gantt Chart Examples What are Gantt charts? On the left-hand side, the Y-axis of the chart lists specific activities. At the top, the X-axis of the chart shows time. There’s no fixed unit for time — it could be measured in weeks, days, hours, or minutes, depending on the tasks you’re tracking. In Excel, each task gets its own row. As you can see in the example above from Microsoft, task 1 starts at time unit 0 and runs until just before task 2, which proceeds until task 3 begins. It’s worth noting that tasks can overlap — the purpose of the Gantt chart isn’t to determine the order of tasks but to provide an easy way to see what’s happening, when, and how many processes are happening at once. ...

How to Enable Macros in Excel Microsoft Excel’s versatility and sheer number of features make it one of the most complex software programs to master. While you might want to learn every feature in Excel, you’ll likely need to know just a limited number to get the best from the software. Macros are small and simple programs that can make your work much easier and are favored by expert users. The good thing is that learning how to enable macros is relatively easy, and in this article, we’ll show you how to enable macros in Excel, since they are disabled by default. What are macros in Excel? The Benefits of Using Macros in Excel The Dangers of Using Macros in Excel How to Enable Macros in Excel How to Enable a Single Macro in Excel How to Enable Macros in a Single Excel File What are macros in Excel? An Excel macro is a small program created in Excel that allows you to automate everyday tasks. It’s a precious tool in Excel that lets complicated instructions be carried out with just a command. Macros are tremendous time-savers — you can create macros that can do almost anything you want in Excel and other Microsoft Office software. For example, macros can let you insert company letterhead into an existing document in Word or add formatted tables to a spreadsheet in Excel — all with one command. When you create a macro, you’re recording your mouse clicks and keystrokes....

43 Handy Excel Shortcuts You Can't Live Without Many marketers use Microsoft Excel every day, whether it's to create a chart, analyze data, or run a report to present at the next team meeting. Creating reports like these in Excel is time-consuming enough. How can we spend a little less time navigating, formatting, selecting, and entering formulas for our data? Wouldn't it be great if there were keyboard shortcuts that could help us get our work done faster? So glad you asked. We’ve put together a list of 43 keyboard shortcuts for Microsoft Excel. Although you can do all of these maneuvers manually, knowing these tricks will help save you time so you can focus on the stuff that really matters. All of these shortcuts can be accessed on PC and Mac, so we've included both types below where applicable. (Note for Mac users: To access the F keys (F1–F12), you'll have to hold the Fn key before pressing any F key -- unless you've enabled those keys as standard function keys.) 43 Excel Shortcuts You'll Definitely Want to Bookmark Navigation Shortcuts These simple shortcuts can help you navigate between workbooks, sheets, rows and columns: Move up through a selection Shift + Enter (PC and Mac) Jump to the top of a column CTRL + ↑ (PC); Command + ↑ (Mac) ...