Use Center Across Selection instead of Merge and Center.
Instead of using Merge and Center, try using Center Across Selection. It makes highlighting easy and formulas more logical. The results look the same, but what is happening in the background is different. Below is how most people will put a title across multiple cells, by using the Merge & Center button. Notice the green […]
Filter a chart the easy way.
You can filter a chart, right on the chart. Select the graph and choose the filter button, the one that looks like a funnel. Choose which series or categories you want to be able to see. Select Apply. Here is the original chart. And here is the chart with North and South unselected.
Two very handy shortcut keys for charts.
There are two particularly useful shortcut keys when using an Excel chart (graph). Use Ctrl + Left or Right arrow keys to move through selecting each chart element. From the whole chart area, to Plot area, legend, y-axis, x-axis, title, all points in the series, and then each individual point in the series. Great for […]
Sorted! Get sorted with SORT function.
Sorting used to have to be done in place, but the Excel SORT function allows you to present data from one place, in another place sorted by your specifications. Here is a simple example. This is a list of companies, in company order, their listings and market share. This is our original data. What if […]
Filter function: Return just the data you want to see.
A lot of users of Excel know about the filter buttons. You can read about them here: The filter function allows you to filter data into another place in your workbook, leaving your original data as is. Just like the filter buttons you can display some information from a list based on specified selection criteria. […]
Hookups with data: (x, v, h) lookup and index match.
When you have data in one place, and you want to collect a certain piece of that information and return it somewhere else based on a given value, you probably want a lookup. Depending on the situation there are options about how to do that. The VLOOKUP (the V stands for Vertical) formula used to […]
An easy way to pull information together: Use consolidate.
If you have data in different places that you would like to bring together as one, Consolidate is a great option. You can choose data from different ranges and combine it together either by the headings in the top row, the left column, or both. Once you select Consolidate from the data menu (shortcut keys: […]
3D Reference in Excel
You can create formula in excel that gets data from multiple worksheets without having to specifically reference each sheet by using 3D references. Example of 3D reference: =sum(Holiday1:Holiday3!C3) In the example above all the values in C3 will be added together from each sheet from Holiday1 to Holiday3. No matter what the sheets are called, […]
Interpreting Formulas in Tables.
You are probably aware of how formula use cell references in Excel. A1 refers to a single cell, A1:A5 refers to a column of cells and A1:G1 refers to a row of cells. If you have a formula in a table, or a formula that refers to a table then the way that the formula […]
More Conditional Formatting Options.
On the home tab of your #Excel sheet or shortcut key Alt, H, L. You can use conditional formatting to simply highlight values of interest. See this post. Other options from within Conditional Formatting are, Data Bars, Colour Scales and Icon Sets. Data Bars Within Data Bars, you get two Standard options: Gradient Fill, and […]