5 Best Tips Using Excel from Jack Dumoulin – Excel World Champion
When we heard that Jack Dumoulin, a 17-year-old varsity baseball player at Forest Park High in Prince William County in Virginia had won the Excel World Championship last fall, Innowera sought to learn more about his favorite tips and tricks in Excel for our users.
Beating out 156 opponents from all over the world is quite an accomplishment. Jack took home the winning title, making him the first American to win the competition in its 16-year history.
We’re thrilled that Jack was willing to share his insights with our user community. Here are Jack Dumoulin’s top 5 Excel tips that any Excel user will find useful:
#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.
For SAP users, if you’d like to try Innowera’s Process Runner – the most advanced SAP and Excel integration tool on the market – you too can find new ways to use Excel to upload and download data to and from SAP.
Share your favorite Excel tips with us, and tell us what they’ve helped you to accomplish.
Please send to firstname.lastname@example.org. With your permission we’ll post some of your tips with a group of best ideas in an upcoming blog post.