Stacked bar or column charts are used a lot by the media and corporate world. I believe they are popular because they display a lot of information in one chart, and are relatively easy to create in Excel. However, I’m not a big fan. I’m going to explain why I consider them to be “bad…
This chart that compares a series of data points against the entire distribution across multiple categories. It’s a great alternative to a box plot or histogram because it is easy to explain and conveys a clear message to the readers. Full step-by-step guide and free downloads are available.
In this post I will explain how to calculate a dollar or percentage commission payout in one cell using the SUMPRODUCT function. Calculating commissions on a tiered rate structure can be difficult because you are trying to determine the cumulative payout based on different rates at each tier, and the achievement amount might fall in…
Structured Reference Tables are great for creating clean, easy to read formulas. But creating absolute references to the columns (aka anchoring the columns) in the formula is a bit tricky. Quick Guide Duplicate the column references as if referring to multiple columns. Absolute references to: One column in the same or other table: table1[[column1]:[column1]] One…
Excel has a built-in feature to convert text to numbers, but it can be tricky to use when you are trying to select a long list of cells or multiple rows and columns. In the video below I describe how to use keyboard shortcuts to accomplish this very quickly. Keyboard Shortcuts to Convert Text…
When you have a dashboard with small panel charts it is nice to be able to zoom in on the charts to see the trends better. The ‘Zoom_Chart’ macro included in the workbook allows you to add a zoom button (shape) over the top left corner of the chart to zoom in on the chart….
The Find All Form for Excel allows you to type a search query in a text box and have the results appear in a list box. The results are narrowed down as you type. This is similar functionality to Google’s auto fill or auto suggest feature when doing a Google search. See the animated screen…
Did you know that the new theme based colors in Excel 2007 are being converted to the old 56 color palette when opened in a previous version of Excel? This means your new spreadsheets will NOT look the same to clients or colleagues using Excel 2003 or earlier versions. The Color Palette Conversion tool solves this problem, and it’s free!
Do you spend too much time formatting the same report in Excel?
You probably find yourself formatting the same spreadsheets in the same way daily, weekly, or monthly. This can be a very time consuming and redundant task. The Format Copier tool automates the process of applying page layout and formatting options to multiple sheets in multiple workbooks. And it’s FREE!
The Quick Access Toolbar (QAT) in Excel 2007 is extremely useful for creating keyboard shortcuts for items that don’t have a defined keyboard shortcut. For example, there is no defined keyboard shortcut to Paste Values but you can easily set it up on the QAT.
My name is Jon Acampora and I'm here to help you improve your Excel skills. This blog is updated frequently with Excel and VBA tips, tricks, tools, and techniques. Subscribe below to stay updated.
