Display a New Window

If you have two or more screens (I have three) you can have separate Excel windows on separate screens. This can make copying and linking much easier.

The keyboard shortcut to open a new window in the current file is Alt W N pressed in sequence not held down.

This allows you to have the same file visible in two separate windows. Each window can have a separate sheet.

The New Window icon is on the View ribbon tab.

Related Posts

Introduction to Power Pivot

FREE

Duration: 1.5 hours

Power Pivot removes the limit of one table, one report for your Pivot Tables. You can combine data from multiple sources into a single report. You can summarise tables in relational databases without involving the database administrator or having to have a View created.

Its a pivot table on steroids (in a good way). Power Pivot can report on virtually any relational data base as well as bringing together data from different sources into a single report. Eg combining a csv file; an Access database and an Excel table.

We will use an Access database in this session. YOU DO NOT NEED TO HAVE ACCESS INSTALLED ON YOUR PC/LAPTOP.

The session will cover

  • background information on using relational databases
  • creating a PowerPivot report from a large relational database
  • adding tables to the data model to expand the reporting possibilities
  • techniques to make the report more reader-friendly
  • hiding fields to simplify the interface
  • using Slicers
  • an introduction to DAX
  • books and websites to help you learn more
Date: November 25, 2021
Time: 1:00 PM AEDST
Event: FREE Intro to Power Pivot (1.5 hours)
Topic: Introduction to PowerPivot
Public: Public
Registration: Click here to register.

Excel PivotTable Essentials

NO CHARGE

Duration: 1 hour

This updated session will cover the basics of pivot table creation as well as solving some common frustrations. Topics covered include

  • pivot cache what it is and why it is important
  • data rules that make creating pivot tables easier
  • format as table and why you need to use it
  • populating missing entries in the data – manually and with Power Query
  • the basics of creation
  • pivot charts
  • filtering with slicers
  • grouping

As always, I will share a few general tips along the way.

Date: November 18, 2021
Time: 1:00 PM AEDST
Event: FREE Excel PivotTable Essentials
Topic: Excel PivotTable Essentials
Public: Public
Registration: Click here to register.

Comma Format

The comma format for numbers is pretty popular.

There are a couple of keyboard shortcuts to apply it.

Hold the Ctrl and Shift keys down and press 1 on the keyboard.

The next one uses the Alt key. Use the left Alt key. Keys pressed in sequence (don’t hold them down).

Alt H K

Remember Hong Kong.

Related Posts

 

FREE Webinar – Excel Chart Tips and Tricks

This webinar is focused on showing you how to create and modify charts in Excel with a minimum of fuss.

You will see some best practice design and formatting techniques demonstrated and explained.

See how to create dynamic charts that automatically change based on selections made or data added.

Learn about the feature called Sparkline charts which are great for dashboards.

Date: November 16, 2021
Time: 1:00 PM AEDST
Event: FREE - Excel Chart Tips and Tricks
Topic: Charts
Public: Public
Registration: Click here to register.

Old Macros XL4 macros

If you use the old Excel macro language – known as XL4 macros – you may need to update a setting to keep using them.

This is the macro language before VBA was introduced in the Excel 5 back in the 90’s.

Not many people use these macros any more but there a couple of techniques that they are used for.

Microsoft will soon disable them automatically and you will need to turn them back on if you want to use them.

The setting to update is in the Trust Center Macro settings – see image below.

 

Related Posts

 

 

Power Query Shortcut

Have you tried right clicking a formatted table recently?

There is a new option to Get Data from Table/Range – which means to import the table into Power Query so you can data cleanse the table.

Related Posts

 

 

Power Query Zoom

I used this today in a live webinar.

I zoomed into the Power Query window in Excel to make it easier to see.

Ctrl + Shift + + (plus)

Excel Advanced Range Names

 Price: $25 incl GST

Duration: 1 hour

This session covers many advanced techniques. These techniques can be applied to reporting models, dashboards, budgets and forecasts.

The session covers

  • Naming conventions – what they are and how to use them to simplify formula creation
  • Non-formula uses for range names – create an unbreakable hyperlink
  • Linking between files – technique to reduce broken, or corrupted links
  • Advanced techniques – creating a sheet name range name and some advanced ways to refer to ranges
  • The INDIRECT function and range names
  • One Formula to Rule Them All – an amazing technique that can be applied to many situations – a single formula to extract Actuals, Budgets and Forecasts
  • Advanced range reference techniques

As always, I will share a few general tips along the way.

Date: September 23, 2021
Time: 1:00 PM AEST
Event: Excel Advanced Range Names
Topic: Excel Advanced Range Names
Public: Public
Registration: Click here to register.

FREE – Custom Number Formats

In this webinar we examine Custom Number Formats which hide away at the bottom of the Number Format tab. These custom made formats offer some useful techniques.

They can

  • display negatives in red and with brackets
  • format mobile phone numbers correctly
  • display numbers and text together and still perform calculations
  • hide zeroes
  • display rounded numbers to thousands and millions
  • display the day of the week
  • create customised dates
  • be saved and be available in new sheets and files using a Template
  • be saved as a Style

See examples and demonstrations of many different custom number formats and learn how to create your own.

As always I will share a few other tips.

Date: September 28, 2021
Time: 1:00 PM AEST
Event: FREE - Custom Number Formats
Topic: FREE - Excel Custom Number Formats
Public: Public
Registration: Click here to register.

Excel Range Name Essentials

 Price: $25 incl GST

Duration: 1 hour

Learn the basics, plus some powerful techniques to make your range names more dynamic. This session has everything you need to get started with using range names effectively.

Even if you have been using range names this session provides some useful shortcuts and techniques that you may not be aware of.

This session covers

  • the three ways to create a name (some are much easier than others)
  • naming rules and suggestions
  • keyboard shortcuts
  • name intersections
  • formula-based names
  • using names with formatted tables

As always, I will share a few general tips along the way.

Date: September 21, 2021
Time: 1:00 PM AEST
Event: Excel Range Name Essentials
Topic: Excel Range Name Essentials
Public: Public
Registration: Click here to register.

Webinars – Advanced Excel (3 webinars)

This is a three-part series of live webinars run over three consecutive days.

Each session will be about 1.5 hours long.

The sessions will take you through the following topics

  • Advanced summing techniques
  • Logic formulas
  • INDEX-MATCH combination
  • Text functions
  • INDIRECT function
  • Power Query single, multiple files and merging
  • Advanced Pivot Tables
  • Relationships
  • GETPIVOTDATA function

Along the way there will be lots of shortcuts and advice on creating effective spreadsheets.

Date: September 14, 2021—September 16, 2021
Time: 1:00 PM AEST
Event: FREE - Advanced Excel - 3 webinars
Topic: Advanced Excel
Public: Public
Registration: Click here to register.

Keyboard Shortcut to Record a Macro

You need the Developer ribbon tab visible to record a macro, or do you?

This old-fashioned keyboard shortcut will open the record macro dialog. Pressed in sequence, not held down.

Alt T M R

You can also use the little icon next to the Ready at the bottom left corner of the screen.

Once you start recording the small square icon to stop recording appears in the same spot.

Related Posts

Webinars – Excel Level One Series (3 webinars)

This is a FREE three-part series of live webinars run over three consecutive days.

Each session will be about 1.5 hours long.

The series will take you through the basics of Excel and teach you how to

  • set up shortcuts
  • use the mouse effectively
  • navigate round a file
  • create basic formulas and functions
  • apply formats
  • use shortcut keys to save time
  • printing tips

Along the way there will be lots of shortcuts and advice on creating effective spreadsheets.

One registration signs you up for all three sessions. If you can’t make a session its OK, they are all recorded and you get the recording the next day.

Date: July 27, 2021—July 29, 2021
Time: 1:00 PM AEST
Event: FREE Excel Level One - Beginners - 3 webinars
Topic: Excel Level One Series
Public: Public
Registration: Click here to register.

Speed up a macro

Just saw an Excel newsletter post from Kevin Jones from https://www.dataautopros.com/about-us/

He found that turning off the VBA interactive setting can speed up some macros. I tested it on a one minute macro and it cut it down to 40 seconds.

Worth a try if you have a longer running macro. You can add it to your opening and closing routines.

Code to turn it off

Application.Interactive = False

And then turn it back on at the end of your code.

Application.Interactive = True

Thanks Kevin for sharing.

Related Posts

Name box shortcut

Just found out you can double click the re-size control on the Name Box. This quickly shrinks the Name Box width.

I have no idea how long that feature has been there, but I have just found it. Woohoo!

Related Posts

 

The Proof is in the Plants (Penguin, 2021)

A great book that brings together all the science that supports a plant-based diet.

The book is easy to read and accessible with lots of good analogies.

Plant foods are great for you and the planet and you can read about the science that backs that up.

The book pushes a mostly plant-based diet. It doesn’t push vegan or vegetarian. Being mainly plant based is the aim. The more the better.

Highly recommend the book.

I follow the Plant Proof podcast.

Open the Power Query Editor

If you have cell selected in the output table from a Power Query, you can press, in sequence (not held down) the following keys Alt P U E to open the Power Query Editor window.

Related Posts

 

FREE Excel Introduction to Power Query

Duration: 1 hour

Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources.

Learn the basics, plus an advanced technique to automate data cleansing routines on your data sources.

This session covers

  • fixing dates so that Excel can recognise them
  • formatting columns as text – retaining leading zeroes in CSV files
  • deleting unwanted rows and columns from your data
  • removing leading and trailing spaces
  • populating blank values with zeroes
  • populating blanks with entries from above
  • correcting trailing minus signs
  • unpivot a report – converting a report layout into a table layout
  • converting a MYOB report into a data table
  • capture header information in a column

As always, I will share other tips and tricks along the way.

 

Date: May 13, 2021
Time: 1:00 PM AEST
Event: FREE - Introduction to Excel Power Query
Topic: Introduction to Power Query
Public: Public
Registration: Click here to register.

Data Validation Shortcut

I typically use the shortcut Alt A V V pressed in sequence (not held down) to open the Data Validation dialog.

I like it because you can do it one-handed. The A and V are close together.

There is another shortcut that works the same. Again, pressed in sequence and not held down. Alt D L

Use the one that is easiest for you.

Related Posts

Pivot Table Shortcuts

Here’s a couple of useful keyboard shortcuts for Pivot Tables.

Display/Hide the Pivot Table Field List – this list lets you create or change the Pivot Table.

Alt J T L  – pressed in sequence, not held down.

 

To add Subtotals above the entries in an existing Pivot Table.

Alt J Y T T – again pressed in sequence, not held down.

Related Posts

Expand the Formula Bar

The Formula Bar can be expanded using the icon on the end. But there is a keyboard shortcut as well.

You can expand it or return it to one line using the keyboard shortcut Ctrl + Shift + U.

Thanks to Excel MVP Tom Urtis for sharing this shortcut recently on LinkedIn.

Related Posts

Webinar – Dashboards #4 – PivotCharts and Slicers

Price; AU$25

This is the fourth in the series and focuses on creating a dashboard using PivotCharts with Slicers.

PivotTables are extremely powerful and allow you to summarise large data sets very quickly without using any formulas. PivotCharts are charts that are based on PivotTables and are extremely interactive. This dashboard will use Slicers to add interactivity to the dashboard so that users can change the dashboard.

The session includes

  • how a PivotTable works
  • creating a PivotTable that works well with a chart
  • formats for large values
  • making PivotCharts display better for dashboards
  • making changes to PivotTables to improve headings
  • sorting and Top 10 reports
  • use Slicers to control filters on multiple PivotTables
  • using the GETPIVOTDATA function to create specify reports/charts

As always, I will share other tips and tricks along the way.

Date: April 23, 2020
Time: 1:00 PM AEST
Event: Dashboards #4 - PivotCharts and Slicers
Topic: Dashboards #4 - PivotCharts and Slicers
Public: Public
Registration: Click here to register.