5 Excel Hacks that Take Less than 1 Minute

by | Mar 14, 2017 | Excel |

excel shortcuts

Meet my guinea pig. She chooses to remain anonymous throughout this test so I’ll just refer to her as Penny (I had a guinea pig called Penny when I was 7 years old).

So here’s the deal. I set Penny 5 tasks to do in Excel, threw down the gauntlet and told her that each of these tasks could be accomplished within 1 minute.

How did she go?

excel weekdays

Task 1 – Enter the Dates for the Current Month in Column A Excluding Weekends in 4 Easy Steps

Time Taken for Guinea Pig: 1 min 25 Secs
Time Taken for Me: 20 Secs

  1. Type today’s date in cell A1 e.g 1/3/17
  2. Place the mouse pointer over the lower right corner of the cell containing the date and when it changes to a thin black cross, click and drag down column A until you see the last day of the month in the yellow screen tip box.
  3. Immediately as you release the mouse you will see a small Auto Fill options button appear next to the selected cells, click on it and choose Fill Weekdays and then adjust the column width to remove the hash symbols.
  4. Change the cell format to Long Date if desired.

TIP The Auto Fill options tag should not be ignored, it contains useful options that can be applied to change what happens when you click and drag to fill down or across.

Where did Penny go wrong? She had to manually remove every instance of Saturday and Sunday from the list

Task 2 – Add a Header and Footer to 12 monthly sheets (Jan to Dec) in 5 Easy Steps

Time Taken for Guinea Pig: 12 mins 11 Secs
Time Taken for Me: 46 Secs

  1. Group the sheets you want to work on; first, click the Jan sheet
  2. Hold down Shift on the keyboard and at the same time click the Dec sheet, (the sheets will now be grouped so when you create the header and footer it will be applied to all sheets in the group)
  3. Click Insert Header and Footer on the ribbon and click into the header section/footer section to add information you need, you can also use the buttons on the ribbon bar at the top of the screen to add page numbers, date and time etc.
  4. When you are done, click on a cell in the worksheet and then click View on the ribbon and Normal to return to normal view
  5. Ungroup the sheets, to do this, select a sheet which isn’t in the group OR if all sheets are grouped simply click onto a different sheet.

TIP grouping sheets is useful for making changes simultaneously across multiple sheets in the same workbook e.g changing column widths on multiple sheets, printing sheets or changing the formatting of titles on multiple sheets plus much more

Where did Penny go wrong? She had to repeat the process of inserting the header and footer for every single sheet

excel shortcuts

Task 3 – Create a Chart to Compare Actual and Forecast in 3 Easy Steps

Time Taken for Guinea Pig: 1 min 43 Secs
Time Taken for Me: 9 Secs

The following data was used to produce the chart.

ForecastActual
Sydney12500015000
Melbourne269000275000
Perth875000900765
Adelaide367445478545
Darwin278556245778
  1. Highlight all of the data including column headings and row labels (in this case A1:C6)
  2. Press the F11 function key on the keyboard to product the default column chart
  3. Click into the chart title and edit it as required

TIP Excel shortcut keys are a necessity if you want to be more productive and can save hours of wasted time

Where did Penny go wrong? She manually created the legend entries, axis entries and data which meant separately specifying a range for each piece of data.

excel shortcuts

Task 4 – Display Data Bars inside Cells to Highlight Difference Between Actual and Forecast in 3 Easy Steps

Time Taken for Guinea Pig: She gave up
Time Taken for Me: 12 Secs

  1. Highlight the forecast values
  2. Click the Home tab on the ribbon and select Conditional Formatting, Data Bars and choose a colour
  3. Repeat for the actual values and choose a different colour.

TIP Conditional formatting is a highly effective way of using data visualisations to highlight data that meets criteria in different ways including traffic light symbols, arrows, shapes and data bars.

Where did Penny go wrong? She had no idea where to start and gave up.

Task 5 – Calculate How Many Days from Now to Christmas in 3 Easy Steps

Time Taken for Guinea Pig: I lost count zzzzzzzzzzz
Time Taken for Me: 18 Secs

To work this out you need to know the current date so that each day Excel counts down how many days are left.

  1. Type the formula =TODAY() in cell A1, this will always display the current date and it will change day to day.
  2. In cell A2 type 25/12/17 or whichever date you want to use.
  3. In cell A3 enter the following formula:   =A2-A1 to calculate the difference between the 2 dates.

TIP Formulas aren’t always about numbers in Excel, formulas can calculate on dates, text and a whole host of things.

Where did Penny go wrong? She didn’t know where to start.

Author: Anita Lund owner of Trainers Direct, Microsoft Certified Instructor teaching Excel courses in Sydney, please share if you found this article useful. Thank you.