Data doesn’t always import or paste into Excel as nicely as you want. Sometimes, all the information is there, but extra blanks and spaces are carried along with the data cells. At first glance, this looks like it could be a very tedious problem to solve… It could take ages to manually take them out, and sometimes the data order is sensitive, so it can’t be sorted (to bring all the blanks together). Fortunately, there is a way to select all the blanks in a selection simultaneously for deletion. Let’s walk through an example of how… Continue reading How to Automatically Delete Blank Cells to Organize Data
How To Add an Average Value Line to a Bar Chart
When you are comparing values in a bar chart, it is useful to have some idea of what the average value looks like relative to the data set. By default, however, Excel’s graphs show all data using the same type of bar or line. In this quick tutorial, we’ll walk through how to add an Average Value line to a vertical bar chart by adding an aggregate statistic (Average) to a data set and changing a series chart type.
Continue reading How To Add an Average Value Line to a Bar Chart
Automatically Highlight Max Value in a Data Table Using Conditional Formatting
When you are looking at a large list of numerical data, it’s useful to find maximums and minimums. You can always sort the list to find the largest totals, but if you can’t modify the data (or want to sort by some other rule, like alphabetical), you might need another way to find the max or min. Conditional Formatting in Excel lets you set rules for highlighting values just like you would when using IF statements. Let’s walk through an example using quarterly sales data… Continue reading Automatically Highlight Max Value in a Data Table Using Conditional Formatting
Make Custom Excel Keyboard Shortcuts with Quick Access Toolbar
We’ve already gone over my top 10 keyboard shortcuts in Excel… I’ve even shown you how to make your own custom shortcuts, using Macro capturing and a little VBA tweaking. But even if you don’t want to dive off the deep end into custom programming, there’s still a way to make quick keyboard shortcuts for some of your most-used actions. Better yet, it’s built right in to Excel, and it’s called the Quick Access Toolbar! Learn how to customize it with your favorite shortcuts and turn them in to easy hotkeys… Continue reading Make Custom Excel Keyboard Shortcuts with Quick Access Toolbar
How to Count Numbers with Leading Zeros in Excel Using SUMPRODUCT instead of COUNTIF
If you’ve used Excel for a while, you realize that working with numbers that have leading zeros can be a pain. Excel treats all formats the same when doing arithmetic calculations, which means that 0023 + 23 still add up to 46. This makes things complicated when you are working with other numbers like product IDs or UPCs that have leading zeros. If 1234 is different from 001234, then you need a way to preserve the zeros. Counting numbers with leading zeros is especially challenging, because the COUNT functions in Excel automatically remove those precious zeros. Here’s a way around the problem, using a little-known ability of SUMPRODUCT… Continue reading How to Count Numbers with Leading Zeros in Excel Using SUMPRODUCT instead of COUNTIF
Excel Web Resource Links Are Up!
I’m working hard to make Excel Tactics a great resource for Excel tricks and tutorials, but there are other great sites and forums that can also be great resources to help you master your spreadsheets. I’ve put together a page of Web Resources to help you on your way.
I’ve included some pages with introductory videos to walk you through Excel basics. There are also a few fantastic forums where you can get your questions about specific problems answered on the fly by volunteer pros (including yours truly). Finally, there’s an index of handy macros and VBA snippets that address common tasks. Take a look and see what you’re missing! I’ll add more resources to the list as I find them. If you want to stay up to date with the latest links, join the newsletter to get an e-mail when I post them!
Make Your Own Custom Keyboard Shortcuts Using Macros
We all know that keyboard shortcuts are the fastest way to accomplish most tasks in Excel. I already showed you my top 10 keyboard shortcuts, and you can get a free quick reference for them when you join the newsletter. Unfortunately, not every common action in Excel has a keyboard hot-key assigned to it by default. Paste Values, for example, is one of the most common actions when formatting spreadsheets, but the only keyboard access for it is the cumbersome ALT+H+V+V through Excel Key Tips. Here, we’ll show you how to make your own custom keyboard shortcuts with macros by giving Paste Values the convenient CTRL+SHIFT+V it should have had all along… Continue reading Make Your Own Custom Keyboard Shortcuts Using Macros
How to Save Time When Formatting Cells By Using Format Painter
The final step in most spreadsheet preparation is making all your hard work look pretty and presentable for the people who will use it. Whether you are exporting charts and tables to a presentation program like PowerPoint, or building a summary worksheet to explain the data to your board of directors, formatting cells to make all the numbers and figures line up can be tedious and time consuming. Fortunately, Excel has an often overlooked feature that can save you tons of time when you are formatting your work! Here’s a quick how-to for using Format Painter… Continue reading How to Save Time When Formatting Cells By Using Format Painter
Using Solver to Optimize Solutions to Costing Problems in Excel
Imagine you’re faced with a purchasing problem… You need to buy some inventory that comes in multiple sizes. Bigger sizes come at a discount, which can save you money. Buy too much, it will be wasted, and that won’t save you money at all. This is a problem that would be very challenging to solve with basic IF statements and arithmetic formulas. Fortunately, Excel has an official Plug-in called Solver that can do amazing things to solve optimization problems like this. Let’s walk through an introduction to Solver and work through an example… Continue reading Using Solver to Optimize Solutions to Costing Problems in Excel
Calculating Week-To-Date, Month-To-Date, and Year-To-Date Using Helper Columns and SUMIFS
When you are working with date-stamped data imports, the volume of information can be overwhelming. It is difficult to tell, at a glance, whether performance indicators are trending upwards, downwards, speeding up, or slowing down. Aggregate statistics like Week-To-Date, Month-To-Date, and Year-To-Date performance data can often be easier to read and tell you more about what’s actually going on. Let’s learn how to implement X-To-Date aggregate statistics using helper columns, Excel date functions, and SUMIFS. Continue reading Calculating Week-To-Date, Month-To-Date, and Year-To-Date Using Helper Columns and SUMIFS