Category Archives: Quick Tips

Converting Decimal-Separated Dates to Excel Date Format

Decimal Date LeadDates don’t always get imported into Excel in a nice, clean, ready to use format. When they don’t, it can be a huge hassle – un-formatted dates can’t be added or subtracted, or even filtered or sorted easily. It is usually best to convert them to the standard Excel date format. Excel has an entire array of functions you can use to work with them once you do (you can learn about them here). Excel’s string manipulation functions can help you convert date text to the Excel date format. Let’s walk through an example… Continue reading Converting Decimal-Separated Dates to Excel Date Format →

How to Automatically Delete Blank Cells to Organize Data

Delete Blanks LeadData 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

AverageLineLeadWhen 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 →

Pages: 1 2

Automatically Highlight Max Value in a Data Table Using Conditional Formatting

Highlight Max LeadWhen 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

QAT LeadWe’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

Leading Zeros LeadIf 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 →

How to Save Time When Formatting Cells By Using Format Painter

Format Painter LeadThe 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 →

Top 10 Keyboard Shortcuts to Save Time in Excel

Keyboard Shortcuts [Image]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 →

How to Add Totals to Stacked Charts for Readability

Chart Totals Above Stacked Bar Chart [IMAGE]Have you ever noticed how standard charts in Excel never look quite “finished”? If you put in the basic data, you’ll get a nicely scaled X axis and Y axis, and all the bars will line up. If you use data labels, you can see exactly what the values are for each bar. But they’re still hard to read because Excel doesn’t provide totals! I’ll show you how to add clean, easy to read totals to your stacked column and bar charts… Continue reading How to Add Totals to Stacked Charts for Readability →