Free Excel Webinar Recording – Excel Yourself 2018

Feedback score 89%

In December 2018 I reviewed four of my articles from the INTHEBLACK magazine from 2018. Download the materials using the button below and watch the video.

Webinar Materials

Includes extra content not included in the magazine.

This year’s smorgasbord webinar includes

  • how to handle validations
  • dynamically highlighting the current row in a table (uses a macro)
  • centralising logic – how and why to do it – alternatives to the IF function
  • creating a slope chart (includes a macro to speed up the process)

As always there will be a few more tips and tricks shared in the session.

Open Filter the Drop Down

If you have filters turned on and you are in the heading row of the table you can press Alt + down arrow to open the filter drop down.

You can then use the arrow keys to move up and down.

Related Posts

 

Free Excel Webinar Recording – Mouse Shortcuts

Feedback score 93%

When I ran some face to face training sessions recently I was reminded how much people LOVE shortcuts. I did a keyboard shortcut webinar session in October 2018.

In November 2018 I shared lots of mouse shortcuts. Download the materials using the button below and watch the video.

Webinar Materials

Some of the topics and techniques you will learn include:

  • the various cursor shapes and how to use them correctly
  • right clicking options you may not know exist
  • copying anything quickly with the mouse, even sheets
  • drawing perfect circles and squares
  • lining up charts and other shapes perfectly
  • cool techniques with the Fill Handle
  • something you may never have tried with the right mouse button
  • using the the Office clipboard
  • a trick to use the Series dialog

This session will demonstrate lots of techniques using the mouse and also using the mouse with the keyboard.

Even if you prefer using the keyboard you might learn a few useful new techniques.

Bold Your Headings

Apparently this is not widely known, but you should always bold the headings in your tables.

Then when you use Format as Table (Ctrl + t) on the Home ribbon tab the header row will be correctly identified.

This also applies to the Ctrl + Shift + L shortcut to insert the filter drop downs.

It also applies to the ranges used for charts.

In general ALWAYS BOLD your headings – it is something Excel looks for.

Ctrl + b is the bold shortcut.

Related Posts

Free Excel Webinar Recording – Keyboard Shortcuts

Feedback score 93%

When I ran some face to face training sessions recently I was reminded how much people LOVE keyboard shortcuts. So I decided to update my keyboard shortcut webinar.

In October 2018 I shared lots of keyboard shortcuts. Download the materials using the button below and watch the video.

Webinar Materials

Using the mouse isn’t always the quickest way to perform tasks in Excel. Keyboard shortcuts can speed up your work and save you hunting through screens and dialogs. Some of the keyboard shortcuts you will learn in this session are:

  • wrap text and other useful formats
  • applying row and column grouping
  • selecting a table quickly
  • copying visible cells only
  • apply and remove Freeze Panes
  • returning after following a hyperlink
  • how to avoid an annoying feature of formula and reference dialogs when you press an arrow key to move around
  • get the most out of the Tab key

There will be lots of other shortcuts as well. Even if you prefer using the mouse you might learn a few useful new techniques.

Delete a cell comment

From a question in a recent webinar I found a keyboard shortcut to delete a cell comment.

Alt r d pressed in sequence, not held down.

Free Excel Webinar Recording – What If Techniques

Feedback score 92%

My free Excel webinar for September 2018 covered What If Techniques. Download the materials using the button below and watch the video.

Download Webinar Materials

Content applies to Excel 2010 and later versions. You will need to install the Solver Add-in – instructions in the manual and  video.

  • Goal Seek – simple what-if changes
  • Solver – advanced what-if analysis
  • Scenario Manager – handling different sets of inputs
  • Data Tables – single and double variable sensitivity analysis
  • NEW – Forecast sheet

DAX Functions

A listing of all the DAX functions with their syntax.

DAX functions are used in Power BI and PowerPivot for Excel.

It is a reference site, not a training site.

It has been created by Marco Russo and Alberto Ferrari – legends in Power BI and all things DAX.

Link to DAX Guide

 

Horizontal or Vertical Progress Bar in Excel

Conditional Format or Sparkline take your pick

In a recent webinar on conditional formatting I was demonstrating how to create a horizontal progress bar using conditional formats when someone asked an interesting question about creating a vertical progress bar. It is possible and in this blog post I will explain both techniques.

Conditional Format Feedback

The Conditional Format webinars I ran in August were popular and well received- 94% feedback score.

An email I received recently is shown below.

To watch the recording and download the materials click here.

 

 

Free Excel Webinar Recording – Conditional Formatting

Feedback score 94%

My free Excel webinar for August 2018 covered Conditional Formats. Download the materials using the button below and watch the video.

Download Webinar Materials

Content applies to Excel 2010 and later versions.

This session will take you through the basics, as well as an introduction to formula-based formats.

  • Data bars
  • Creating a progress bar using a Data bar
  • Colour scales – traffic light colours
  • Amending the default settings – getting the result you want
  • Icon sets – icons can be better for colour blind people
  • Cell-based rules – make the most of built-in features
  • Working with dates automatically
  • Formula-based rules – use formulas and functions to gain total control over conditions

Printing Tip

If your sheet has a lot of colour and you want to print it on a black laser printer, one of the Page Setup options can improve the print.

On the Sheet tab of the Page Setup dialog there is a Black And White option – see image below.

This removes all the colour and prints in black only,

You can access the Page Setup dialog from the Page Layout tab. Click the small arrow on the bottom right of the Page Setup section.