Written by John "Jack" Dumoulin
#1 - Conditional Formatting
You can find conditional formatting on the home tab of any Excel workbook in the Styles group (Home tab/Styles). This feature enhances your data on the spreadsheet and makes it easier to compare your data with many different styles and formats (such as fill colors, font colors, symbols, mini-graphs, etc.).
For example, in a table that shows the sales revenues for a company each month, you could apply conditional formatting to display the months with the highest sales in green, the months with the lowest in red, and the average sales months in yellow.
Conditional Formatting is a great trick. Aside from the fact that not many people know about it, it’s very easy to use to enhance and make your data more eye-catching.
#2 - Fill Handle
The Fill Handle is a copy command shortcut, but with some advanced features. You can find the fill handle as a small, black box in the bottom right corner of any cell and can use it to drag data, text, or format to other cells within the adjacent cells in the row or column.
Formulas can also be copied using the fill handle. For example, if you need to find the sum of multiple columns in a table, you can find the sum of one column, and then drag the fill handle across the row and the formula will automatically compute for the remaining columns.
If you have sequential data, you can use the fill handle to populate the data and it will automatically replicate the same logical values.
#3 - Freeze Panes
Freeze Panes are useful when you have many columns and rows filled with data, and you don't want to have to keep scrolling back towards the top left of your worksheet to see the row/column headings. The Freeze Panes function can be found under the View tab in the Window group (View tab/Windows).
Freeze Panes keep the headings in place when scrolling throughout the entire worksheet. For example, if you are adding data to columns, it’s an easy convention for keeping the subject of that record in easy view for speed and ease of use.
To do this, simply click the row/column below the rows/columns you want to "Freeze" and then click "Freeze Panes".
#4 - Sparklines
Sparklines is a type of information graphic that represent trends or variations in collected data. This can be a line, column or win-loss chart.
A Sparkline is a small graph (Line, Column, Win/Loss Comparison) that you can insert into a cell that shows a graph for a group of data. It can be found under the insert tab in the Sparklines group (Insert tab/Sparklines).
You can use Sparklines to quickly show trends in a series of values, such as sales increases or decreases, or for binary outcomes such as win-loss and true/false results. Additionally, you can highlight maximum, minimum or negative values in your data. By positioning your Sparklines next to the data, it makes it easy to visualize data trends all in one view Click the cell you want to insert a Sparkline and click Insert tab, and select the Sparkline type you need from the Sparklines group.
#5 - Absolute Reference Formula
An absolute reference is a handy tool to keep a specific cell’s formula the same, while copying the formula from one cell to other cells. This is done by changing the value reference in the cell. Absolute references are noted by adding a dollar sign in front of the cell reference.
For example, changing cell reference of "A4" to "$A$4” will lock the reference to that particular cell to all copied cells.
This is useful across many areas of business data and analysis such as sales proformas, where your target growth for a market is a fixed percentage. It’s also a great shortcut for use in financial analysis, demand planning and inventory projections.
