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 […]
Make your data colour coded with Conditional Formatting
Conditional Formatting, such a great tool. On the home tab of your #Excel sheet or shortcut key Alt, H, L. Start by selecting the cells that you want to apply the formatting too. I have used it to highlight all the cells in my column that are over 85. You can use it to automatically […]
Line up
When you have graphs(Charts) or pictures in Excel and you want to get them lined up you can use Align. It is available on the Page Layout Menu and also the Format Shape Menu. You need to select the elements first. Use the Shift key (hold it down) while you click all the elements you […]
Display just the way you want.
The data displayed in a cell can be different to the data that is stored in the background. You can change the way the data looks by formatting the cell. Perhaps the simplest way to understand this is to enter a number and then format it as money (either Currency or Accounting). The data stored […]
How to fit in… your data into a cell.
Fitting data into a cell can be tricky, but with a few simple tricks and tips, you can fit the data into the size and shape of cell that you want. Resize your columns. You can change the width of an individual column (or row height) by moving your mouse pointer to the line between […]
Powerful Filter Button Options.
Once you have added Filter buttons to your data, either via the home menu or because you have a table, you have a lot of options on how to use them. Filter by Color – if you have colours on your cells, either from formatting or conditional formatting, you can use this to filter just […]
Getting started with Filter buttons.
Add Filter buttons to your data using Sort and Filter, then Filter. Found on the Home Menu. (Shortcut keys: Alt H, S, F) If you add filters to your data, you can access the sorting from the filter button. Sort A to Z (Smallest to Largest) and Z to A (Largest to Smallest) are available […]