View Post

Increasing Efficiency by Using Multiple Sheets

In Basic by Garrett Dorman0 Comments

When performing larger tasks in Excel, it is very important to work as efficiently as possible. There are many ways to increase efficiency, one of which is by utilizing an important feature in Excel, multiple worksheets. The entirety of an Excel document can contain many sheets, with the sum of all the sheets being known as the workbook. Each sheet can contain completely separate information, yet all this information can be used and referenced in the entire workbook. This article will describe the basics of using multiple sheets to create an efficient workbook. Every sheet in an Excel workbook can …

View Post

Trendlines in Excel

In Basic by Garrett Dorman0 Comments

Trendlines are an important tool to measure overall change in data. These lines are designed to track data movements in the long-term, ignoring the subtle changes in the short-term. There are many uses for trendlines, such as for finding the best-fit line for a data set, or for predicting the values of future data. An example of a trendline inserted in an Excel chart is shown in the below figure. A trendline can be added to any chart very easily. After your basic chart is created, first select Add Chart Element either on the Design toolbar or in the submenu …

View Post

Spreadsheet File Formats

In Basic, Intermediate by Garrett Dorman0 Comments

While discussing the multitudes of options and functions that exist within Excel, in order to truly master the program, it is necessary to discuss a few features outside of the program itself. One of these features is the various different file formats that a spreadsheet can be saved in. With over 20 default formats built in, and countless more that are not listed, knowing which format to use at the right time is very important. We will discuss each of these many options and when to use each one, as well as some extra options inside of Excel pertaining to …

View Post

Using Excel to Build Randomizers

In Basic, Formulas by Garrett Dorman0 Comments

Occasionally, the need for a random number will surface in Excel. Fortunately, creating a random number is quite simple, given Excel has a built in function for this task: the RAND function. Using this function, along with some other associated functions gives you all the tools you need to create any sort of randomizer you would need. When creating a randomizer, the central function used is the RAND function. The RAND function looks like this: =RAND() It is a very simple function in principle, containing no arguments. However, there are some subtleties about this function that are very important. First, …

View Post

How to use the vLookup Function in Excel

In Formulas, Intermediate by Garrett Dorman0 Comments

Imagine having a spreadsheet with thousands of entries. Also imagine needing to find a number for one of those entries. Maybe a phone number from a list of employees or a price for a specific item. This is a common problem faced by many Excel users around the globe. However, using Excel’s various lookup functions, this task becomes easy. By the end of reading this, you will be able to use several of these functions to find any information you need, no matter how many entries have to be sorted through to find it. The cornerstone on searching through Excel …

View Post

20 Excel Shortcut Keys to Speed Up Your Workflow

In Basic by theexcelpert0 Comments

Clicking. Dragging. Right-Clicking. Searching through Ribbons and Tabs. All of this takes time. Using Excel is about being effective and efficient. The faster you know how to move, the more you can get accomplished, which means you’re spending less of your life staring into a computer screen (yay!). I’m going to skip over the rudimentary Control + C / Control + V and assume you already know the super basic copy and paste functions. So let’s talk about some of the (other) top shortcut keys to take you from slow-poke to super-freak. Select Entire Row | Shift + Spacebar Select …

View Post

How to setup the Quick Access Toolbar

In Basic by theexcelpert0 Comments

The Quick Access Toolbar is one of the most under-utilized features of Excel that I see from both beginner and experienced excel users alike. Unfortunately, for those under-utilizers, the Quick Access Toolbar is also one of the easiest ways to increase your workflow efficiency with just a few clicks. Let’s go over the Pros and Cons of the Quick Access Toolbar. Pros: Increased workflow efficiency Quick Access to most often utilized commands Customizable options Cons: (crickets) So now that we agree there is really no reason to NOT be using the Quick Access Toolbar, let’s talk a bit about what …

View Post

How to make a Waterfall chart

In Intermediate by theexcelpert0 Comments

Some of the more business savvy out there immediately recognize and understand the value of the Waterfall Chart. It’s essentially the single greatest way to simultaneously create a great looking bridge chart AND show off your crazy awesome Excel skills. Now if you’ve already upgraded to Excel 2016, Waterfall Charts are now a built-in feature (at long last, I might add). However, if you’re one of the less fortunate, and stuck in a prehistoric Excel hell, see below. Let’s say you own a coffee shop cleverly named “The Coffee Shop” and you want to create a beautiful depiction of your …

View Post

Using the IF Formula

In Basic, Formulas by theexcelpert0 Comments

One of the most powerful statements in Excel, the IF statement can be endlessly customized and nested within other formulas to accomplish almost anything you want. The basic premise is that you can use the IF formula to test if a condition is TRUE or FALSE. If the condition is met, or TRUE, then you can have the formula output X, and if the condition is not met, or FALSE, then the formula will output Y. In this case, both X and Y are rules the formula will follow based on the result of the condition specified. If that totally …