Here’s the drill… You’ve got a set of data dumped from some performance analytics database… It has a basic date series with some kind of output, and you need to put it into a chart that your executives can understand at a glance. Excel has an extremely powerful charting system, but it is not at all obvious or easy to use, particularly when you have a non-standard set of data. Odds are, your data is not set up the way Excel wants it to be, so you’ll have to do some leg work. Let’s walk through an example of data transformation and Excel charting… Continue reading Building Charts with Multiple Series and Custom X-Axis
How to Automatically Fill Out Forms using Data Validation and VLOOKUP
The business world is full of forms and paperwork built out of Excel spreadsheets. Even though the program isn’t designed for the task, the benefit of being able to calculate sums, totals, etc. in the form makes it the de facto standard for invoices, receipts, shipping manifests, and purchase orders everywhere. Often, you’ll find yourself using the same forms over and over again, so it makes sense to automate as much of the process of filling them out as possible. I’ll show you how to automatically fill addresses and contact information into a template shipping manifest… Continue reading How to Automatically Fill Out Forms using Data Validation and VLOOKUP
Alternate Notation, Part 2: Converting Mixed-Base Values to Dollars and Cents
Data imports from other programs form the basis of a lot of the information we work with in Excel, and in the best of times, it arrives perfectly formatted and ready to be analyzed in the spreadsheet. Not every data import is equal, however, and occasionally, the numbers coming in don’t resemble anything that a human could read. Today, we have an inventory system from Mars which likes to store price information in a very non-standard way. In this poor computer’s mind, -66 means -$6.75, 72 means $7.25, and 540 means $54.00. In order to make sense of it, we’re going to have to break down the rules of this alternate notation and convert it into something we (and Excel) can use. Continue reading Alternate Notation, Part 2: Converting Mixed-Base Values to Dollars and Cents
Car Loan Calculator, Part 4: Data Visualization Using Conditional Formatting, Charts, and VBA
By the end of Part 3 of this series, we have a very functional car loan calculator that gives us a detailed view of the trade-offs between interest rates and loan terms. With all this data at our fingertips, the purpose of a tool can get lost. This is where data visualization can save the day. A clear chart or graph can turn a complicated mountain of information into a simple, intuitive business decision. In Part 4, we’ll change our car loan calculator from an analyst’s tool into a user-friendly application by adding a dynamic chart and heat map using conditional formatting and a little bit of VBA. Continue reading Car Loan Calculator, Part 4: Data Visualization Using Conditional Formatting, Charts, and VBA
Alternate Notation, Part 1: Converting Hours to Days-Hours
Working with numbers is really Excel’s specialty. Most of the basic operations we use involve adding things up, counting them, or performing other basic calculations. This works great when the numbers we use are in the standard base-10 and operate according to the rules of basic arithmetic. Sometimes, though, life gives us a need for strange, inconsistent numbers. Over the course of this series, we’ll explore how to work with Excel in alternative notations, starting today with Days–Hours. Continue reading Alternate Notation, Part 1: Converting Hours to Days-Hours
How to Sort Mixed Numbers and Text Using String Functions
Excel’s built-in sort functions are incredibly useful for organizing data and ordering information like dates, times, and other numerical inputs. Sometimes, though, you may need to sort product IDs, employee numbers, or other information that has letters and numbers in it. Then, what do you do? A normal Excel alphabetical sort will not prioritize the numeric parts of a string on its own. Here is how to sort alphanumeric data with complete control… Continue reading How to Sort Mixed Numbers and Text Using String Functions
Car Loan Calculator, Part 3: Scenario Analysis Using a Data Table
In Part 2, we took our simple car loan calculator and added a breakdown of the payments to learn more about the interest charges. Our calculator can still only look at one set of variables at once. That means that if we wanted to compare different loan lengths (terms) or different interest rates, we’d have to manually enter each combination. Today, it is time to change that. Why do something by hand if we can get Excel to do it for us? Get ready to learn how to use one of the most powerful tools for scenario analysis in the Excel arsenal – the Data Table! Continue reading Car Loan Calculator, Part 3: Scenario Analysis Using a Data Table
Car Loan Calculator, Part 2: Building a Payment Table and Calculating Interest Using the SUMIF Function
Last time, we set out with the goal of building a car loan calculator that could tell us the monthly payment due and how much was owed. It was a great start, but like many of the calculators you can find online, it was missing a lot of details. Today we’re going to take the simple tool we started with and start to add some of those missing features, including a deeper look at the interest payments and the balance over time. Here we go! Continue reading Car Loan Calculator, Part 2: Building a Payment Table and Calculating Interest Using the SUMIF Function
Car Loan Calculator, Part 1: Collecting Variables and Calculating Payments Using the PMT Function
You’ve been learning individual Excel functions and quick tips to improve your work, but now it’s time to put them together to make a functional tool. Calculating the payments for a car loan is a pretty basic financial exercise. Google “Car Loan Calculator” and you’ll find no fewer than 31 million results! But with a few basic formulas and an Excel worksheet, you can make a payment calculator that better and more powerful than the majority of those online! Think you’re ready to start putting your Excel skills to use? Let’s get started! Continue reading Car Loan Calculator, Part 1: Collecting Variables and Calculating Payments Using the PMT Function
Top 10 Keyboard Shortcuts to Save Time in Excel
![Keyboard Shortcuts [Image]](https://exceltactics.com/wp-content/uploads/2013/08/KBLead.png)
You know about Copy (CTRL+C) and Paste (CTRL+V), but did you know that almost every menu and operation in Excel can be controlled from the keyboard? Left and right clicks can get a lot done, but knowing your way around the keyboard is often the fastest way to crunch the data in your spreadsheets. Here are the Top 10 keyboard shortcuts in Excel, along with a few extras… Continue reading Top 10 Keyboard Shortcuts to Save Time in Excel