In the following screencast I show how to use conditional formatting in Excel to automatically create horizontal lines between different groups of data in a table. When applying conditional formatting you need to pay particular attention to properly setting up the cell references (absolute, relative, or mixed) in the conditional formatting formula.
One thing researchers should keep in mind is that graphs in research reports or articles, and even those shown in verbal presentations, are not meant for the author, but for the reader or the seminar audience. The line chart that a researcher uses in the data exploration phase—with default gridlines, tick marks, and colors—may not […]
PivotTables are one of the most useful tools in Excel. One feature that can be confusing to use is calculated fields. In this example I show how to use calculated fields to calculate the percentage of sales arising from a specific product by city and have the percentage of sales automatically aggregate to the provincial […]
Here’s some data in Excel 2007 copied from the Northwind Traders database that comes with Microsoft Access. In this post, I’ll cover how to create separate worksheets that only show the information for people having a specific job title. The final result will look like this: This can easily be accomplished in a few steps […]
When extracted, data collected using radio buttons sometimes comes out as a TRUE/FALSE column for each option. For example, data from the following question: may look like this in the extract: Data that is spread over multiple columns like this can be hard to analyze (e.g., pivot tables don’t work well with this kind of […]
Every once in a while, a situation comes up where there’s a need to copy a table made in Microsoft Word to Excel. If the Word table has multiple lines of text within a cell that have been separated by a carriage return (i.e., the Enter key has been typed at the end of a […]
Pressing the Alt key and equal sign key (i.e., first press the Alt key and then while keeping it pressed, press the equal key) will autosum a contiguous range of cells. In the following example, the numbers 10, 20, 30 are typed into cells B2, B3, and B4 respectively. Then in cell B5, first the […]
In this post, I’ll cover a few more techniques for quickly moving around in Excel. Here’s the link to the earlier post on navigating in Excel. This screencast covers changing the direction the cursor moves after pressing the enter key, keeping the cursor from moving after pressing the enter key, and the keyboard shortcut for […]
Two big productivity boosters in Excel are the F2 and F4 keys. F2 will put Excel into the edit mode. If you need to change something you’ve already typed into a cell, just press F2 to edit the cell. If you change your mind, press Esc to get out of edit mode. If the cursor […]
About a year ago, I asked this question on the SuperUser site, but I didn’t get an answer that worked. I’ve finally come up with a solution. The following is a very common workflow when writing a report: 1. Make tables and charts in Excel 2. Copy and paste these into Word and write additional […]
