Blog Articles

//Blog Articles

Power Query Data Cleansing – solving all sorts of Excel issues

by Wyn Hopkins

Power Query Data Cleansing Power Pivot

 

Some may see Power Query simply as a brilliant way of connecting to multiple data sources and loading that data into a Microsoft Power BI development environment (Excel Power Pivot or Power BI desktop).

Having spent 25 years using Excel / Lotus 123 functions and macros to clean up messy data and automate manual tasks I find it is so much more.

It is a breath of fresh air.

There are lots of great people out there giving great advice on how to get the most out of Power Query and I salute them all. A big thank you.

Power Query data cleansing can solve all sorts of problems for the Excel user who is wrangling with data. Consolidating, Splitting, Merging, Replacing, Filtering, “UNPIVOTING!”… the list is comprehensive and seems to be ever growing.

Here’s just one small and very unusual example. It’s the “unusualness” that prompted this article. It hopefully goes to show that if there’s a problem that you don’t know how to solve using “normal” Excel then Power Query data cleansing may well be the answer.

The Problem

A table of data had been entered in Excel but rather than a unique row being used for each value the user has entered data using the Alt+Enter trick to transfer data onto the next line in the same cell.

Power Query data cleansing, Power Pivot

This makes it impossible to create totals (OK not impossible but you need an array formula and some “out-there” thinking to get it done).

Power Query Data Cleansing to the rescue….

1. Turn the original set of data into a Table (highlight and Ctrl+T)

2. Power Query > From Table

Power Query

3. Then click on the Split Column > Split by Delimiter

And select the Custom Option followed by ticking the box Split using special characters and pick Line Feed (this picks up the Alt+Enter separator)

Power Query data cleansing Line feed characters

(note I’ve put 100 columns to split, this allows for 100 items to be entered in each cell, there are better but more complex ways of doing this)

4. Right Click on Types of Uniform and Unpivot Other Columns

Power Query data cleansing - unpivot

5. Right Click on and Remove the Attribute Column

Power Query data cleansing

6. Right Click on the Types of Uniform and select Group By…

Power Query data cleansing - group by

7. New Column name = “Total Sold” Operation = Sum Column = Value

Power Query data cleansing

8. Click Home > Close and Load To…. then select table and load the data into Excel

Power Pivot

Done! Simply Right Click and Refresh the green table whenever required

Power Query Power Pivot

Ideally, we’d avoid all this and explain to the user why using Alt+Enter, in this case, is not a particularly good idea.

The aim of this unusual example is just to get you thinking that Power Query may well be able to help you ways you never thought were possible.

Power Query to SUM values Separated by Alt-Enter

How to Implement Power BI

If you want to implement Power BI, as with any system implementation, you need to firstly define the business goals and objectives up front and break the project down into a series of phases.  Don’t try to do everything at once.

To implement Power BI well, each phase should clearly define the key things the business wants to measure as well as what they’d like to analyse each measure by e.g. sales: by product group, location, salesperson, month/quarter/year etc.Power BI Implementation

They don’t need to detail each report or chart as these can be easily added at any stage, even by the user but they do need to know broadly what they want to get out of each phase.

Once the requirements are known, the system implementer can investigate where the data is, how clean this is, how will Power BI gain access to the data, and how much effort is involved in extracting and transforming the data.

Communicate with users to ensure they’re aware of the Power BI project, understand what it is intended to do and what it will do for them, and provide the appropriate level of training for all users (e.g. minimal training for consumers of information and in-depth training for data analysts).

Provide resources that can assist when users encounter problems or have questions e.g. help desk, online resources, and/or access to external assistance.

Lastly, when you implement Power BI, make sure you highlight the success stories throughout the company as they occur to inspire users and encourage adoption.

Best practices when you Implement Power BI

In addition to these principles, there are a few best practices to follow when implementing Power BI:

  • Use Group Workspaces so that no one person is responsible for all the reports.Implement Power BI
  • Use the Enterprise Gateway rather than the Personal Gateway so that users’ security credentials are retained for access to databases
  • Use Content Packs to ensure that reporting and security standards are retained
  • Use friendly names for tables and columns
  • Hide unnecessary fields
  • Include a measure tables for every fact table
  • Build DAX formulas in component steps
  • Test the resulting visualisations by examining individual records and tracing the calculations through

Help for when you Implement Power BI

If you’d like some help or assistance with implementing Power BI, just leave your details below or call us on +61 8 6210 8500

 

Get in Touch

Introducing Power Pivot 

In this video I introduce Power Pivot and how you can use it to join multiple tables of data together and display your data in a Fiscal Year format.

Power pivot, power query

In part 1 I demonstrated how Power Query can quickly consolidate all of the files in a folder and bring that data into Excel.

In part 2 I followed on from that introduction and added an extra transformation step to get the dates I needed.  I then converted the data into a Pivot Table and Pivot chart and added some user-friendly Slicer filters.

 

 

If you missed part 2 take a look here…

http://accessanalytic.staging.wpengine.com/faq-items/power-query-get-transform-part2/

Supporting Files

Download Source CSV Files if you’d like to try this out yourself

Information

For more information  on Power Query and Power BI then please read this article

Training

For training courses in Power Query and Power BI please click here

Top 10 reasons why winning CFOs are flocking to Power BI for data analysis

“A (data analysis) picture is worth a thousand words”
(Frederick R. Barnard – 1921)

“Time is Money”
(Benjamin Franklin – 1706 to 1790)

The above truths are even more relevant in today’s real-time business environment. Those who make the smart decisions fastest, are the winners and data needs to be actionable as soon as it’s created.

This is where Power BI comes in.

Power BI is a suite of analytic tools that enables you to monitor your business, analyse data, share insights and get answers quickly.

Here are the top ten reasons why winning CFOs rely on Power BI to stay ahead:

1. Dashboards
With all the key metrics in one place, the Power BI dashboard gives you an instant snapshot of the most critical business data.

Tedious scrolling and switching between windows, applications, drives and even servers to get the full picture, sucks up critical time which should instead be spent on value-adding activities.

Power BI collects the information for you and presents it succinctly, to save time and expedite the decision-making process.

To view underlying data, users can drill down to the detailed level with one click on the dashboard.Power BI, Data Analysis

2. Rich Visuals
Scientific research shows that 90% of the info transmitted to our brains is visual and that we process visuals 60,000 times faster than text.

Power BI allows for otherwise mundane data formats to be represented in a fresh, engaging manner.

It brings data to life, thereby enhancing the receptiveness of your team, a great starting point for meaningful collaboration.

3. Anywhere, anytime data analysis
The digital age which we live in requires 24/7 accessibility to business information when we have the need to do so.

Power BI makes it possible.

Via its built-in gateway, Power BI continuously delivers updated data from databases in your office or the web to the desktop canvasses and mobile devices of your choice, thereby ensuring crucial information is always available at your fingertips.

4. Self-service
There is no need to involve IT staff in either the setup or day-to-day use of Power BI.

With a little training, business users can access data for themselves and turn it into the valuable information and analyses needed for decision-making.

Power BI is therefore a true enabler which greatly enhances efficiency, reduces costs and expedites objectives.

5. Flexible and holistic data analysis
Power BI replaces outdated Excel functionality that was limited in scope, difficult, unreliable, and hard to use.

It provides business users with a suite of tools that are far more flexible, powerful and holistic, which can access a far wider range of data sources. Almost any data source is now possible!

Furthermore, these tools are available in both Excel and Power BI Desktop. This allows Excel users to easily transition to the new Power BI design tools plus it reduces the complexity of using these.

6. Shallow learning curve
Power BI is browser-based and employs a lot of Excel syntax. This makes new users feel at home quickly and lets them become highly productive very quickly.

7. Negligible licence fee
There’s a feature-rich free version available which is sufficient to get started quickly.

The paid version is best for groups and sharing information. Its powerful features are available for just $13 per user per month. Some organisations already own all the licenses they need as part of their Office 365 suite.

8. Rapid iteration
Simply sign up to Power BI and start turning your data into useful information for decision-making.

There is no need to spend months on rolling out and documenting specifications. This reduces the “lost-in-translation” issues and cuts the development period, saving you time and money.

This means you’ll be enjoying the benefits of Power BI much faster.

9. Support & integration
Power BI is supported by Microsoft and integrates tightly with the Company’s other applications and products.

This ensures peace of mind as far as the integrity, reliability, relevancy, future development and outlook of the tool is concerned.

10. ConnectorsPower BI, Data Analysis
So that you can hit the ground running, Power BI comes with pre-built dashboards and report packs for many commonly used systems and platforms, such as:
• Microsoft Azure
• MailChimp
• Oracle
• Microsoft Dynamics
• QuickBooks Online
• Xero
• and Google Analytics
… to name but a few.

If you wish, you can start with any of the connector dashboards and get some powerful analysis up and running within minutes. Alternatively, because they are highly customisable, you can tweak the pre-built dashboards to your liking.

You may also, off course, ignore the connectors altogether and start from scratch by designing and developing your own content pack.
So, whichever way you need your data sliced, diced, mashed and presented, Power BI is up for it. Give your business the intelligence advantage with Power BI.

Power Query (Get & Transform) – Part 2

In part 1 I demonstrated how Power Query can quickly consolidate all of the files in a folder and bring that data into Excel.

In this 6 minute video I follow on from that introduction and add an extra transformation step to get the dates I need.

Once I have the required data I convert it into a Pivot Table and Pivot chart and add some user-friendly Slicer filters.

 

If you missed part 1 take a look here…

Power Query (Get & Transform) – Video Introduction Part 1

 

Supporting Files

Download Source CSV Files if you’d like to try this out yourself

 

Information

For more information  on Power Query and Power BI then please read this article

 

Training

For training courses in Power Query and Power BI please click here

 

Power Query (Get & Transform) Part 1

Microsoft Power Query Connections

Power Query is amazing at linking to data sources and cleansing / transforming that data as it’s loaded into Excel (or Power BI Desktop).

Watch this 4 minute video and see how you can consolidate all of the files from a folder instantly using Power Query.

Build the transformation steps once and then every time you need to reload data you just RIGHT-CLICK refresh!

 

Supporting Files

Download Source CSV Files if you’d like to try this out yourself

 

Information

For more information  on Power Query and Power BI then please read this article

 

Training

For training courses in Power Query and Power BI please click here

 

 

 

 

Dependent Drop Down Box

3d-dependent-drop-down-normal

 

How to do one of those trickier tasks in Excel: set up a drop down box that is dependent on the result of another drop down box.

The scenario

If coffee is selected from drop down A then I want a list of coffee types to appear in drop down B. If Tea is selected in drop down A then I want a list of tea types to appear in drop down B.

In the real world these lists may be product type and sub-products, departments and employees, account groups and sub accounts etc.

I’ve saved a file here for you to download and see how I’ve done it.

demonstration of secondary drop down box.xlsx

Step 1: Set up your primary and secondary tables

Note how the primary table has a column containing the names of the associated secondary tables

Step 2: Set up your Primary Drop Down List called ddDrink

To do this highlight the 3 cells Tea, Coffee, Juice and go to the Name Manager Box above column A and type ddDrink and then press Enter

Step 3: Set up your Primary Drop Down box

Click on a cell, lets say G21 and select Data > Validation > From List and type =ddDrink in the source box

Step 4: The trickier bit : Set up your Secondary Drop Down list

Here’s the trickier part

Let’s say we want our secondary drop down box to be right next to our primary drop down.

So we click on cell H21, and now we are going to create a named range called ddSecondary

To do this, click on Formulas > Define Name and name it ddSecondary

then add this formula to the Refers to: box

=INDIRECT(  INDEX(  tblDrink

[Secondary Table],MATCH( G21, tblDrink[Drink],0)))

That’s a pretty nasty looking formula when looked at in one go.

Ignoring the INDIRECT part for a moment, the main element is an INDEX MATCH formula which I’ve written numerous articles on and even made a video about.

The INDIRECT part is the “clever bit” in that it indirectly gives you the table name to be used for the second drop down.

For example, if Tea is selected in G21 then the INDEX MATCH part looks up the word Tea in the Drinks Table and then returns the corresponding Secondary Table name of tblTea.

The INDIRECT part then uses the reference to tblTea to provide a list to be used by the Secondary Drop down box.

Not easy to understand on first pass, Give it a try and feel free to ask for help.

Step 5: Set up your secondary drop down box

Click on cell H21and select Data > Validation > From List and type =ddSecondary in the source box

Step 6: You’re done!

A very useful extra step is to add conditional formatting to flag invalid combinations such as Flat White Tea or Apple Coffee

To see how I’ve done that take a look at the demo file, essentially I use a COUNTIF formula in the conditional formatting.

demonstration of secondary drop down box.xlsx

 

aaeaaqaaaaaaaagvaaaajdrjodhjzjawltvhntgtndfkos1iztczlthlmgflmzyznmjlzg

Easy Cumulative totals in Tables

The single greatest advancement in Excel in the last 10 years was the introduction of Tables.  Yet although Tables were introduced in Excel 2007 there is still a huge number (I’d even say the majority) of Excel users that don’t know how to create tables or understand what they do.

This is a brief introduction

Click in a cell in a block of data (to be super safe highlight the entire block)

Press CTRL + T

Ensure that My Table has headers is ticked, and press OK

You now have a Table.  There are 10 or so great features of Tables but here are the 3 key ones for me…

  • Tables expand automatically when you type at the bottom or to the right. This is fantastic for those using Pivot Tables or Data Validation.
  • Formulas “auto-fill” up and down the table whenever you enter a new formula or amend an existing one.
  • INDEX MATCH works brilliantly with Tables

If you’ve never used Tables but you do use Excel to analyse “tables” of data then you are missing out on a huge opportunity

 

Cumulative Totals in Tables

One frustration I do have though is that cumulative totals in tables in a column are not straightforward.

In “normal” Excel to get a running total you just add the cell above to the cell on the current row, and that’s it.

For a table, with its structured references and headings this proves problematic.

Also, ideally you want a running total that works when you insert rows into the Table or add lines of data to the bottom of it.

unfiltered

So here are 3 options:

Option 1: If the heading of the Units Sold column is F6 then use =SUM(F$6:@

[Units Sold])

Option 2: A little more advanced since the SUM approach has a drawback.

If you filter the table for February you still get the YTD cumulative rather than just the February Cumulative

 

Cumulative totals in tables

 

An alternative is the little known AGGREGATE function (you could also use SUBTOTAL).

=AGGREGATE( 9, 7, SUM(F$6:@[Units Sold]) )

  • The 9 means SUM
  • The 7 means ignore Hidden Rows AND Errors

So when the data is filtered the rows are hidden, and the Aggregate works nicely. Even if one of your values was an Error e.g. #VALUE or DIV/0 the cumulative would ignore it and the total still works.

This ignoring of elements can come in really useful, but just be careful that you really do want to do this! Spotting errors are normally a good thing.

 

Option 3: For large data sets (5,000 + rows) performance starts to be an issue with options 1 and 2.  So for these bigger tables of data an alternative approach would be better.

Select cell A2 and then create a defined range name called “RowAbove”.   In the Refers to field, enter the formula =N(A1)

rowabove

Your cumulative formula in the Table can be entered as =RowAbove + [@Units Sold]]

This works because the N function handles the fact that for the first row the “row above” is the heading and the N turns text to 0

Hope you find this useful!

 

 

Custom Visuals - Power BI - 2 of the best

One of the great things about Power BI visualisations is the fact that they are open source and clever generous folk are developing fantastic tools for free that can help us all deliver a more enjoyable experience to the end users.

Custom Visuals Gallery

Two of my current favourites are both by OKVIZ and are beautiful in their simplicity

1 Smart Filter
smartfilter

 

http://okviz.com/smart-filter/

2 Card with Status (States)
cardswithstates

 

http://okviz.com/card-with-states/

If you are into Excel and you haven’t started exploring Power BI yet then you’re missing out.

If you are into Excel and you haven’t started exploring Power BI yet then you’re missing out. The Power Query and Power Pivot technology that is now embedded into Excel 2016 (add-ins available for earlier versions) is the engine behind Power BI Desktop.

Power BI Desktop provides you with an awesome “dashboarding” layer for information sharing.

Once you’ve built a dashboard in PowerBI Desktop then a couple of clicks allows you to publish this to PowerBI.com and all of a sudden your reports are available on any device (laptop, tablet, mobile) to anyone you’ve given access to.

Why Power BI is our friend

The real killer feature of Power BI is that it builds on those Excel skills that have taken so long to develop and encourages Excel users to take extra steps to really make their skills shine.

All too often competitor BI tools demonise Excel as some evil make-do child’s toy that is no longer fit for purpose in this “Big Data” era. Far from it, Excel is the most widely used BI tool in the world and always will be, especially with the integration of Power Query and Power Pivot.

So try out Power Query (Get & Transform) and see how much time it can save you, then explore what magic is possible when you start loading data into Power Pivot, and finally step into the world of Power BI desktop and Power BI.com to start to share your hard work and insights with the rest of your department and organisation.

Its’ any amazing journey that will stand you in a great position for many many years to come.

VLOOKUP v INDEX MATCH v GETMATCH

INDEX MATCH is great but GETMATCH would be better

 

I’ve written a number of articles in the past around how INDEX MATCH is a technically better option compared to VLOOKUP.

However, it is a trickier formula and therefore not as widely used, so I’ve added a new formula suggestion to the Excel User Voice forum.

The Excel User Voice forum allows us users to have our say on what the future of Excel holds.

Please take 30 seconds to vote for my suggestion of a simpler, safer LOOKUP formula

Please vote here

***Thanks to everyone who has voted for this so far, we’re now in the top 10 ideas on Excel User Voice – please share / retweet etc to encourage others to vote and push this into the top 10 to make it happen ***

 

In the meantime here are 3 reasons why you should use INDEX/MATCH instead of VLOOKUP        

  1. You can “lookup” to the left easily
  2. You don’t get incorrect results when a column is inserted or deleted from your data
  3. When used in conjunction with Tables, the formulae are a lot more meaningful.

How to set it up in 60 seconds

But INDEX/MATCH is more complicated than VLOOKUP I hear you say,  and I agree. So here’s how to master it in 60 seconds

=INDEX( Step3_Result_Column, MATCH( Step1_Lookup_Cell,  Step2_Lookup_Column, 0),0)

Copy the above formula and paste it into your Autocorrect window (Excel – Options – Proofing – AutotCorrect Options)

I use iii in the Replace Box (see image)

Now whenever you need an index match, type iii,  AutoCorrect kicks in and you have a ready-made formula.  All you need to do is just double click on each part of the formula and then select what you need at each stage. I’ve numbered the parts to indicate the best order to do the “double clicking”.

Once you get the hang of this you’ll never need a VLOOKUP again

Also, here’s a link to vote for the Excel team to provide us all with a simpler lookup formula.  It ‘s almost at number 10 in the most requested changes so please VOTE! to push it further up the list.

Please vote here

Power Query Parameters and Named Cells

By Wyn Hopkins

Power Query is the best thing to happen to Excel since Tables were introduced in Excel 2007

When developing Power Query solutions for others to use, or when needing parameters that can be changed easily (e.g. source file or date) you can store these variables in named cells and reference them via Power Query parameters.

Unfortunately it’s not super straightforward, but nor is it super complex.

For example, in the image below, I’m pulling a simple 3 row table into this file from another Excel file, however I’d like to allow the user to change the File Path in the orange cell click refresh and hey presto the data gets pulled in from the new file.

1 Data from 2nd File for Power Query Parameters

 


For those of you comfortable with Power Query here’s the simple trick I used to achieve this (avoiding steps 1 to 7 below).

I use Autocorrect to store this piece of Power Query Formula (including the comma at the end) as pqname

FilePath= Excel.CurrentWorkbook(){

[Name=”FilePath“]}[Content]{0}[Column1],

Then, whenever I type pqname into Excel, this formula appears which I copy and paste into my Query.


 

If you’re doing this for the first time then here’s the steps I go through to get this formula

FilePath= Excel.CurrentWorkbook(){[Name=”FilePath“]}[Content]{0}[Column1],

 

1. Firstly I name a cell as FilePath  (or whatever parameter name is suitable)

 

 

2. Data > Get & Transform > From Table    (I know it’s not a table but it works!)

 

 

3. Delete the 3rd and 2nd steps in the Query editor

 

 

4.Right-Click on the Text in Column1 and select Drill Down

 

 

5. Then go to the Advanced Editor

 

 

 

6.  This is what the Power Query code will look like

 

 

7. Amend the Code to look like this.

 

Essentially you are removing the words Column1 = Source from the second line.

Make sure you keep a comma on the end

Highlight and Copy this single line of code  (see that this is the code I store in my Autocorrect settings to save me the hassle of doing the above steps every time)

Close out of that window and discard any changes (don’t save it)

 

 

8. Then go into your original query (Right-Click Edit)

 

 

 

Then paste your code to the first line, and amend the 2nd line of code to refer to the word FilePath rather than the full file path text string.

 

Click Done and you now have a parameter feeding your query rather than a hard-coded piece of text.

This can be applied to all sorts of steps in Power Query including filtering data by certain parameters, and replacing certain characters etc.

 

If you have any thoughts or comments, let us know.

 

 

Excel Warning Box

By Wyn Hopkins

In a nutshell: Save yourself a lot of wasted time and turn off DropBox badges….

If you ever experience a VBA Password box appearing after you close Excel and then the dreaded “Microsoft Excel has stopped working” message then there may be a simple solution……

After much debugging and digging around the internet I finally solved the issue I was having and it came down to one (well two) culprits…   DROPBOX!   and UserForms.

The files I was having problems with all had VBA UserForms in them (used to enter passwords) and protected VBA code.  These two things coupled with having Dropbox for Desktop on my computer were causing Excel to crash every time I closed it.

I’m not alone I discovered, and DropBox have been unable to fix this for a year now.

DropBox Forum article

 

There is a simple fix to stop Excel from crashing and it involves turning off DropBox badges.  I’m not even sure what they are, but turning them off makes me happy so that’s what I’ve done…

 

PreferencesTurnOffBadges

 

DropBox help – how to turn off badges

 

I hope this saves you the hours of wasted time I’ve spent trying to debug my VBA code.

 


 


 

Please leave this field empty.

Fix MYOB Account Numbers Exported to Excel

It used to be simple to export data from MYOB AccountRight to Excel.  With more recent versions, the export has changed which means all your formulas looking for particular account numbers won’t find them.  Here’s how you can fix MYOB account numbers.

The Cause

When MYOB exports its account numbers, it actually adds a non-printable character to the end of every account number.

This isn’t visible in Excel, so it looks like everything should match … but it doesn’t.

That’s very frustrating because all your MATCH/VLOOKUP/HLOOKUP/INDEX formulas stop working!  Grrrr!

You can fix this by using the CLEAN function in Excel.

This removes all non-printable characters from a cell and leaves you with just visible characters.

How to Fix MYOB Account Numbers

Download the file below for a quick worked example.

Free Download - fix MYOB account numbers exported to Excel

 

 

 


 


 

Please leave this field empty.

Mischievous Cat

This eBook has some great ideas on how to have more fun in Excel than you can poke a kitten at (usually at your colleague’s expense!)

*evil laugh*

If you’d like some ideas for some mischievous adventures that will mess with your friend’s head (and their models), download this now!

You’ll learn things like:

  • How to automatically change random words
  • How to make sure your colleagues never leave their computer unattended again
  • How to make it appear that all the drawing objects in a workbook have disappeared
  • How to change all the formulas in a workbook
  • How to stop someone from entering data into cells, even though the file is unprotected
  • How to make your computer read everything you type back to you.
  • How to popup alarming messages

Although all these things can be used to cause mischief with your colleagues, please only use them for good!

Fortunately, the eBook also suggests ways in which each function can be beneficial.

Three more things:

  • Don’t turn to the dark side!
  • The kitten isn’t your father
  • These aren’t the droids you’re looking for

Free Download

 

 

 

 

 


 


 

Please leave this field empty.

Excel ShortcutsA collection of our favourite keyboard
shortcuts in Excel.

Pin it up on your wall & share it with your friends.

Shortcuts to save time and maximise productivity including:

  • Creating charts faster
  • Faster formatting
  • Navigating a large spreadsheet
  • and 19 more!

Free Download

 

 

 

 

 

dual monitors

By Wyn Hopkins

This simple tip may save you a lot of time and effort, but it must be used carefully.

With the arrival of Excel 2013 we were finally able to easily split Excel between two screens and happily copy and paste between the two with full paste capability and without the worry of worksheet corruption.

If you weren’t aware of the worksheet corruption risk see this article https://accessanalytic.com.au/faq-items/why-multiple-monitors-might-be-killing-your-spreadsheets-2/ 

However, sometimes it is extremely useful to have 2 separate sessions of Excel running for the following reasons:

1. You can interact between files when certain Excel editor windows are open.  For example copying code between two Power Query Editor windows.

2. Each session of Excel can use the 2GB (soon to be 4GB) of RAM available rather than sharing it.

3. Safety: If you’re working on a critical file and you need to look at something else then start a new session.  Then if that crashes you still have your critical file up and running in the other session.

How to initiate a 2nd Excel session in Excel 2013 / 2016

The key is Alt (literally)

Alt Key

Right-Click on your Excel icon then hold Alt while you Left-Click on the program launch icon.

Keep Alt held down until you see this

alt key 1

Click Yes and you’re done

This technique has saved me huge amounts of time, and hopefully it can do the same for you.

There are lots of ways to add a new data series to an existing Excel chart.  Here are what we suggest are the two easiest ways:

Method 1: Copy & Paste

Great if your data series aren’t in a table or aren’t adjacent to each other.

1. Select your data (including the heading) and copy this (Ctrl+C).

Add data to an existing chart

2. Click on your existing chart, then press Ctrl+V to paste it into the chart.

Add data to an existing chart

Method 2: Expand your Table

If your chart is based on data in a table (like in the example above), it gets even easier.

Just add a new column to your table and the new series will be automatically added to your chart!

Even less work!

 

That’s it unless you want to do any other amendments e.g. like plotting it on a secondary axis (as per the example above)!

Simple!


 


 

Please leave this field empty.

Digital disruption is changing the world

Infographic: How Digital Disruption is Changing the role of the CFO

How digital disruption is changing the role of the CFOAny CFO that still thinks their main role is to produce accounts and lodge tax returns will soon be out of a job.

Digital disruption is changing the world’s economy, companies, and every job function they contain.

We surveyed a number of CFOs and collated research from other experts around the world to produce this infographic.

The CFO’s role has Transformed Significantly

Although 84% are focussed on long-term cost reduction, CFOs cannot remain as “bean-counters and compliance managers” any longer.  The real focus should be on what “moves the needle”, which requires the CFO to firstly know what this is!

In a world of rapid change and innovation, it’s also no longer acceptable to simply follow what was done last year, or use last year’s annual budget.

73% said their primary role is now to act as the strategic financial partner assisting the CEO to navigate an increasingly complex landscape.

What does this mean?

This means:

  • New responsibilities.
  • Increased prominence within the organisation.
  • Providing advice and analysis to support critical business decisions.
  • Increased importance on internal and external partnerships.

Get the data!

To fulfill many of these responsibilities, the CFO needs to ensure the organisation has better systems that analysts can use to gain access to data that is accurate, timely, reliable and integrated.

Indeed, 82% of CFOs said they could see significant value in integrating enterprise-wide data and using this for analysis purposes.

The data could reside internally or externally and encompasses both financial and non-financial information.

The analysis of this often requires new approaches to data capture, analysis and synthesis, using new, best-of-breed software tools.

It’s no longer sufficient to leave important business information sitting in individual silos.  This needs to be integrated with other business information and made available in a controlled manner so that users can draw insights from it.

Free Download

 

 

 


 


 

Please leave this field empty.

58 Crazy Excel Stories (that are probably happening in your company right now)!

Special kind of person to handle my kind of crazy

Crazy things happen when you mix untrained users with Excel.

It’s not really rocket science.  Crazy things would happen with any complex system if you don’t train people properly.

But many companies simply assume that everyone knows Excel, so they don’t need much (if any) training.

Users compound the problem too because if you asked most people, they’d describe their Excel skills as “Intermediate”, because they’re comparing themselves to those around them.

We recently surveyed our LinkedIn connections to get their feedback on some of the things that actually happen everyday in offices around the world.

The results were frightening, surprising, saddening and at times, hilarious too!

So many people are wasting so much time because they don’t know incredibly simple things in Excel.

Executive Summary & Key Themes

We’ve grouped the stories submitted under the main topics and have summarised the recurring themes for each below.

If you want to both laugh and cry, read some of the actual stories together with our comments & recommendations in the eBook.

  • Wrong Tool for the job: Using Excel for everything, including Word processing and attempting to make a relational database was common!
  • Misusing VBA Macros & being unaware of basic functionality
  • Misunderstanding of Data: users spending countless hours reformatting or re-keying data that could have been very simply manipulated with a basic Excel formula or function.
  • Pivot Table Problems: poorly understood by most users hence they often spend huge amounts of time building & replicating Pivot Table functionality.
  • Formula Difficulties: using incorrect or inefficient formulas was common, resulting in huge inefficiencies plus few people ever review key spreadsheets.
  • Formatting Deficiencies: manually applying formats or misusing formatting was quite common.

Conclusions

  • Excel is the most complicated piece of software most users will ever encounter.
  • No one automatically knows how to use something new and if they learn on their own, they won’t learn the most efficient ways of doing things.
  • As such, all users need an appropriate level of Excel training.
  • “Training” includes a wide variety of learning:
    • In-house or public instructor-led training courses, & one-on-one mentoring
    • Online training courses
    • Informal “lunch & learn” type sessions to share & re-iterate Excel shortcuts among staff internally
    • Books and YouTube videos on Excel topics
    • Experiment: click on buttons to see what they do & Google to find out more
    • Online discussion forums & blogs
    • Start today!

Free Download

I recently was asked if there’s a way of adding the Celsius symbol to a value in Excel.

The answer is yes, and the method can be applied to all sorts of useful symbols / notation.  e.g. bullet points, m2, km/hr, bbl, currency symbols etc.

This is how….

Pick a blank cell then go to the Insert ribbon and select Symbol then find Superscripts and Subscripts and select the O symbol and click Insert followed by close.

Newsletter Pic 1

You should now have the superscript O in a cell, just type a capital C after it.

Highlight these 2 characters and copy them (you can use Ctrl+C). Then press Esc.

Now you have 2 options, depending on whether you want to add this to a value or a piece of text.

Option 1:  Number Formatting

Right-Click in the cell you want to format and select Format Cell > Number > Custom and in the Type: box enter this

0.0⁰C;  

[Blue](0.0⁰C);   0.0⁰C

(Note the ; separates how you format Positive; Negative; Zeros)

Newsletter Pic 2

Option 2:  Autocorrect for adding it to text

I’m a fan of Autocorrect and use it for all sorts of tricks (see here for how to create an INDEX MATCH formula using it)

As for Option 1 you first need to copy the Superscript O and C using Ctrl+C then press escape.

Now go to Autocorrect (via File > Options > Proofing > Autocorrect)

Newsletter Pic 3

In the “With:” box do a Paste (Ctrl + V)

In the “Replace:” box type  dc  or any other shortcut that you aren’t going type for any other reason. Then click Add.

Now when you are typing you can just write “the temperature was 6 dc ”  and the dc will Autocorrect to your symbol.   Once you close Excel and open Outlook or Word or PowerPoint this Autocorrection will work in those applications too.

I frequently use this trick to enter bullet points in Excel.

Power Query Trap:  Formula Autofill

If you output the results of Power Query to a table in Excel then it’s often really useful to be able to add an extra calculated column or two onto the end of this output table.   I used to do this all the time when using Microsoft Query.

However, now I’ve switched to Power Query I came across this Power Query trap (and risk) that had me stumped for a little while but a little trial and error eventually provided a solution.

So here’s the scenario. We have a source table which we reference using Power Query and then output to another table in the same Excel file (I’m not doing any transformations in this example to keep it nice and simple)

Power Query Trap Formula Autofill

Here’s the output table, and I’ve added an extra column called Formula (= Code x Cost )

Power Query Trap Formula Autofill

If I add a new line to my Source Table, let’s say we add 6 and 60 to the blue Source Table, then, when I refresh my output table I would hope and expect my Formula to copy down automatically.

IT DOESN’T!!!

Here’s the refreshed output and the formula is missing!

img03

This is not good, in fact it’s downright dangerous and briefly depressed me as how could such an amazing tool as Power Query not work as well as the old MS Query?

Undeterred I thought there must be a way to fix this….

And here it is….

Right Click in the output table and select Table > External Data Properties

Tick the box “Preserve column sort/filter/Layout

Power Query trap screenshot

Click OK

I’d recommend you then copy your formula down the entire column again before trying the refresh (it’s not always necessary to do this but better safe than sorry).

Now when you refresh the formula and formatting will copy down nicely.

Power Query Trap Formula Autofill

This should be the default setting in my view.

Get in Touch


 


 

Please leave this field empty.

How to combine multiple tables with Excel Power Query

If you have multiple tables of data in a file and you want to view a single report based on these tables then it can be time consuming and risky to manually copy and paste them into a single table before creating pivot table.

Here’s some steps to do this smoothly with Power Query.  I’d recommend downloading the Excel file first so you can step through the process.

Free Download

The great advantage of this approach is once you set it up then every future update just requires a single RIGHT-CLICK refresh on the pivot table and hey-presto – immediate update!

The screen shots are from Excel 2016 where Power Query has now been re-labelled as Get & Transform.  However, it works in exactly the same way as the Power Query add-in for Excel 2010 + 2013.

In this example we have 4 tables of monthly data.  Each table represents the sales from a different region.  We want to combine these 4 tables into a nice easy to manage Pivot Report.

From this…

How to combine multiple tables with Excel Power Query

 

to this….

How to combine multiple tables with Excel Power Query

 

Step 1:   Click inside one of the tables (I’ve selected tblNorth) and select Power Query > From Table

How to combine multiple tables with Excel Power Query

 

Step 2: Delete the Changed Type step

How to combine multiple tables with Excel Power Query

Step 3:  Click on Append Queries, and select the current query again. This doesn’t really make sense at this stage as why would you want to append the table to itself?  All will be revealed very shortly…

How to combine multiple tables with Excel Power Query

Step 4:  This is where it gets interesting, we now need to edit the M code that Power Query has generated for us.  This is a bit like stepping into the VBA code after recording a Macro.

Click on Advance Editor

How to combine multiple tables with Excel Power Query

 

Here’s the code as it stands….

 

let

Source = Excel.CurrentWorkbook(){[Name=”tblNorth”]}[Content],

#”Appended Query” = Table.Combine({Source, Source})

in

#”Appended Query”

 

Let’s take a look at this in detail.

Source is just a name that Power Query gave to the data held in tblNorth

The Append function then uses Table.Combine to join Source with Source (i.e. joins the tblNorth data to tblNorth)

 

We can now use this basic layout to create some “M” Code that can combine all 4 tables in one go…How to combine multiple tables with Excel Power Query

Here’s the amended part

 

Source1 = Excel.CurrentWorkbook(){[Name=”tblNorth“]}[Content],

Source2 = Excel.CurrentWorkbook(){[Name=”tblSouth“]}[Content],

Source3 = Excel.CurrentWorkbook(){[Name=”tblEast“]}[Content],

Source4 = Excel.CurrentWorkbook(){[Name=”tblWest“]}[Content],

#”Append Query” = Table.Combine({Source1,Source2,Source3,Source4}),

 

 

That’s it for amending the M code so we can now click DONE

 

 

Step 5:  The data from all 4 tables should now be displayed.

If one big table is all you need then you are done and you can skip to step 8. This layout however, with months across the columns, does not work well with Pivot Table reports.

It would be much better if the dates were all in one column.  This is where the AMAZING Unpivot functionality comes in.

Highlight the first 3 columns (non-date fields) and select Transform > Unpivot Other Columns

How to combine multiple tables with Excel Power Query

How to combine multiple tables with Excel Power Query

 

Step 6:  All your dates now appear in a new column called Attribute.

Format this as Data Type > Date

How to combine multiple tables with Excel Power Query

 

Step 7:  Now rename your “unpivotted columns” as Date and Sales, plus rename your Query as Combine Tables

How to combine multiple tables with Excel Power Query

 

Step 8:  At this point you can load your data.   Click on the DROPDOWN under Home > Close & Load

Then choose Close and Load To…

Close and Load to

You could load the data to a Table in Excel, or into Power Pivot however here’s one more trick that‘s available. Select Only Create Connection then click Load.

How to combine multiple tables with Excel Power Query

 

Step 9:   We can now access that Query (Connection) directly via a Pivot Table

Go to Insert > Pivot Table

Select Use an external data source (not that it really is external but that’s how this trick works)

Select your Query – Combine Tables

 

How to combine multiple tables with Excel Power Query

 

You now have a linked Pivot Table.

Drag Region into rows, Date into columns, Sales into values and then add a few slicers.

 

How to combine multiple tables with Excel Power Query

Whenever the source tables are updated you can just RIGHT-CLICK on the Pivot table and select Refresh to automatically run Power Query and load this new data.

Author’s comment…

There are several approaches to combining multiple tables, but as of writing (March 2016) this is the most flexible and least error prone approach we’ve identified.   Power BI Desktop has recently had an upgrade to allow you to choose multiple tables to append in one go, but until that arrives in Excel we will continue to adopt this approach.

 

Stop merging cells!

One Microsoft Excel Insider has been quoted as saying that 53% of Excel Users merge cells.

There is a standard tool on the toolbar called “Merge and Center”, it “merges” two or more cells into one and then centers the text horizontally within the merged cell.

This can cause a number of problems.

  • Copying and Pasting can fail.
  • Highlighting a single column of numbers can prove impossible.
  • Macros can hit unexpected issues.

There is a better alternative!   “Centre Across Selection”.

 

Merge Cells

Stop Merging Cells

 

  • Select a range of cells in a single row (as you would when merging cells).
  • Right – Click > Format Cells (Ctrl + 1 is the keyboard shortcut)
  • Click the Alignment tab
  • Click the Horizontal drop down arrow and select “Centre Across Selection”
  • Click OK.

This now looks like the cells are merged but actually you have maintained each individual cell but the value in the right hand cell is now “Centered Across the Selection”

Unfortunately, there is not a toolbar button for “Centre Across Selection”. To simplify using it you could create a style, or record a macro and then assign the macro to a toolbar button.


 


 

Please leave this field empty.

The Great 48 Excel Toolbar

We love Excel and it can do some pretty amazing stuff.

But sometimes there are a lot of unnecessary steps and clicks which are quite annoying, especially when you’re using Excel all day long!

So we developed this Excel toolbar for ourselves and now we’re sharing it with you.

Free Download

It has all the shortcuts we use every day including a lot of shortcuts we’ve written ourselves using VBA (Macro) code.

  • Ever been annoyed by the default Pivot Table layout – we have a button to fix that.
  • Ever found it painful formatting your numbers nicely    1,234,999 or (1,234,999) – we have a button for that.
  • Still using VLOOKUP?  Much better to use INDEX MATCH instead – we have a button for that.

So here’s The Great 48Access Analytic Toolbar … 48 of the best functions you’ll ever use!

Excel Toolbar 1Excel Toolbar 2

48 different buttons are a few too many to get your head around to start with so we’ve picked a few of our favourites from each section & highlighted them below.

Excel Toolbar Recommendations 1 Excel Toolbar Recommendations 2 Excel Toolbar Recommendations 3

So here’s the catch…..

  1. We ask to you to share this with at least 1 other person you know
  2. We ask you to let us know if you find any bugs or issues or suggestions for improvements.  This is a toolbar we use internally and we haven’t developed it as a commercial grade product,  however one day, if we get enough positive feedback we may well just do that.

Free Download

Get in Touch

 

Last Cell used on a Row

Find the reference of the last cell used in a row

By Neale Blackwood

One of the articles on our site allows you to find the cell reference of the last used cell in a column. You can see it below in the related articles.

Someone asked if there is a similar formula to find the last used cell in a row.

Well, it got us thinking and experimenting.

The answer for row 1 is below.

In Excel 97-2003

=ADDRESS(ROW(1:1),MAX(ROW(1:255)*(TRANSPOSE(A1:IU1)<>””)))

In Excel 2007+

=ADDRESS(ROW(1:1),MAX(ROW(1:16383)*(TRANSPOSE(A1:XFC1)<>””))) 

How to calculate the last cell used on a row

TRANSPOSE is used because the rows numbers go down rather than across and you need to convert the column entries to do the same, so the array will work.

How to Use this

There are lots of ways you could use this formula and the results it produces.

  • You may want to know the last cell used so you can calculate where to import next month’s data (e.g. in a VBA Macro).
  • Maybe you’d like to always read the right-hand figures into your report because these will always be the most recent.
  • Perhaps you’d like to chart the last data imported.  You could use this formula to always display the last column of figures, then point your chart at this.

… let us know how you’re using this!


 


 

Please leave this field empty.

Create a Waterfall Chart

by Yury Tokarev

A waterfall chart is a great way to represent financial inflows and outflows. Yet, it is a daunting task to create this in Excel versions up to 2013 (see links below for help!).

The good news is that Excel 2016 has taken all the complexity away. Below we show how to produce Waterfall in three easy steps.

Free Download

Here is how the final product looks…

PL Waterfall Chart

The underlying data for the chart is this…

Waterfall Source Data

And here are the 3 steps to replicate the chart…

Step 1: Select Ranges

While holding ‘Ctrl’ button select (multiple) ranges B42:C49, B52:C58. The multiple range selection is required to exclude ‘Total Operating Expenses’ line from the graph, as we do not want to show it.

Waterfall Source Selection

Step 2: Insert Waterfall Chart

Go to Insert/Charts menu, click on Waterfall chart icon and then select ‘Waterfall’

Waterfall Insert Menu

Step 3: Set total columns

Click twice the “Gross margin” bar on the chart to select it individually. Then right click and select “Format Data Point” from the context menu. This will display “Format Data Point” menu. On this menu select “Series Options”, then tick the box “Set as total”. Repeat this step for “Net Income”, “EBITDA”, “EBIT” and “NPAT”.

Waterfall Set Totals

Frustrated with spreadsheets?
You will be amazed by how we can help!
To find out more, contact Yury +61 423 588 212 or leave your details below
ytokarev@accessanalytic.com.au

Get in Touch

“OMG! They Fired our Excel Guru!”OMG! They fired our Excel guru

Unfortunately, this is all too common these days.

Companies, in their over-zealous quest to reduce costs, fire the one person who actually knew how to operate that really complicated Excel spreadsheet that’s critical to the report you need to send out next week.

What were they thinking!!!

Now what?

You have a few options:

  • Muddle through: this might enable you to get the report out, but it’s highly likely to cause problems later on, particularly if you accidentally change something that has an impact you weren’t aware of elsewhere in the file.
  • Hire or contract the person back: probably not allowed under company policy, even if you can find the person.  Companies in this situation usually have a freeze on headcount.
  • Get external help: quite possibly your only option!

External Excel Experts

They’re often a great solution to problems like this because:

  • No need to hire or fire anyone: keep your headcount static so you comply with company policy.
  • No long-term commitments: use as much or as little as you need, only when you need them and keep the cost down.
  • Greater Expertise: because they focus on Excel all day, every day, they often have a higher level of expertise compared to internal resources
  • Independence: they haven’t built the spreadsheet so they can provide you with objective advice
  • No politics: when they’re on your project, they don’t get pulled off by another manager to work on something “more important”
  • Reduce Risk: you won’t be person dependent again because it’s a company that stands behind the work they deliver, not an individual

How we help

  • Scope the work to be done
  • Provide a proposal for you to approve
  • Complete the work

If you like, we can be available on an as-required basis in future to provide help whenever you need it.

Get help!

If your critical Excel guru has gone or you just have some spreadsheets you need help with, call us on +61 8 6210 8500 or leave you details below and we’ll be in touch shortly.

Get in Touch

Hyperlink Me!

The HYPERLINK Formula (who knew that formula existed?) can be really useful when navigating around large spreadsheets

The simplest format is along the lines of

=HYPERLINK(“#A1″,”Any Helpful Message”)

This would jump you to cell A1 of the current sheet.  But that’s no different to a normal old Hyperlink (via RIGHT-CLICK > Hyperlink or Insert > Hyperlink or Ctrl-K)

Where it really comes in handy is if you want to make the link dynamic.

The Dynamic Hyperlink (now we’re getting tricky!)

This is a little complex but….

Lets say you have a very tall spreadsheet  made up of different sections.  A single formula at the top can allow users to jump to their section of choice (no macros!).

See the example (it uses  a match and address formula also but don’t let that put you off, they just combine to give the address of the cell you want to hyperlink to)

The user types or selects a section in cell C3 and then just clicks the hyperlink formula in D3.

The Hyperlink Formula

This is the formula:

=IFERROR(  HYPERLINK(“#”&ADDRESS(MATCH(C3,B:B,0),COLUMN(B:B),1),”Jump to Section”),    “Section Not Valid”)

Note:  I’ve wrapped it inside an IFERROR Statement in case the section name selected in C3 does not exist in column B.

Extra example:

Jumping to the bottom of a table

You can use the same approach to create a link to jump to the bottom of a table. For example if someone needs to paste new data each week to the bottom of a list then this quick hyperlink can save a lot of scrolling down…..

The Hyperlink Formula

Here is the formula:

=HYPERLINK(“#”&
ADDRESS( ROW(tblData

[[#Headers],[Values]]) + ROWS(tblData[Values]) + 1, COLUMN(tblData[Values])),
“Jump to End of Data”)

If that doesn’t make a lot of sense to you, don’t worry!  You can always use the simpler versions of HYPERLINK as outlined above.

 

Get in Touch

So firstly lets face facts.  Not many people need to bring 4 million rows of data into Excel.  Point accepted.

However, some do, and I recently needed to do this.

If you’ve ever checked  (Press End then the down arrow key) Excel has just over 1 million rows, but if you start adding a formula or two to those rows then you will soon have issues.

So how do you do it?

The Answer is PowerPivot!

Here is a screenshot to prove it.  4 million rows of data loaded into my data model from a year’s worth of transactions in a 1.7 GB text file.

 

How do you get 4 million rows of data into Excel? PowerPivot

 

This data had a column called Period and contained the numbers 1 to 12.   Which isn’t the nicest way to report data, much better to have the month descriptions.  So I then imported a Lookup Table containing the month names and linked these two data sets together (using a simple drag and drop).

 

How do you get 4 million rows of data into Excel 2   PowerPivot relationships

 

That’s the equivalent of 4 million VLOOKUPs calculated instantly.

Finally a quick Pivot Table and done….

 

PowerPivot results into a Pivot Table

 

4 million records isn’t actually that many for PowerPivot (especially not on a 64 bit machine).

The PowerPivot Pro Book shows how 141 million rows is not an issue.

The final Excel file compacted down to 90MB due to PowerPivot’s brilliant compression.  And if I’d removed some of the unique fields such as Batch Number then the compression would be significantly better again.

Find out More

If you’d like to know more about manipulating and analysing large data sets, call us on +61 8 6210 8500 or leave your details below & we’ll be in touch shortly.

Get in Touch

CFO secrets to analysing under pressure

CFO Secrets of Analysing under Pressure

The CFO and their Finance Department are under more pressure than ever before as a result of cost-cutting, staff cuts and an ever increasing demand for analysis to support business decisions like:

  • What are the cost synergies if we acquire X?
  • What if we cut projects A & B, but still keep C?
  • What if we change the business model like this or restructure it like that?
  • What are the implications of this pricing model vs that one for a new product/service?
  • How much cash is required if we pursue strategy A vs strategy B?

If it feels like you’re getting swamped with requests like these, you’re not alone!

CEBResearch and advisory firm, CEB, recently released some research that found a 10% increase in the volume of requests like these from 2014 to 2015.  An increase that is likely to be equal to or greater in 2016.

This isn’t surprising given that the CFO and their Finance departments are playing an increasingly important business advisory role during these times of economic stress because the need for decisions based on quality analysis is also rising dramatically.

Whereas in the past, business decision-makers may have been able to hide poor decisions in naturally rising sales and profits, this is no longer the case.

Quality decisions backed up by solid analysis are more critical than ever.

“It’s your job to Control Costs”

A recent survey by Barcanet found that 80% of companies around the world have cost reduction as a major strategic priority this year.

Furthermore, the C-level executives in those companies believe the responsibility for a cost reduction of up to 10% lies with the CFO.

The pressure is really on to find savings and get them done both quickly and correctly!

The Forecasting Paradox

This highlights the forecasting paradox: when business conditions are stable, it’s relatively easy to produce accurate analyses … but no one really needs these much because the business is so stable.

Conversely, when business conditions are volatile or difficult, it’s much harder and more time-consuming to produce accurate analyses (plus they’ve probably cut your staff numbers) … but everyone in the business wants these (yesterday, if not sooner!).

So what is a CFO to do?

Less Staff + More Demands = Creative Solution

Aside from trying to make Finance staff work longer hours (which only works for relatively short periods of time) the only real way for a CFO to solve this problem is to either employ more staff (good luck getting approval!) or make more use of consultants and “rent” the expertise you need on an “as-required” basis.

Using external consultants provides the CFO with a number of benefits:

  • No increase in head-count: their cost also comes from a different budget line
  • Significantly easier & faster approval process compared to hiring new staff
  • Access broad experience: consulting staff have very useful experience across a wide range of clients that they bring to your projects
  • No long-term cost commitments: turn them on or off when required without the need for hiring & firing
  • Focussed: the consultants have a single purpose while they’re working for you
  • Reduced risk: the consulting firm provide ongoing support for the models they produce even if their staff leave

Because of these advantages, IBIS World is forecasting a significant rise in the demand for consulting services backed by analytics of nearly 4% in 2015/16 in Australia.  Outside Australia, the increase is even higher.

What’s your Project?

Get in touch today by calling +61 8 6210 8500 or +61 412 581 486, or leave your details below for a confidential discussion.

Get in Touch

A report is more useful if it is formatted well

Steps:

  • Highlight the numbers you want to format
  • Right Click – Format Cells
  • Custom > then copy and paste (or type) the following into the Type box:

#,##0_);

[Red](#,##0);-??_)

 

Beautiful Numbers in 10 Seconds image 2

Done

If you want to format other numbers with the same format you can always right click, select custom and scroll down to the bottom of the list and your format should be there.

 

For those that have more time to read this and are interested:

The positives are formatted with a comma separating the ‘000s  so  #,##0

The positives are also pushed across the width of a bracket hence the _) so that they line up with the negatives.

The semi-colon ; then tells Excel you’ve done with positives and you’re moving onto negatives.

Negatives are to turn [Red] and have brackets around them  [Red](#,##0)

Finally, after another semi-colon, Excel is ready for how to format Zeros.

In this case we want zeros to show as a dash and also be shifted in 2 characters from the right in addition to the width of a bracket, hence   -??_)


 


 

Please leave this field empty.

A little macro never hurt anyone

So here’s a little macro that can automatically create and update a contents page with hyperlinks in any of your Excel files.

  1. Create a new sheet (call it Contents for example)
  2. Right Click on the sheet tab name  and select View Code
  3. Copy and Paste the code that I’ve added at the end of this post
  4. Close the Screen

DONE

Screenshot after you’ve pasted the code

create live excel contents page image 2

Now whenever you click on this contents sheet the code will run and update your contents list.

REMEMBER TO SAVE YOUR FILE AS .XLSM

(otherwise you will lose your macro)

 

Here’s the code to copy and paste….

Private Sub Worksheet_Activate()
‘This macro creates a list of sheet names and hyperlinks to cell A1 of those sheets

‘Change this reference if you want the index list to start in a different cell
Range(“B10”).Select

 Dim c, d
Dim rCell As Range

d = 0 ‘counter used to increment rows in offset command

For Each c In Worksheets

Set rCell = ActiveCell.Offset(d, 0)

rCell.Value = c.Name
rCell.Hyperlinks.Add Anchor:=rCell, _
Address:=””, SubAddress:=”‘” & c.Name & “‘” & “!A1”, TextToDisplay:=c.Name

d = d + 1
Next c

Set rCell = Nothing

End Sub

Free Download

There are so many keyboard shortcuts and every year I come across new ones. These are a few that I’ve been using recently:

Windows key & e
Open up My Computer (windows Explorer)

Windows key & d
Jump to Desktop

Windows key & +
Screen Zoom (very useful for training courses and presentations), the minus key does the opposite and Windows Key & Escape exits. Can be customised by clicking on the Magnifying glass that appears

Windows key & +

And a couple of Excel ones…..

Right Click Button + V
Paste Values

5 keyboard shortcuts image 1

Ctrl & ;
Put today’s date in a cell

5 keyboard shortcuts image 2

Any other favourites out there?


 


 

Please leave this field empty.

3 Bonus Pivot Buttons you really need

Free Add-in

By Wyn Hopkins

If you’re a regular Pivot Table creator then download this free add-in to save yourself some time.

There are a number of steps involved when creating a Pivot Table that are often repetitive and frustrating.  So much so I’ve created 3 buttons, so that a single click now replaces the multiple manual steps I used to perform.

pivot table bar

Jump to Pivot Source Simply click inside a Pivot Table and then clicking this will jump you to the source data, with the option to stay there or jump back.(No more Pivot > Change Source Data > “Oh there it is” > Cancel > “where was it again” etc.)
Pivot Reformat This takes any Pivot Table you are clicked on and converts the layout to the one I would prefer as the “default” option:

  • Tabular layout
  • No Subtotals
  • Turn off update column widths
  • Wrap column headings and autofit
  • Format all numbers with 0 decimal places
  • Thousands with commas, zeroes formatted as “-“ and negatives in RED with brackets.

Note, this is a slightly enhanced version of this solution: How to get your preferred pivot.

Pivot Toggle Sum / Count If you’ve ever added multiple columns of values to a Pivot Table and they’ve all defaulted to COUNT and then you have to manually go through and change each one to SUM, then this is the button for you!   It works in both ways – hence the “Toggle”

 

I created these for my own use, but thought it would be good to share with others, so I created an add-in that you can save to your computer.

The instructions on how to do this are really simple and listed out in the file itself.

Also, hover your mouse over the icons in the Pivot Addin tab (to the right of the View menu) to see a description of what each button does.

Please let me know if you come across any issues or have any suggestions for future additions.

Free Download


 


 

Please leave this field empty.

Depreciation is one of those concepts that sounds easy and simple … until you try to put it into a financial model!

In this blog, we start with Excel’s standard depreciation functions, then show two much better, more functional and more elegant ways to calculate depreciation.

1. Excel Functions

Excel has some built-in (although relatively unknown) depreciation functions:

Function Description Comments
SLN(
cost, salvage, life)
Straight-line depreciation of an asset for one period A very basic function:

([Cost] – [Salvage]) / [Life]

 

Somewhat useful, although it doesn’t consider the year so it doesn’t know when the asset has been fully depreciated.

DB(
cost, salvage, life, period, [month])
Depreciation calculated using the declining balance method A more sophisticated function that includes a period input so it knows whether the asset is fully depreciated.

 

Excluding the optional [Month] argument, depreciation is calculated as:

([Cost] – [Total depreciation from prior periods] x [rate]

 

Where

 

[rate] = 1 – (([Salvage] / [Cost]) ^ (1 / [Life]))
DDB(
cost, salvage, life, period, [month])
Depreciation calculated using the double declining balance method Similar to the DB function above except that the depreciation is accelerated.

You can find more help & examples of each in Excel’s Online Help or via Google.

They’re OK for simple calculations but they’re not overly useful and so personally, I’ve never actually used them in a financial model.  I’m sure someone must use them however!

2. A Better Straight-Line Function

If you have some assets that are simply being depreciated at x% every year, there is a better way to handle straight-line depreciation than the basic SLN function.

The tricky bit with straight line is that we want Excel to automatically remove those assets that have been fully depreciated, because otherwise they’ll keep on depreciating and we’ll end up with too much depreciation.

Here’s the formula logic for what we really need to do:

[Depreciation Expense] =

[Last year’s depreciation expense]

plus

[Current year’s CAPEX] / [Life]

minus

IF [Year] > [Life] THEN

[CAPEX from [Life] years ago] / [Life]

ELSE

0

END IF

This effectively removes the CAPEX which has now been fully depreciated from the calculation.

Deprecation Modelling

This is what the formulas on row 12 are doing.

Using the OFFSET function, once we get to year 6 and beyond, we remove the depreciation on CAPEX that occurred 5 years ago (since the asset life is 5 years).

Credit and thanks to my friend Damien Beng for this one.

3. Reverse Ticker

The Straight-Line method above works well where there is a consistent depreciation rate.  But what about where there is a different rate each year?

The way most financial modellers handle this is to do a separate calculation for each year’s depreciation.

On the first row, the depreciation calculation multiplies the CAPEX in each year by the first year’s depreciation rate (30% in our example).

On the second row, the depreciation calculation multiplies the CAPEX from last year with the second year’s deprecation rate (25% in our example) … and so on until the total number of years has been reached.

This is fine if you only have a small number of rates and small number of asset classes, but it becomes very cumbersome and unwieldy when you have larger numbers of these.

The calculation also has to be manually adjusted for each row – you can’t copy from one row to the next because you must adjust the formula.  It’s annoying!

A Better Way

A better way is to use the Reverse Ticker method.

We start by putting all the depreciation rates on a single row, then reversing these using an OFFSET formula (could also be done via an INDEX).

Once we have the rates reversed, we can multiply everything together using SUMPRODUCT that multiplies the CAPEX row (anchored in year 1 and growing by one column to the right each year), with the depreciation rates row (anchored in the final year and growing by one column to the left each year).

The results are shown below:

Year Calculation
1 $35,000 x 30%
2 $35,000 x 25% + $50,000 x 30%
3 $35,000 x 20% + $50,000 x 25% + $80,000 x 30%

… and so on

Deprecation Modelling 2

As you can see, both examples result in the same total depreciation charge each year.

Application to other areas

You can use the Reverse Ticker in all kinds of places including:

  • Growth rates
  • Progress payments
  • S-Curve construction costs

Credits and many thanks to my friends Andrew Foster & Liam Bastick.

Free Download

Introducing SUBTOTAL

SUBTOTAL is an incredibly useful function for producing reports … yet it’s relatively unknown.

If you have a list of values that are in categories and you want a subtotal for each category, then a grand total at the bottom, it’s so much better & far more reliable than using SUM.

The key difference is that SUBTOTAL ignores all other SUBTOTAL functions.  This means you can have one subtotal or a hundred subtotal functions, it makes no difference to the grand total at the bottom because SUBTOTAL will add everything up without double-counting.

Let’s say you want to have 3 subtotals in your data at A3, A10, and A15.  If you create these with SUBTOTAL at each row, your grand total function can simply read =SUBTOTAL(9, A1:A15).  If you used SUM instead, you’d need to write =A3+A10+A15

It’s not too bad when you only have 3 subtotals, but when you have 10+ SUBTOTAL is so much easier!

… just don’t mix SUBTOTAL and SUM together because that’ll cause problems!

Where SUBTOTAL gets weird

While the behaviour above is relatively straightforward, you may not expect it to work like this.

If you use the SUBTOTAL function inside another function e.g. =AVERAGE( SUBTOTAL(9, A1:A15), 55) function, any subsequent SUBTOTAL functions that include this one will still ignore the result.

This applies regardless of what the wrapper function is.  Depending on your workbook, this may or may not be what you were intending.

See the attached example to illustrate this concept.


 


 

Please leave this field empty.

Apologies for the Dad joke above (not really), but in this article, I’ll show you how you can easily get data labels on all your data series, even if you have LOTS. All it takes is a little VBA code.

In the data set below, I’ve made up some stats about how many girls were born at our imaginary hospital over a period of time, and what their names were … mainly because I feel this data completely justifies my continued use of very bad dad jokes!

Labels Labels Labels

Anyway, let’s assume you want to chart the individual names, plus you want to know the total each month.

That’s easy enough to do … you just put this into a pivot table & generate your desired chart.

Labels Labels Labels 2

The resulting chart wouldn’t be too bad if we only had 4-5 names, but what if we have 20-30 names?

It’s a bit hard to match the legend colour to the column colour.

Labels Labels Labels 3

One solution could be to add a data label … but with so many data series, the girls would be teenagers by the time you finish!

A Macro Solution to a Micro Problem

The solution here is to use a VBA macro to automatically create all the data labels we want to see.

This is the 2013 version – see the file below for one that works in 2010. We also set this up to run every time someone clicks on the Report sheet so that it is constantly refreshed.

While the result is slightly different in 2010 since it lacks some of the nice new features in 2013, the result is mostly the same.

Labels Labels Labels 4

… and now if the image were big enough, you’d be able to read all the names plus they’d stay in sync with any new data you add! Voila!

Download template Data Labels Series Names 2010 or Data Labels Series Names 2013.

Excel 2016 - 3 new features

The next version of Microsoft Office is just around the corner and we’re nearly as excited as this girl!

So here are the top 3 new features (according to us) … let us know if you agree (download preview).

1. Cool new charts

Check them out!

2. More Powerful Power Query

Power Query has been with us since Excel 2010 and if you aren’t using it to access all your data sources, you should be, because it’s AMAZING! Seriously!

Excel 2016 takes the awesomeness to a new level by improving several key things:

  • Baked in: it no longer feels like a third-party add-in, nothing to download or enable. It’s front and centre, right there in the Data ribbon by default (the old Query Editor has thankfully been retired! Yay!!)
    Power Query
  • PowerPivot field names and table names can now be changed without screwing up all your measures
  • Measures are now called “Measures” again rather than “Calculated fields” (what were they thinking changing this anyway?) and you can right-click to edit them again.
  • Much easier Calendar tables that are built-in
  • Search for field names in the Field List of Pivot Tables. No more scrolling up and down trying to find the elusive data you’re looking for.

3. Forecasting Functions

Excel has always had a degree of forecasting functionality (just extend the Trendline for a data series), however, they’ve expanded the forecasting functions significantly in this version.

You can now also allow for seasonality and produce different forecast scenarios.

forecasting functions

There are quite a few others, but these are our top 3.

The new charts and new data acquisition/handling capabilities are enough reason to upgrade alone!

Download the preview and have a play to see what you think!

 

Love them or hate them, Excel charts have always been a key feature.  They’re a powerful way to visualize data, communicate trends, and highlight anomalies.

Excel 2016 has taken charts to a whole new level by adding several charts that users have been requesting for many years!

Here’s a quick tour of the top 3 new charts.

1. Waterfall Charts

… Finally! (how many years have people been asking for this one?)

waterfall chart

waterfall chart 2

These type of charts are a great way to show a starting point, positive/negative movements and the ending point that results.

This chart also allows for subtotals along the way.

For example, they’re often used to show things like Profit & Loss Statements (Income Statements) where you have revenue, cost of goods sold, Gross Profit (sub-total), overhead expenses, operating income (subtotal), other income, earnings before tax (EBT), income tax, and net income.

They could also be used for cash flow statements, project forecasts or to reconcile the current year’s results to the previous year’s.

2. Treemap

To chart data from hierarchies and visualize how one element and category compares to the others.

In this example, we have car manufacturers and car models together with their sales.

Trying to put all this data into a pie chart is problematic: it’s just too hard to see what’s actually going on.

Treemap

But taking the same data and putting it into a Treemap, makes it much easier to understand.

Floorplan

You can easily see that in our sample data, Toyota and Ford have nearly 50% of the market share, and that the Camry model makes up the biggest chunk of Toyota’s sales.

3. Sunburst

Another really useful way to visualize hierarchies (like our car sales example above) is to use a Sunburst chart.

This works particularly well when you have more than 2 levels.

In this example, we’ve taken the car sales and broken them down into manufacturer, car type, and car models.

The Sunburst chart is perfect for visualizing this data.

Sunburst

But wait, there’s more …

There are some other new ones too:

  • Histogram (for statistical analysis)
  • Box & Whisker (for showing data and the distribution of results)
  • Pareto (the very principle we’ve followed here to just bring you the best ones!)

Upgrade to 2016 or Office 365!

So get ready to upgrade because there are also some other really cool features you’re going to want to make use of too.

Don’t leave it until 2023!

Power BI Desktop

We’ll go into much more detail on these in future posts but this is very new stuff and we thought we’d let you know about it

Key Points:

It’s free and it’s pretty amazing.

If you want to get more advanced then it is $9.99 per user per month.

Power BI steps

  1. Pull your data together from single or multiple data sources
  2. Slice and Dice (Pivot Table, Pivot Chart) the data
  3. Create Charts & Dashboards
  4. Publish them to a secure website and choose who in your company to share these dashboards with (you can only share with people with the same @xyz.com address)

You can either create these reports in 1 of 3 places:

    1. Online: PowerBI.com
      On your machine (and then upload to Power BI.com when ready to distribute).
    2. Power BI Desktop (download this from www.PowerBI.com)
    3. Excel – using Power Pivot and Power Query – we love these tools!

Find out more from Microsoft’s website, and contact us for a demonstration.
https://powerbi.microsoft.com/

excel health check

Sticking with the theme of the health of your spreadsheet, have you ever notice spreadsheet become inexplicably bigger? Suddenly becoming 20MB instead of 2MB.

One cause is unused rows and columns.

How do I check and how do I fix it?

Simple, on each worksheet in your file press End then Home.

Your cursor will jump to the bottom right active cell. If this happens to be in column XFD or similarly far across your spreadsheet, or down in row 1 million and something then this may be your issue.

To fix it you need to delete all of the columns / rows between your data and this rogue cell and then Save (and sometimes re-open) your file. For complex files you may have to resort to clearing out smaller sections each time as the calculation load otherwise causes an “Insufficient Resources” type warning to appear.


 


 

Please leave this field empty.

multiple monitors

Ever get the warning that Excel has “run out of resources” or “too many formats” or you simply can’t paste into it anymore. There’s a glitch in Excel 2010 and 2007 (fixed in Excel 2013)

Am I affected?

Here’s how to check. Open your Excel Files (your important ones), go to the Home Ribbon and click on the drop down for Cell Styles.

If you get this, then your file is all good 🙂

excel table 1

This however is indicative of problems to come

excel table 2

 

These “corrupted” styles often begin when you paste data into Excel from other systems, however it usually doesn’t cause much of an issue.

So what makes it a problem and what does this have to do with multiple monitors?

When you have two monitors it often makes sense to have two “sessions” of Excel open so that you can compare them on separate screens. i.e. you have started Excel twice. The temptation is then to copy and paste between sessions, and this is where the problem starts.

Each time you copy paste between sessions it also brings across ALL of the corrupted cell styles. This happens even if you bring across just one blank cell. If you use the same spreadsheet on a regular basis and copy data into it daily, weekly, monthly then this issue gets compounded each time. Eventually you will get performance issues and finally you will no longer be able to paste into that file.

So what do I do?

Prevention:

Never copy between “sessions” in Excel 2007 or 2010 (all fine with Excel 2013). Instead open up the files you want to use from within the same session if you are copying and pasting.

Cure:

1. Macro
If your sheet isn’t too corrupted we have a macro that can restore cell styles to the default setting.

Download it below and then follow the instructions. ALWAYS make a back-up of your file before running anything like this.

Free Download

2. EXE file
If our macro can’t help you (i.e. your file is “too” corrupted) then I’ve used this tool successfully many times in the past. XLCleanerDotNET4.0

However, we strongly advise you consult your IT department before installing this.

forecasting
Market volatility, depressed prices and slow growth are major factors affecting virtually all companies.

For everyone, the focus on maintaining and improving their financial position is intense and this means planning, budgeting and forecasting has never been more critical to success.

So here are the seven forecasting habits of highly successful companies:

1. Forecast in two directions

Top down forecasting – where the most senior management define the objectives of the company and then all levels below build their planning and forecasting off this vision – has its flaws.

Yes, it can be the best way to ensure your companies high-level goals are embedded within your plan. But, as anyone who has worked under this regime in a big company will testify, it also runs the risk of weak organisational buy-in and a lack of grass roots knowledge – making forecasting accuracy less feasible.

A bottom up approach to forecasting encourages organisational buy-in and improves feasibility – as each department, function or division contributes to creating the forecast. But the major risk is that a grassroots generated forecast is not the best fit to help the company achieve its wider goals.

The most successful companies combine a top down and bottom up approach.

While more complicated to manage, it means you get the benefits of both methods without the common flaws. When supported with well-defined processes and good levels of automation, this approach is effective in linking the performance of all levels of the organisation to the wider company vision. And this promotes a level of confidence and buy-in that makes that vision more achievable.

2. Get your financial house in order

No organisation sets out to have financial planning and forecasting delays.

But … it happens.  Regularly!

Failing to get forecasts done on a timely basis is usually a sign of more concerning lapses of financial control in the organisation.

In the most successful companies, forecasts are completed at least quarterly.

This one key action is a strong indicator of the likely success of an organisation’s overall financial planning processes.

3. Hold people accountable for forecast accuracy

That’s pretty obvious, right? Of course you monitor performance against forecasts and hold people accountable. It’s likely your management will have to explain why targets were not hit and goals not achieved.

But is that enough?

Is that enough of an incentive to gain a competitive advantage in your control of inputs and outputs?

For the best performing companies, the answer is no.

They know that there is a greater chance of improved financial planning performance when a manager’s personal interest is tied to forecasting accuracy.

The facts are simple: when bonuses and even job security are linked to accurate forecasts, managers are more likely to make their forecasts as accurate as possible.

These firms achieve significantly better financial planning outcomes as a result. In fact, the best in class companies are five times more likely to link compensation to goals, targets and forecasting accuracy.

Keep in mind however, that you can only hold people accountable for things they have control over e.g. no one is responsible for movements in commodity prices.

4. Keep your forecast agile

With the volatile economic conditions that prevail, agility in financial planning is key to decreasing risk and grabbing opportunities.

External and internal factors can greatly alter your ability to attain the goals you have set. So it’s hardly a surprise that having the ability to re-think and then re-forecast as market conditions change is a tremendous advantage.

By keeping agile and reacting to changing conditions, you ensure your forecasts do not become unrealistic over time.

Successful companies perform scenario modelling, including the capability to conduct “what if” scenario and change analysis.

This type of analysis makes your financial plans more informed because they’ve taken into account and anticipated the effect of a range of possible events.

Put simply, this puts you in the fortunate position of being able to consider alternative scenarios and having the ability to change forecasts, plans and forecasts mid-stream.

5. Improve the quality of your data

There are myriad ways to improve the quality of your financial planning input data.

But improve it you must because the quality of your input data significantly affects the quality of the output you achieve: “garbage in, garbage out”.

The best in class companies are much less likely to base forecasts on a combination of historical data, current performance and forward indicators.

This helps these companies to shift the focus from looking back to looking at the road in front.

6. Keep your eyes on profit

With this all said, it’s important to not get so attached to a forecast that it becomes your most important consideration. This blinkered approach to financial planning can lead to serious flaws.

The best in class companies know that accuracy must be balanced with the need to improve or preserve profitability.

They know that while forecast accuracy is very important, it cannot be to the detriment of good business decisions.

In other words, the best performing companies focus on the overall health and profitability of their business, rather than managing rigorously to achieve a forecast.

Forecast deviation is serious (and a potentially grave threat to profitability), but they have a wider view of the financial planning process.

7. Modernise your processes with smart use of technology

There are three key areas where the use of technology can help you improve your current financial planning performance. And these are three areas that successful companies focus their attention on.

Firstly, best in class companies ensure that their people involved in forecasting processes are automatically guided through steps with smart systems.

Secondly, they ensure that events outside of the company (e.g. industry or financial changes/events) trigger an alert to make a forecast adjustment.

And finally, and most importantly, they use technology to automatically link internal events to financial planning activity. When a contract fluctuates, or a schedule is missed or an order lost or whatever event it is, they automatically trigger an alert to adjust the forecast.

Just imagine the advantage this gives you not only in forecast accuracy but also in removing unnecessary layers of manual data handling.

So what are the next steps to improve your financial forecasting performance?

Here is a quick recap on what highly effective habits you can learn from the best:

  • Establish enterprise wide forecast collaboration from the top-down and bottom up as well as across departments
  • Produce timely forecasts
  • Closely link the achievement of targets and goals to achievement and compensation for all employees
  • Develop agility with ‘what if’ scenarios and change analysis before finalising plans
  • Improve the quality of your input data to significantly affect outcomes
  • Focus on the overall profitability of decisions above the rigid adherence to goals
  • Use technology to receive alerts triggered by internal and external events, and be automatically guided through the steps of your forecasting process.

Begin implementing these changes to your forecasting processes and you can’t help but improve outcomes.

Observing, emulating and improving on the activities of successful companies are great ways to drive positive change to your financial planning.

Turn forecasting pain into competitive gain!

As the saying goes, if you keep on doing what you’ve always done, you’ll keep on getting what you’ve always got.

Let us Help

Why not let Access Analytic help you develop highly effective forecasting processes and models?

We can assess your situation, recommend the most appropriate tools, build budgeting & forecasting solutions using these, and consult on best practices.

Simply contact Principal Business Analyst Jeff Robson on +61 8 621 8500 or +61 412 581 486 or leave your details below for a confidential discussion.

Get in Touch

When costs go bad - hand
What do you do when costs go bad?

CFOs in the mining, resources & engineering sectors are facing cost pressures and pricing volatilities that are squeezing profits.

Key risks on the radars of many CFOs include:

  • Declining commodity prices
  • How to reduce wage, salary and contracting costs (that are often combined with diminishing productivity).
  • Increasing taxes at both state and federal level and uncertainty in relation to these.

Indeed, a report by Ernst & Young states:
“The risks continue to grow and we don’t expect a slowing in this trend. The bottom line is that if returns start to wane, then there is a greater imperative for organisations to tightly and effectively manage their risks to maintain an adequate risk/reward balance.” (Michael Elliot, Global Mining and Metals Leader, Ernst & Young).

Strong words indeed.

Adding to these risk factors are other concerns such as cost inflation, capital project execution, social and environmental complexities, price and currency volatility, capital management and access, as well as fraud and corruption. While production and revenue numbers are strong, the price peaks have passed and so there is a much greater imperative for mining and resources companies to remain nimble and sure-footed in how they manage these fast-changing risks.

A “new normal” of relatively low growth, depressed prices, cost pressures, increasing supply capacity by low-cost producers, and pronounced volatility is intensifying pressure. When the significant new regulatory burdens on the sector are included, the pressure on profits is incredibly intense … and it is only likely to increase.

To stay on top in this challenging environment, CFOs must work smarter to understand how changes to underlying assumptions might affect overall profitability.

Implications for CFOs and finance decision-makers

Cost control. The need for CFOs to manage and control their costs is always cited as an imperative regardless of where we are in the cycle. This is particularly relevant for resource companies who basically cannot control their revenue (since this is dependent on the commodity price as set by the markets). The only thing they can focus on to generate more profits is costs. With increasing commodity price fluctuation, companies need the ability to quickly and accurately analyse the impact of price changes, understand how this affects their business, and then determine where they need to make cost savings.
Improving efficiency. Productivity gains and improving efficiency have become key to surviving intensifying competition and market price pressures.

With the increasing complexity of doing business, businesses need access to the right information, within the right timeframe, with the right level of summarisation and detail more than ever to make decisions that are based on facts and data, rather than gut-feel and estimations.

However, this needs to be an ongoing process, not just a once-off, knee-jerk reaction to particular market events. In addition, everyone in the company needs to be involved in making better decisions about costs and realising the implications of their actions on the bottom line.

The best-performing companies achieve this through the effective use of enterprise-wide, analytical applications.

Yes, complexity presents challenges for all CFOs, however, these challenges can be crippling for the whole organisation; particularly if the challenges are combatted with inadequate IT systems and poor finance IT capability, not to mention the increasing demands from the board or executive management for information.

How to Stay on Top

With the appropriate skillsets and analytical systems, business users can have the tools they need to investigate issues, track down the causes of problems, and make decisions about how to improve the company.

When these systems also allow the analysis to be distributed to the right managers and staff within the organisation, and presented in a way that is useful so that each person gets access to what they need in a form they can make sense of, the power that comes from the alignment around the company’s objectives is amazing!

Better information means that CFOs can make better and informed decisions. By implementing systems that utilise all the relevant, timely, accurate information that the business has, CFOs and their organisations will be able to plan better, meet regulations, improve decision-making and ultimately, maximise profitability.

To know you’re making decisions with confidence, call Access Analytic on +61 8 6210 8500 or +61 412 581 486, or leave your details below.

Get in Touch

Hot Air Balloon

Accuracy of Budgets and Forecasts

In times of increasing volatility, decision-makers have an even greater need for meaningful, accurate, up-to-date financial projections so they can respond to changes in economic conditions rapidly.

In a recent survey of global corporations, research firm Aberdeen Group found that market volatility is the major challenge for effective financial planning, budgeting and forecasting.

Greater volatility means that budgets and forecasts are almost certainly wrong because it’s not possible to predict how markets, competitors, customers, governments and suppliers will interact and react to changes in the market.

But in high uncertainty situations, the need for accurate budgets and forecasts is also greatly increased as executives, managers, employees and shareholders seek guidance about where the company is going and what the results are likely to be.

This is the forecasting paradox: forecasts are needed most during the times when they are least reliable!

Forecasting in Times of Volatility

In practical terms, the best way for companies to respond to this volatility is to increase the frequency of forecasts and dynamically account for change.

The research shows that as market volatility rises, consideration given to the annual budget declines because management pays far more attention to the timely reforecasts and dynamic, ‘rolling’ forecasts.

This fills the gap by providing useful, relevant information that managers can use for decision-making.

In reasonably static organisations, annual budgets may be adequate but for companies experiencing high volatility and uncertainty, accurate decisions and alignment between planning, budgeting and corporate goals can only be achieved with more frequent analysis and reforecasting.

Forecasting Systems: A Competitive advantage?

While many companies need forecasting that is more accurate and timely, far fewer give sufficient priority and resources to this area to enable it to occur … to their detriment.  Many attempt to achieve this without implementing proper systems or allocating sufficient staff, which usually means that the initiatives are either unsuccessful or only partially successful.

Aberdeen Group’s research shows that companies that are considered “best-in-class” achieve lower costs, higher profits and greater returns.  At least part of the reason for this is that they allocate adequate resources in terms of staff and IT applications to enable effective frequent reforecasting.

An effective budgeting/forecasting system can be a dedicated application (larger companies) or could be equally as effective in Excel (mid-size companies) if managed correctly.  The key is the agility and capability of the system used, not so much the platform itself.

Improved forecasting capabilities provide the company with a competitive advantage because they enable recognition of issues and opportunities, support decision-making, and enable the company to take action far quicker than competitors.

As such, a company’s ability to finalise the budget prior to the beginning of the fiscal period then prepare timely, ongoing reforecasts is a key indicator of the company’s likelihood of success.

Budgeting and forecasting

Capabilities of Leading Companies

Aberdeen found that “best-in-class” performers built capabilities across a range of areas including:

  • Ability to re-forecast as market conditions change
  • Ability to track actual performance vs budget/forecast
  • Capability to perform “what if” scenarios.

Of the various defences taken by business, Aberdeen noted that one of the top strategic actions that successful businesses take is to prepare for a variety of scenarios using (“what if” analysis).

The capabilities listed above clearly shift the focus away from the rear-view mirror and concentrate far more on the road ahead (assuming that fundamentals such as good quality, reliable, timely source data have already been sorted out).

Budgeting/Forecasting is just the Beginning

In relatively static environments, the preparation of the annual budget is often seen as an end in itself: a once-a-year time-consuming activity that once completed, can be largely ignored until the next budget.  However, the budget is just the beginning for companies operating in highly uncertain environments.

There are always two critical sides to the budgeting/forecasting process: preparing the budget together with its various scenarios and subsequent reforecasts, and comparing actual results to these then reporting and analysing reasons for variances.

The use of automation and tools to assist in the planning process leads to an efficiency that is very hard to achieve using manual spreadsheet processes alone.

What Capabilities should your Budgeting/Forecasting System have?

If you’re in a company that needs better systems to enable more timely reforecasting and analysis, here are some key capabilities:

  • Integrated System: you don’t want to be moving data between different systems or trying to integrate your budgeting/forecasting/scenario system with your reporting and analysis systems.  The budget/forecast system should handle all these things.
  • Centralised: you need to have everything in one place so you can control access, provide workflow (e.g. approvals and follow-ups), and handle multiple versions.
  • Ability to Budget/Forecast in Different ways: many parts of the business think in terms of dollars per unit, rather than totals.  To get them involved, they need to be able to input their budgets and forecasts in their terms, then access reporting and analysis the same way.
  • Interactive, Multi-Platform Visualisations: a large part of the business doesn’t understand financial figures and even those who do, often find it much easier to spot issues and opportunities if the data is presented in a visual form with charts, dashboards, scorecards that also allow drill-down.  And since most people are not confined to PC’s, you’ll also want to make this information available via the web, tablets, smart phones etc.  Fortunately, even Excel now has these capabilities!

Where to Begin?

If you’d like to explore this further, get in touch with us for a confidential discussion.

Simply contact Principal Business Analyst Jeff Robson on +61 8 621 8500 or +61 412 581 486 or leave your details below for a confidential discussion.

Get in Touch

"Hey! Are you lookin' at me?"

In the current environment, reducing costs and increasing productivity are the keys to success.

This was emphasised strongly by a recent PriceWaterhouseCoopers (PwC) report which found that profits at the world’s top 40 mining companies, including BHP Billiton and Rio Tinto, slumped by half between 2011 and 2013 as the mining boom came off its highs, then reduced by a further 70% between 2013 and 2015.

PwC Australia’s head of energy and mining, Jock O’Callaghan, says the global mining industry is facing a crisis of confidence.  It’s no great revelation that they need to focus on cutting costs and improving productivity!

Productivity Falls Short

Unfortunately, productivity in Australian workplaces continues to fall short.

Ernst & Young’s (E&Y’s) recent Australian Productivity Pulse, revealed over $300 billion in unrealised potential for Australian companies.

This report found that a staggering 85% of employees could be more productive.

Perhaps most critically, E&Y found that the key to increasing productivity is to measure productivity.

You Need to Measure

It’s not brain surgery; effectively measuring productivity allows organisations to identify what works to increase it, as well as making more employees aware of how their actions impact the business.

Whether you’re trying to improve the operation of an individual, a department or an entire organisation, key performance indicators (KPIs) are critical.

But, just having KPIs is not necessarily going to deliver positive business outcomes.

Your KPIs need to be accurate, reliable, clear and relevant.

And unfortunately, even having those four critical elements right, your KPIs can still be a complete waste of time if the person who needs to take action doesn’t understand what they mean or if the performance isn’t clearly communicated.

Getting your KPIs Right

The critical success factor is:

Designing, implementing and communicating KPIs using the best possible practices and principles to ensure that your KPIs are:

  • Effective
  • Relevant
  • Understood – and so can be acted upon.

This means that effective modelling, reporting and analysis for organisations is critical to enable them to improve their financial and operational performance.

In a nutshell, what gets measured gets done.

This makes KPIs vital!  Because today’s successful organisations create sustainable competitive advantage by focusing on costs, productivity, profitability and optimisation relentlessly across their businesses.

Measurement and reporting are the keys to driving that meaningful focus.

KPIs must focus on only what’s important, and measure progress towards defined goals.  However, we must also accept that measures are not always precise.

It is imperative that what we measure aligns individuals to organisational goals, and that KPIs are presented in an accessible dashboard format that can be effectively distributed throughout the organisation.

Dashboard Best Practice

Access Analytic believes that best practice dashboard principles can be distilled to two critical considerations:

  • Communication
  • Actionability

In terms of the basics, data must be accurate, timely, reliable, and clean.  It must be easy to update, and ideally, this should be an automatic process.

As the overriding objective is communication that supports action, the presentation of the data should define what each KPI is – and what it means.

An at-a-glance screen that summarises the five or six most relevant measures can be a highly effective way to communicate key measures.

Ideally, it should also define what the KPIs are, and what they mean, and provide an information summary with the facility to ‘drill down’ to the detail where required.

Exceptions and particularly important information should be highlighted – and most critically – the dashboard should support action not just within the most senior executive team, but right across the organisation.

Get the Benefits for your Business

If you’re interested in reviewing your key performance measurement methodology, contact Jeff Robson on +61 8 6210 8500.


 


 

Please leave this field empty.

Creating a Waterfall Chart in 5 Steps

By Wyn Hopkins

waterfallchart1A waterfall chart is a great way of illustrating the causes of differences between two data points. The most common being Actual v Forecast or Current Year v Prior Year.

It can be quite a tedious manual process to create this chart, however using Up Down bars you can create an automated waterfall chart really easily.

Alternatively, if you have Excel 2016+, waterfall charts are built-in.  Excel 2016 Charts

More Information & Free Example File:

Free Download

Saving a Default Template

By Wyn Hopkins, 26 Jun 15 (updated 30/09/2016)

Why?

Life can be very repetitive.
If you find yourself making the same changes to Excel every day such as setting custom number formats, fonts, cell styles, colours, print headers and footers then you should set up a template.
Even better than this is you can set it to open automatically every time you start excel or a new file.

How?

Simply create a file with all the number formats, fonts, cell styles and print headings you want.

Or you can use our pre-built template 

Then save as Book.xltx in your XLSTART folder

Search for XLSTART on your C: Drive – it’s normally one of these locations…

C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART\Book.xltx
or
C:\Documents and Settings\<username>\Application Data\Microsoft\Excel\XLSTART\Book.xltx
or
C:\Program Files\Microsoft Office\OFFICE14\XLSTART\Book.xltx

DONE! It’s that easy, the next time you start Excel it will automatically open with that file
Note: To restore the original blank start up sheet, simply delete Book.xltx from the folder (Excel will re-create a new one the next time it starts)


Extra notes on Excel 2013+

DefaultTemplate1With Excel 2013 you can add another step in order to skip the annoying front screen that now appears and instead load your Book.xltx file automatically.
Under Excel Options (File>Options> General)

Uncheck the box labelled Show the Start screen when this application starts.

Alternatively…
If you want to be able to choose from different templates each time Excel starts then ignore the steps above and simply use Save As and select .xltx as the file type. Use the default folder that appears when you select that file type. The next time you open Excel and see the start screen of templates just go to the Personal templates and pin you template

DefaultTemplate2

 

Mail Merge in 7 easy steps

By Wyn Hopkins, 5 Jun 15

Let’s say we have a list of people that have overdue library books, and we need to email each of them to let them know how many days overdue they are.

It’s easier than you think.

1) Set your Excel list of contacts up in a similar format to this Excel image. Close this file.

11

2) Once that’s done open Word and go to Mailings – Step by Step Mail Merge Wizard

22

3) Select E-mail message and click on Browse to select the Excel file containing your email addresses

33

4) Then select Non Blank from Email Address

44

5) Then select greeting line and use the Match Fields box to pick Name from your Excel file

55

55_2

6) You can also select individual fields to insert into the text of your email

66

7) Once you’ve done that then just Preview and Send

77
Note:

A more advanced solution using VBA in Excel can allow you to automatically refresh reports and dashboards and email them automatically to different individuals in the business.  For example a monthly cost centre report for each manager or a daily sales report to the reps in the field.

Power BI Companies of the World

By Wyn Hopkins, 13 May 15

Data extracted from LinkedIn based on a company’s specialty being Power BI.

Kudos goes to the ever-informative powerpivot(pro) team for detailing how to do this.

Display Day of the Week

How can I display the day of the week in a cell instead of the date?

By Neale Blackwood, 18 Jun 09

To have the day of the week displayed in a cell based on another cell, use one of these formulae (this assumes that the date you are using is in cell A1).

Example 1:

articleid_4_displaydayofweek_1_400

Example 2:
articleid_4_displaydayofweek_2_400_01

You can change the format of the date itself by using a CUSTOM format. Press Ctrl+1 (or format menu/format cells). Select the number tab. Select the custom category. Type dddd or ddd in the “Type:” box. Click OK. You can also use these formulae and formats to display month names by changing dddd to mmmm and ddd to mmm.

articleid_4_displaydayofweek_3_01

First published: CPA Australian Magazine October 2002 – Page 65 Excel Yourself

Extras

Excel 2007

In the Home Tab of the Ribbon click the Number Section arrow (see below) to quickly access the Number Tab in the Format Cell dialog.

Tip1-1

List Unique Items in a List

Can Excel extract unique entries from a column?

By Neale Blackwood, 18 Jun 09

Yes. First select all the entries. Then click the Data menu click Filter, click Advanced Filter. Click Copy to Another Location, click Unique Records Only. Click in the Copy To: box and select a cell to copy the unique list to. Click OK.

articleid_6_listuniqueitems_1_400_04

Depending on the layout of your data, you may see a warning message regarding column labels. If this happens, just click OK. You may have the first entry repeated if you didn’t have a column label.

First published: CPA Australian Magazine December 2002 – Page 65 Excel Yourself


Extras

Excel 2007

To access the Advanced Filter in Excel 2007 click the Data Tab of the Ribbon. Select the Advanced option in the Sort & Filter Section. The other instructions from above apply to Excel 2007.

Display Sheet Name

Is there a formula to insert the sheet name into a cell?

By Neale Blackwood

Yes. In cell A1 insert the formula =CELL(“filename”, A1). In another cell, enter =MID(A1,SEARCH(“]”,A1)+1,LEN(A1)-SEARCH(“]”,A1))

The first formula will display the file’s full path. The second formula will display the sheet name. This is also a useful formula as Excel’s header and footer feature cannot easily display the full path of a file. But note, that the file needs to have been saved before these formulae will work.

Note: if you use the sheet name formula on multiple sheets, each sheet will show the name of the sheet that was active last time the file was re-calculated i.e. it’s not dynamic.

Extras
Excel 2007+

Use the Page Layout button at the bottom right of the screen to view the spreadsheet split into pages similar to Microsoft Word.

Tip2-1

When you click in the Header or Footer section of the page a special Tab is opened to allow you to make changes easily. One of the options inserts the File Path.

Select Formula cells only

Is there a way to select all cells that have a formula?

By Neale Blackwood, 1 Dec 02

Yes. Hold the Ctrl key and press the “a” key to select the whole sheet. Then press F5 (GOTO), click the Special button, then click the Formulas radio button and select the types of formula you want by using the check boxes. Then click OK.

This method of selection is useful for formatting an protecting Formula Cells. Check out the other options in the Special section as they can speed up some selections.

First published: CPA Australian Magazine December 2002 – Page 65 Excel Yourself


Extras

Sometimes you need to Press Ctrl + a twice – the first time Excel can select the current region – the second time it selects the whole sheet.

Clicking the grey area where the row numbers and column letters intersect also selects the whole sheet.

Excel 2003

Tip4-1

 

Excel 2007

Tip4-1

Paste Subtotals Only

Is there a way to just paste the subtotals?

By Neale Blackwood, 18 Jun 09

I have a subtotaled list. I want to copy just the subtotals. However, when I paste I get all the detail as well as the subtotals. Is there a way to just paste the subtotals?

Yes. First make sure only the subtotals are showing. Then select your range, press F5, click the Special button, select “Visible cells only”, click OK. Then copy, then past your selection.

Note:

Only values will be pasted. You can use Edit/Paste Special/Paste Link when you paste to link to the cells with the subtotals. Be warned that if you change the data and re-subtotal the list the cell references may change and the links may not be valid.

There is a Toolbar button for “Visible cells only” if you need to use it regularly.
First published: CPA Australian Magazine February 2003 – Page 55 Excel Yourself


Extras

The keyboard shortcut for selecting Visible Cells Only is Alt + ;
Toolbar Icon

Tip5-1

This is at the bottom of the Edit Category in Customize dialog.
Excel 2007

Unfortunately there is no toolbar icon for this in Excel 2007.

Rounding to 5 cents

Is there a formula to round to the nearest five cents?

By Neale Blackwood, 22 Jun 09

You can use the ROUND function to do it. If the number is in cell A1, this formula will work:

=0.05 * ROUND(A1/0.05,0)

 Example:

articleid_10_roundingtofivecents_400_02

This formula rounds 2.5 cents and 7.5 cents up to the nearest 5 cents.

This formula will work to round to any number. Just replace both 0.05’s in the formula with the number you want to round to.

First published: CPA Australian Magazine February 2003 – Page 55 Excel Yourself

Multi-criteria SUMIF

I use SUMIF to sum a range based on one condition. Is there a way to sum a range based on two or more conditions?

By Neale Blackwood

One way is to use SUMPRODUCT. The following formula will sum the range C1:C10 based on the contents of A1:A10 and B1:B10. Cell E1 contains the condition for column A and cell F1 contains the condition for column B.

=SUMPRODUCT((A1:A10=E1)*(B1:B10=F1)*(C1:C10))

You can also use an array formula. Another option is to insert a column in the data and combine two or more columns together by using the & (see other question). [see the Extras below] Then use the new column in the SUMIF.

A word of warning on these three solutions. Your calculation time will increase if you are using large numbers of SUMIF, SUMPRODUCT or Array formulae.

Another alternative could be to use a pivot table to summarise the data. This increases the file size rather than calculation time. Remember, a pivot table needs to be refreshed if the data changes, either manually or via macro.

Excel 2007+ SUMIFS

Excel 2007 introduced the SUMIFS formula that allows you to so multiple criteria SUMIF calculations (up to 127 criteria!) without resorting to the more complicated SUMPRODUCT function.

Syntax

=SUMIFS(sum_range, range1, criteria1, range2,criteria2)

In the example above the formula would be:

=SUMIFS(C1:C10,A1:A10,E1,B1:B10,F1)

Dynamic Headings

Is there a way to change headings based on a cell that has the current month?

By Neale Blackwood

Yes. If cell A1 contains Feb 03 as a date, then the following formula will create the heading – Report for Feb 03.

=”Report for ” &TEXT(A1, “mmm yy”)

Note the space after “for” and between mmm and yy. If you use “mmmm yyy” in the formula you will display February 2003. The & is useful for headings and also for combining names. If cell A1 contains John and cell B1 contains Smith then the following fomula will display – John Smith

=A1&” “&B1

3D Sum formula

Is it possible to easily add up the same cell across spreadsheets?

By Neale Blackwood

Yes, these are called 3D formulae. To create a 3D sum formula that sums all the A4 cells in multiple sheets, open a new workbook, click the AutoSum toolbar icon, then click the Sheet2 tab, then click the A4 cell, then hold the Shift key down and click the Sheet3 tab, release the Shift key and press Enter.

The formula will look like this:

=SUM(Sheet2:Sheet3!A4)

You can also select ranges using this method. The formula can be typed, but the mouse method is better. Be wary of using this method if you move sheets around the workbook. In the above example, if Sheet2 was moved to the right of Sheet3, the formula will change to exclude Sheet2.

The above formula can be read as sum of all the A4 cells between Sheet2 on the left and Sheet3 on the right. In other words, moving sheets around can have an impact on 3D formulae. The sheet number makes no difference to the formulae, it is their relative position that decides which sheets are included in the calculation.

One 3D technique uses the two sheet names First and Last. Then the sheets to be included are moved to the right of First and the left of Last. The formula would look like this:

=SUM(First:Last!A4)

 Toolbar overload

Help! I’ve added so many buttons to the toolbar its hard to find the one I’m after?

By Neale Blackwood

Two suggestions; the first is to right click the toolbar area. You will see a menu of toolbars. It’s likely you don’t use many of your buttons all the time. So it might be better to make a toolbar visible while you are doing a specific task, for example, working with drawings, then hide it once you have finished. You can then remove those buttons from your permanent toolbar.

The second suggestion is to use your Shift key while clicking a toolbar button. Some buttons will perform a different (usually opposite) operation when you hold the Shift key while clicking them (Align Left will Align Right with the Shift key, Sort Ascending with Sort Descending with the Shift key). Hence you might be able to remove some buttons because one button can do two operations. The Shift key can be used with most buttons that have an opposite button.
Tip: You can create your own specific toolbars that will be listed just like the built-in toolbars when you right click the Toolbar area. To learn how, search for “toolbars” in Help and look at Customize Menus and Toolbars.


Extras

In Excel 2007 you can’t customise the Ribbon, but you can add buttons to your Quick Access Toobar.

 

Auto Correct Explained

How does Excel correct my typing errors?

By Neale Blackwood

Excel uses AutoCorrect to correct common typing errors. To see which words are included, click the Tools menu and click AutoCorrect. Use the scroll bar to view the combination of letters and symbols that are included. You can add your own spelling mistakes to the list by typing the incorrect spelling in the Replace box and the correct spelling in the With box and then clicking the Add button.

You can also use this feature as a form of shorthand. You could type tba in the Replace box and To Be Advised in the With box. Then click the Add button. Now type tba in a cell and press Enter. It will automatically change and display To Be Advised.

Please be careful in the combinations you use as you may get unexpected results when typing. Also note that any additions to AutoCorrect only work on the PC they are entered on.


Extras

Word and Excel use the same file for the Autocorrect so changes you make in one will affect the other.

To access AutoCorrect in Excel 2007 click the round Office button and click Excel Options button and click the Proofing category on the left hand side. Then click the Autocorrect option button.

Use a Range Name in a single sheet

Can I create a range name that only works in a single sheet?

By Neale Blackwood
Yes. To create a range name that only applies to a single sheet you type in the sheet name following by a “!” then the range name you require. To create a range name called Test in Sheet1, you would type Sheet1!Test in the Names in workbook box in the Define Name dialog box.

To quickly open the Define Name dialog box press Ctrl + F3. If you have names defined pressing F3 will display a list of names available for the active test. This is handy when creating formula using names.


Extras

Excel 2007 has a new feature called the Name Manager. This is in the Formula Ribbon tab in the Defined Names section. When creating the name you can specify the scope as sheet or workbook. Once created you can’t change it. You need to delete the name and re-create it with a different scope.

Line break within a cell

Is it possible to insert a new line within a cell?

By Neale Blackwood
Yes, when typing in a cell hold down the Alt key and press Enter. This inserts a new line within the cell. You can insert as many lines as you want, but be warned that when you select the cell the formula bar will cover up the sheet space at the top of the sheet and row height will expand accordingly.
This technique is useful for headings in narrow columns or when entering a formula. Note: if you refer to a text cell with an inserted line the result may display a square symbol for the inserted file.


Extras

This technique is different to using the Wrap Text format as you determine where the line break will be rather than the column width determing the break.

In Excel 2007 you can modify the height of the formula bar to dispay more lines that don’t encroach on the spreadsheet. There is a double headed arrow on the line below the formula bar. Click hold and drag the double headed arrow to increase the hieght of the formula bar. See below.

Tip18-1

Dynamic Range Name

What is a dynamic range?

By Neale Blackwood

A dynamic range is a range whose size changes as data is added or deleted in an area. You can use this technique to define a range for a Data Validation drop down list or define a variable length data range for a pivot table.

To create a dynamic range, follow these steps. (You can enter all the formula in lowercase. Excel automatically changes the functions and references to uppercase)

  1. Open a new workbook. (Ctrl + n)
  2. Type the numbers 1 in A1, 2 in A2 and 3 in A3.
  3. Hold Ctrl and press F3
  4. In the Names in Workbook: box type Test
  5. Delete whatever is in the Refers To:box and type =offset($a$1,0,0,counta($a:$a))
  6. Click the Add button, then click OK

Now that you have created a dynamic range you can use it as a Data Validation List.

  1. Click cell B1
  2. Click the Data menu, click Validation
  3. Click the drop down arrow in the Allow: box and select List
  4. Click in the Source: box and press F3. Double-click Test
  5. Click OK

Click in cell B1 and the click the drop down arrow, you will see the numbers 1,2 3 listed. If you type a number in cell A4 and then click the B1 drop down list you will see the new number has been added to the list. Deleting the last number(s) will also adjust the list. This formula looks at the whole of column A. If the range is only in the first 100 rows of column A, you could use the following formula:

=offset($a$1,0,0,counta($a$1:$a$100))

Anything below row 100 would be excluded from the range.

The above examples are single column ranges. You can also define a multi-column range for use as a pivot table data range. The formula below will create a three column range based on the contents of column A.

=offset($a$1,0,0,counta($a$1:$a$100),3)

The 3 at the end of the OFFSET formula specifies how many columns. The default value is 1.

Note the above techniques only work if there are no empty cells between the entries in the range. To create a single column dynamic range that includes empty cells within a range in column A, use one of the following formulae.

For text ranges use:

=offset($a$1,0,0, match(“*”,$a:$a$,-1))

For numeric ranges use:

=offset($a$1,0,0,match(1e+100,$a:$a))

Copy quickly between sheets

Is there a quick way to copy between sheets in the same workbook?

By Neale Blackwood

Open another window of the workbook by clicking the Window menu and selecting New Window. You will notice that the sheet name in the title bar will have “:2” added. To see the two views of the workbook side by side click the Windows menu, click Arrange and then click Tile. You can select the source sheet in one view and the destination sheet in the other view.

Tip: To easily copy between sheets hold the Alt key and the Ctrl key down while dragging a selection between sheets with the mouse. The Alt key stops the sheets from scrolling when you drag with a mouse.


Extras

In Excel 2007 the New Window is in the View Ribbon tab in the Window section along with the Arrange command.
In all versions you can also use the option Windows of Active workbook to limit the Windows displayed.

 Pivot Tables and grouping dates

I use a pivot table to summarise a sales transaction data base. I want the data summarised by month. When I use the date to summarise the totals are by day rather than month. Is there a way to get totals by month?

By Neale Blackwood

There are two solutions. One works with single year data, the other works with any date range. The single year method uses the Pivot Table Group feature. Right click the Date entries in the Pivot Table. Click the Group and Show Detail option (in Excel 2007 its just Group) and choose Month and click OK. This will summarise by month. If there is more than one year’s worth of data then the pivot table will add all the same months together eg all the January figures from all the years will be added and displayed as Jan in the Pivot Table. This is not usually acceptable. There is an option in the Group Dialog to enter start and end dates. You could use this option to only report on the latest year.

The other solution involves adding another column to the data to calculate the month. In the added column enter the following formula. This formula would be entered in row 2 of the Data assuming row has a heading and copied down as far as the data. This formula assumes the transaction dates are in column A.

=DATE(YEAR(A2),MONTH(A2),1)

This formula will display the first of the month involved. Format the added column to display month and year and use that column to summarise the data. This will report on all the months in the data.

Hiding and unhiding sheets

I hide a number of sheets in the workbooks I create because they are used for workings. Is there an easy way to make them all visible at once?

By Neale Blackwood
The following macro will unhide all the sheets in the active workbook.:

Sub UnHideAllSheets()
‘this macro unhides all the sheets in the active workbook
Dim sht As Worksheet
For Each sht In Worksheets
sht.Visible = xlSheetVisible
Next sht
End Sub

The line “For Each sht In Worksheets” will go through each sheet in the workbook. This will ignore any chart sheets in the workbook.

If you want to hide all the sheets in a workbook be aware that you must leave at least one sheet visible otherwise Excel will return an error. The following code will hide all sheets except the active sheet:

Sub HideAllSheets()
‘this macro hides all sheets except the active sheet
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name <> ActiveSheet.Name Then
sht.Visible = xlSheetHidden
End If
Next sht
End Sub

Dualing Screens

I have recently upgraded to a computer with dual screens. Is it possible to display Excel across both screens and have one screen with one workbook and the other screen with another workbook?

By Neale Blackwood

To expand the Excel window across screens, first click the Restore Down button on the Excel title bar (between the Minimize button and the red X button), assuming the second screen is on the right. Next drag the right-hand border of the Excel window across to the right-hand edge of the other screen. This technique is useful to view more cells on a wide spreadsheet.

To view the two files on separate screens, have both files open and click the Window menu.

Select Arrange and select Vertical and click OK. You may have to resize the right-hand file’s window and drag its title bar (where its name is displayed) to the other screen.

WARNING: If you save and close the file when it is on the second screen and then return Excel to a single screen and open the file, you may not be able to see the file on the screen. It is good practice to return the Excel screen to normal single screen mode before doing a final save and close.

Click the Maximize button on the Excel title bar to return the Excel window to normal.

Cumulative Summing

I use cumulative balance columns which add the figure to the left of the cell to the value in the row above the cell. When I insert a row I have to copy the formula from the row above to the new row and to the row below for the column to be correct. Is there a formula that handles inserted rows? I only want to copy the formula down to the inserted row.

By Neale Blackwood

The OFFSET function will allow you to create the formula you require. Assuming column A is for text or date input, column B has the values to be accumulated and column C is the accumulation column. In cell C3 the formula would be:

=B3+OFFSET(C3,-1,0)

Note: the first formula in Column C may need to be different if there is no value above it to add to column B. For example, cell C2 may be =B2 if B1 is the heading for the column.

OFFSET allows you to refer to a cell by starting at a cell and moving, or offsetting, by rows and columns. The -1 means to move a row above the reference, the zero means to stay in the same column. The Help system has a good explanation of the OFFSET function, which also works with ranges.


Extra

Note: you can use the cell’s reference you are in providing the result of the OFFSET doesn’t refer to its own cell. Eg the above formula is in C3 and refers to cell C3 but its doesn’t cause a circular reference error.

Save All Open Files

I work with a model that has many files and links between the files. When I make structural changes that affect links I have to go through and save all the open files to make sure the changes are kept. Is there a way to save all the open files in one step?

By Neale Blackwood

 

Microsoft Excel’s ‘Autosave’ option can do this automatically every so many minutes. When working with a large model, ‘Autosave’ can be annoying and inconvenient.

The macro below will save all the open Excel files that are not new. If you have a new file (for example, Book1) open that hasn’t been saved, it will not be saved by this macro.

The ‘If statement’ in the code ignores all new files.

Sub SaveAll()
Dim wb As Workbook
Dim wbActWb As Workbook
Application.ScreenUpdating = False
Set wbActWb = ActiveWorkbook
For Each wb In Workbooks
If wb.Path <> “” Then wb.Save
Next wb
wbActWb.Activate
Application.ScreenUpdating = True
End Sub

Printer Trays in VBA

I have recorded a macro to print my sheets. I need to print some sheets single-sided and others double-sided. Is it possible to create a macro to do this?

By Neale Blackwood
Macros don’t have many limitations, but changing printer settings is one of them. The solution is to set up two printers on your system. One has single-sided as the default and the other has double-sided (duplex).

When you record the macro, simply select your sheet and change printers and print, and then select another sheet and another printer and print. The macro recorder will record changes in the printers selected.

Link sheet name to cell value

Is it possible to link the sheet name to a cell’s contents? I want the sheet name to be whatever I enter into cell A1.

By Neale Blackwood

There is no built-in Excel function or feature to do this. You have to use an event macro. The macro below will change the sheet (tab) name to whatever is in cell A1. It won’t change the sheet name if it isn’t a valid name. It won’t generate any error messages.

Private Sub Worksheet_Change(ByVal Target As Range) ‘this macro renames the sheet with the value in cell A1
Dim c As Range
On Error Resume Next
Set c = Intersect(Target, [A1])
If Not (IsEmpty(c)) Then
Me.Name = [A1].Value
End If
End Sub

To use this macro right click the sheet name and select View code. Type the above macro code in the white code area on the right-hand side of the Visual basic screen. You could copy the code via the online version of this article. Close the Visual basic screen and enter something in cell A1 to test the macro.

To vary which cell the name is linked to, change all references in the code from A1 to the cell required. If you are unfamiliar with macro code it would be a good idea to practise on a blank file.

Warning: This method may break external links to the sheet involved. Any closed Excel files that are linked to a sheet will not update their links when a sheet name changes, resulting in broken links when the closed file is opened.

Counting weekdays

Is it possible to figure out how many occurrences of a certain weekday there are in a specific month? I need to figure out how many Thursdays in each month of the year.

By Neale Blackwood

Yes, but the formula has 6 IF functions in it. Assume cell A2 has the date of the first day of the month involved, for example 1/2/08 for February 2008. Cell B2 has the text of the three-letter abbreviation for the day being searched, for example, Thu for Thursday. The formula in cell C2 is:

=IF(MONTH(A2)=MONTH(A2+30),

IF(OR(TEXT(A2,”ddd”)=B2,TEXT(A2+1,”ddd”)=B2,TEXT(A2+2,”ddd”)=B2),5,4),

IF(MONTH(A2)=MONTH(A2+29),

IF(OR(TEXT(A2,”ddd”)=B2,TEXT(A2+1,”ddd”)=B2),5,4),

IF(MONTH(A2)=MONTH(A2+28),

IF(TEXT(A2,”ddd”)=B2,5,4),4)))

I’ve split the formula into six lines to make it easier to follow.

The idea behind the formula is that there are only three possible month lengths — 29, 30 and 31 days — that can have more than four occurrences of a day. Also, only the days that occur at the start of the month can occur five times.

The first IF is checking to see if the month has 31 days. If it has then the second IF determines if the day in cell B2 matches one of the first three days using an OR function. The third IF determines if the month has 30 days, if it has the fourth IF function figures out if the day in B2 is in the first two days of the month. The fifth IF determines if the month is 29 days long, if it is then the sixth IF function figures out if the day in B2 is the first day of the month. In the second, fourth and sixth
IF functions, if the day is found to occur at the start of the month then ‘5’ is displayed otherwise ‘4’ is returned.

The last 4 in the formula handles a normal February. The TEXT functions are used to convert the date in cell A2 to a three-character day text to compare with the entry in cell B2. You can copy the above formula from this article on the CPA website to save typing it.


Extras

Rob Steinhoff, Associate, Clarkson ITT sent through a better solution using the SUMPRODUCT formula.

=SUMPRODUCT((TEXT(D3+ROW(A1:A31)-1,”dddd”)=D5)*(MONTH(D3+ROW(A1:A31)-1)=MONTH(D3)))

See sheet image below – formula in cell D7.

tip23-1

Change page orientation on all sheets

Is there a quick way to change the page orientation from portrait to landscape on all the sheets in a workbook?

By Neale Blackwood

Yes. Right click on the sheet name tabs, click Select All Sheets. Now click the File menu, select Page Setup, then click the Page tab (if it isn’t already displayed) change the Orientation to Landscape, click OK. Finally, always ensure you right click the sheet names again and click Ungroup Sheets. If you want to change the page orientation for some of the sheets then hold down the Ctrl key and then progressively click the sheets name tabs you want to change. Then click the File menu and follow the steps above.

Warning: Be very careful whenever you group multiple sheets. Grouping means that whatever you do on one sheet will be done to all sheets that are grouped. Grouping is a very useful feature especially when the layout and format of your sheets are identical and you want to make changes to all the sheets at once.
However, you can also easily corrupt a file by accidentally making changes to many sheets. You can use Undo to correct inadvertent changes. Always remember to use the Ungroup Sheets command mentioned above once you have finished making your changes. If only some sheets are grouped, then clicking on a sheet that isn’t grouped will also ungroup the sheets.

List Range Names

Is there a way to list all the range names in a workbook?

By Neale Blackwood

Yes. Go to a cell where you want the list to start, then press the F3 key to open the Paste Name box and click the Paste List button to paste all the names used. Note: this list is done at a point in time, if you add more names or modify a name, the changes will not be updated in the list. You will need to paste the list again. Listing your range names is a good documentation practice.

If you want to view ranges names on your worksheet simply change the zoom percentage to 39 per cent, or lower, and you will see your sheet with the range names displayed in the cells that they apply to. The zoom percentage can be accessed via the View menu and then select zoom. You can select a standard percentage or type in your own percentage. This technique will not display dynamic ranges names. If you have a scroll mouse button you can hold down the Ctrl key while scrolling to zoom in and out of a sheet.

Last Day of the Month + Useful Date Formula

How do I display the last date of a month in a cell?

By Neale Blackwood

There are two methods. The first uses Excel’s series feature. If you enter two dates, for example, 31/1/03 in A1 and 28/2/03 in B1, you select both cells and drag to the right using the Fill Handle (bottom right-hand corner of the selection). Excel will fill in dates with the last day of the following months. This method will also work for a specific date in the month.

The second method uses a formula. The following formula will display the date of the last day of the month of the date in cell B1, =DATE(YEAR(B1),MONTH(B1)+1,0)
(you may have to change the format to display the date)

The following date formulae may also be useful. They calculate results based on a date in B1:

Days elapsed this calendar year =B1-DATE(YEAR(B1),1,0)

Days until the end of the calendar year = DATE(YEAR(B1),12,31)-B1

Days in the month =DAY(DATE(YEAR(B1),MONTH(B1)+1,0))

Tips on debugging formula

Have you got any tips to debug a formula that is displaying an error?

By Neale Blackwood

Excel has an Auditing toolbar that allows you to trace cell links. This toolbar has a Trace Error button. Excel XP and 2003 have built-in Auditing cell notes that examine and report on your spreadsheet formulae for typical errors. When typing formulae, always use lowercase. That way it is easy to see if you have made a spelling mistake, as Excel will automatically convert words it recognizes into uppercase once the formula is entered.

If you use range names capitalise at least one letter of the name. When typed in lowercase, the name will be changed to the capitalised spelling.

There are rare instances when Excel will not calculate fully. If this is the case, pressing Ctrl+Alt+F9 together will force a full calculation.
If you have a complicated formula with many parts or functions, you can calculate part of the formula by selecting that part in the Formula Bar (above the column letters and below the toolbars) and pressing F9. His will replace that part of the formula with its results or an error message in the Formula Bar. You can use this technique multiple times on a large formula.
Warning: You need to press the Esc key to reset the formula and remove the values. If you forget to press the Esc key you can use the Undo feature to correct the formula. This method is useful for a large formula where you are trying to track down what is causing an error.


Extras

In the Formula Auditing toolbar there is also an Evaluate Formula option which allow you to step through the formula and find errors. You can also use a Watch Window (also on the Auditing toolbar) which displays the values of cells.
In Excel 2007 there is a Formula Auditing section in the Formulas Ribbon tab.

Split first and last name formula

I have a column of cells with names in the format John, Smith. I need to split them into first name and second name?

By Neale Blackwood

Assuming the name is in cell A1 the following formulae will extract the first and second names:

First name formula =LEFT(A1,SEARCH(“,”,A1)-1)

Second name formula =TRIM(RIGHT(A1,LEN(A1)-SEARCH(“,”,A1)))

You could also use Excel’s ‘Text to Columns’ feature in the Data menu. This allows you to split the contents of cells based on commas, spaces, semi-colons or another character that you specify.

In the above example you would select the range that had the names then click the Data menu, select ‘Text to Columns’, ensure the Delimited option is selected and click ‘Next’. Select the ‘Space’ and ‘comma’ options and Click ‘Next’. Select the ‘Space’ and ‘Comma’ options and Click ‘Next’ and ‘Next’ again.


Extras

When using the Text To Columns feature it is a good idea not to have entries in cells to the right of the column being split as they may be overwritten.

In Excel 2007 the Text To Columns icon is in the Data Ribbon tab in the Data Tools section.

 Solve leading apostrophe problems

I have a column of figures that have a leading apostrophe(‘). Excel treats them as text. How can I convert them to numbers that Excel will use?

By Neale Blackwood

There are at least two methods. This first involves typing 1 into a cell. Copy that cell. Then select the range you want to convert to numbers. Right click the range and select ‘Special Paste’ and then click ‘Multiply’. Click OK. This is also a handy way to convert positive numbers to negatives and vice versa if you use -1 instead of 1. You can also use the ‘VALUE’ function, which converts text figures to values. Eg = VALUE(A1) where A1 is the cell that has the text figures. You can then use Paste Special>VALUES to paste the formula cells on top of the text figures’ location.
Warning: Numbers with leading apostrophes can cause problems in Excel as the cell appears to contain a number, but Excel’s SUM function will not include them in calculations. Always look at the alignment of the cell contents. Excel aligns text to the left and numbers to the right. If you manually change the alignment it becomes harder to see cells with leading apostrophes
First published: AUSTRALIAN CPA Magazine March 2004 – Page 57 Excel Yourself


Extras

When typing numbers with leading zeroes e.g. mobile phone numbers, typing an apostrophe first will ensure that Excel doesn’t drop off the zero.

SUM function to handle inserted rows and columns

When I insert a row between the data and the SUM function Excel will usually amend the SUM function to include the new row. Sometimes, however, it doesn’t do it. Is there a way to automatically include the row above the cell with the SUM function?

By Neale Blackwood
When I insert a row between the data and the SUM function Excel will usually amend the SUM function to include the new row. Sometimes, however, it doesn’t do it. Is there a way to automatically include the row above the cell with the SUM function?

Yes, you can use the OFFSET function to achieve this. Assuming the data is in the range A1 to C10. The following formulae should achieve the results you require.

To SUM down in cell A11 enter =SUM(A1:OFFSET(A11,-1,0))

To SUM across in cell D1 enter =SUM(A1:OFFSET(D1,0-1))

Note: Both these formulae use their formula’s cell reference in the formula.

They don’t cause a circular reference because when calculated the formulae don’t’ include their cells in the SUM.

List of sheets with hyperlinks

I regularly create a list of sheets in a contents sheet in my files. I then create hyperlinks to the sheets. Can this process be automated?

By Neale Blackwood

The macro below will create a list of all sheets in the current workbook. This list will start at the active cell. A hyperlink to each sheet is then created. No chart sheets will be listed, only worksheets. The macro will stop if it encounters cells that have entries.

To insert this macro in the current worksheet, right click the sheet name and select View Code. Either type the macro EXACTLY as it appears below, or copy it from the CPA website. Once entered you can run the macro by selecting the cell you want the sheet list to start from and holding Alt and press F8. Ensure the “Macros in” box has “All Open Workbooks” selected. Select the SheetListHyperLink macro and click the Run button. The list should then appear.

Sub SheetListHyperLink()
‘This macro creats a list of sheet names and hyperlinks to those sheets
Dim c, d, rCell As Range
d = 0 ‘counter used to increment rows in offset command
For Each c In Sheets
Set rCell = ActiveCell.Offset(d, 0)
If rCell <> “” Then Exit Sub ‘stop the macro if the cell contains anything
If c.Type <> 3 Then ‘3 = Chart – only create entry if sheet is NOT a chart
rCell.Value = c.Name
rCell.Hyperlinks.Add Anchor:=rCell, Address:=””, SubAddress:= _
“‘” & c.Name & “‘!A1”, TextToDisplay:=c.Name
d = d + 1
End If
Next c
End Sub


Extras

The version below uses the Worksheets collection, which automatically ignores charts. After following a hyperlink pressing F5 followed by Enter will return you to where you were.

Sub SheetListHyperLinkv2()
‘This macro creates a list of sheet names and hyperlinks to those sheets
Dim c, d
Dim rCell As Range
d = 0 ‘counter used to increment rows in offset command
For Each c In Worksheets
Set rCell = ActiveCell.Offset(d, 0)
‘stop the macro is the cell contains anything
If rCell <> “” Then Exit Sub
rCell.Value = c.Name
rCell.Hyperlinks.Add Anchor:=rCell, _
Address:=””, SubAddress:=”” _
& “‘” & c.Name & “‘” & “!A1”, TextToDisplay:=c.Name
d = d + 1
Next c
End Sub

Print Title Tips

How do I set up titles when printing in Excel? I can see the area I need to change but it is ‘greyed out’ in the Sheet tab of the Setup section of Print Preview?

By Neale Blackwood

This is a common problem in Excel. To put titles ie; Rows to repeat at the top of the page and Columns to repeat on the left of the page when you print out sheets, you need to go into the File menu and click Page Setup and then click the Sheet tab. The screen looks exactly the same as the Sheet tab in the Setup of Print Preview but you can enter your references in the input areas. You can include a Page Setup button on your toolbar if you use titles frequently. To do that right click the Toolbar, select Customize, click the Commands tab, then scroll down in the Commands section of the File Category until you see the words Page Setup. Click, hold and drag “Page Setup…” to your toolbar.


Extras

In Excel 2007 Print Titles can be directly accessed through the Page Layout Ribbon tab in the Page Setup section.

In Excel 2003 and earlier if you change your View in the view menu to Page Break Preview then when you right click on the sheet you can select Page Setup to amend the titles. In Page Break Preview there are extra right click options relating to printing.

Merged cells alternative

When copying and pasting cells, I sometimes get an error message that says:

By Neale Blackwood

Merged cells can cause difficulties when using Excel. There is a standard tool on the toolbar called “Merge and Center”. Many people use this tool to format reports, as it “merges” two or more cells into one and then centers the text horizontally within the merged cell. This can cause a number of problems.

One of these relates to the question above, as it affects how you can copy and paste. Merging also affects how you can select ranges since you can’t select or enter data into an individual cell within a merged cell – apart from the cell to the left or top of the merged cell.
There is an alternative to using the “Merge and Center” toolbar icon but it only works horizontally. You can, however, merge cells vertically or vertically and horizontally simultaneously.

This alternative is “Centre Across Selection”. Select a range of cells in a single row. Click the Format menu, click Cells, click the Alignment tab, click the Horizontal drop down arrow and select “Centre Across Selection”. Click OK. This format works exactly the same for display purposes as “Merge and Center”, but doesn’t have drawbacks mentioned above.

Note: If the cell to the left of the range you selected was empty, you will not see a difference until you enter something in that cell. When you use “Center Across Selection” you can select and enter data into an individual cell within the “merged” area. Unfortunately, there is not a toolbar button for “Centre Across Selection”. To simplify using it you could create a style, or record a macro and then assign the macro to a toolbar button.

Refering to cells within a range name

I use range names for rows. How do I reference a specific cell in a named row?

By Neale Blackwood

When you have a range name that refers to a whole row, its value will depend on where you use the name. Assume row 2 is name Sales. If you enter =Sales into cell E5, the value displayed will be the value of cell E2.

It will match the corresponding column in row 2. This relationship applies to other sheets of the workbook as well.

Here are two techniques to refer to a specific cell within a named row range. They allow you to select a specific cell from the named range.

Still assuming you have a range named Sales that refers to Row 2. Cell A2 contains the text Sales. Cell B2 contains 100, C2 has 200 and D2 has 400. To refer to cell B2 you could use =INDEX(Sales,0,2). This will display 100. The 2 in the INDEX formula refers to the second column within the range. To refer to cell D2 you would use =INDEX(Sales,0,4)

Another technique is to name columns. Assume Sales is named as above and that column B is named Jul, column C is named Aug and column D is named Sep.
To refer to cell B2 you would use =Sales Jul. The single space between the two range names instructs Excel to find the intersection of the two ranges. =Sales Sep will refer to cell D2.

To easily name a range, first select your range, then click in the Name Box (the drop-down box to the left of the formula bar, above the column headings) and type the name and then press Enter. You must press Enter or the name will not be created. You can’t use spaces in range names, but you can use the underscore “_”. For example, Tax_Rate. You should also capitalise at least one letter of your range name. When you enter a range name in lower case in a formula, Excel will change the capitalisation of the name if it recognises it. If you make a typing error, Excel will not capitalise the name, which then makes debugging the formula easier.

You can easily insert names in formula by pressing the F3 key. This will display the Paste Name dialogue box. Double click the name to insert it in your formula. Ctrl +F3 will display the Define Name dialogue box which enables you to modify or delete existing names.


Extras

If you have data in a table layout you can automatically creates names based on the column and row headings by using Ctrl + Shift + F3 this opens the Create Names dialog.

Array formula to SUM rounded values

I format many of my reports to zero decimal places. This means that in some cases the SUM function will show a different total than the displayed numbers indicated. If I format two numbers 5.5 and 7.5 with zero decimal places, they will display as 6 and 8 respectively. When summed that result is 13. Can I get the SUM function to SUM the displayed values, in this case 14?

By Neale Blackwood

The solution is your problem is an array formula. Many of the limitations of Excel’s functions can be overcome by using an array formula. The down side of using array formulae is that they can slow down calculation time.

There are two types of array formulae. Single cell and multi-cell arrays. We will use a single cell array formula to solve the problem above. When you enter, or edit, an array formula you must press the Ctrl, Shift and Enter keys all at once to accept the array formula. This is commonly written as CSE. Excel will then insert brackets { } around the formula.

Array formulae do not conform to the normal conventions of formulae. If you do not use the CSE keys you will usually get an error message or an error displayed. Single cell array formulae normally work on ranges of cells in a way that normal formulae cannot or would require many formulae to achieve the same result.
Assuming A1 contains 5.5 and A2 contains 7.5. An array formula in cell A3 that will solve the above problem is: =SUM(ROUND(A1:A2,0).
You must hold the Ctrl and Shift key and then press the Enter key after typing the formula to insert the array formula. It will display as:

{=SUM(ROUND(A1:A2,0))}

Basically this formula allows you to ROUND a range of cells simultaneously and then SUM them. If you enter the above formula without CSE then a #VALUE error is displayed. To correct it, just select cell A3 and press F2 and then use CSE. A single cell array formula can be copied like any other formula.
If you are interested in learning more about array formulae try a Google search for Excel array formula.


Alternative Solution

Another (simpler) solution is to use SUMPRODUCT(ROUND(A1:A2,0))
This gives the same result and removes the need for array formulas since the SUMPRODUCT formula is essentially an array formula.

(Thanks Steve Zabiela!)

 Stop Zeroes Displaying

Is it possible to stop zero values displaying in a range?

By Neale Blackwood

That can be achieved using a Custom Number format. First select the range, then click the Format menu and select Cells (Ctrl + 1 will open the Format Cells dialog box). Click the Number tab and select Custom from the list. Enter the following format in the Type box:

#,##0;#,##0;

This Custom Number format will not display zeroes. It has zero decimal places and uses the comma separator for thousands.

The first selection of the Custom Number format is for the positive number format. A semi-colon is used to separate the different sections of the format. The second section is for the negative number format. The third section is for the zero format. Because there is nothing after the last semi-colon, nothing will display for a zero value. You can see examples of other number formats by scrolling down the Custom format list.


Extras

In Excel 2003 and earlier versions you can also go to theTools menu, under Options in the View tab you can deselect the Zero Values checkbox to stop zeroes displaying in the whole sheet.
In Excel 2007 this is in the Round Office button in Excel Options under the Advanced section under Display options for this worksheet.

The Format dialog can be accessed via the Home Ribbbon tab in Excel 2007.

Paste Linking

I use Paste Link frequently. Is there a quick way to access it instead of going through Paste Special each time?

By Neale Blackwood

Unfortunately Paste Link doesn’t have a toolbar icon. However, when you drag a selection with the right mouse button there is a “Link here” option, which is the same as Paste Link. This is handy if you are linking within the sheet. If you are linking to other sheets, there is a simple macro that performs Paste Link after you have copied. You could assign the macro to a toolbar button to speed up your use of Paste Link. The macro is:

Sub PasteLink()
ActiveSheet.Paste Link = True
End Sub

When you Paste Link a single cell, Excel will use an absolute cell reference – for example $A$1. When you Paste Link ranges Excel uses relative cell references such as A1:A10.

Note: Excel XP and Excel 2003 have expanded paste options that include Paste Link.

View Values and Formula at the same time

Is it possible to view cells’ values and formulae simultaneously?

By Neale Blackwood, 1 Dec 08

There is a technique that lets you see the formula and the value at the same time. This is often useful when creating or debugging a spreadsheet. Open a file with formula and values in it. To make it easier ensure that only one file is open. Select the sheet you want to view. Click the Window menu, click New Window. Click the Window menu again and then click Arrange. Select Vertical and click OK. You should now see two windows of the same sheet side by side. Select one of the windows and press Ctrl + `.

The ` key is above the left Tab key on a normal keyboard. This command is a toggle as it turns the display of formulae on or off each time you press it. You should now see the values and the formula side by side.

First published: CPA AUSTRALIA Magazine INTHEBLACK December 2004 – Page 14 Excel Yourself

Full Path name in Footer

How can I put the full path name of my file in the footer without typing it?

By Neale Blackwood

Excel XP and 2003 allow you to easily insert the full path in your footer using the Page Setup, but unfortunately earlier versions don’t. In Excel 97 and 2000 you can use a macro to add the full path to the current worksheet.

To automatically insert the full path in the left footer, follow these instructions (note: this macro will overwrite whatever is in the left footer).

  1. Right click the Excel symbol to the left of the File menu.
  2. Select View Code.
  3. In the left-hand drop down box (below the toolbar) select Workbook.
  4. In the right-hand drop down box select BeforePrint

(you may have to scroll up to see it).

Excel should have automatically inserted the first and last commands of the BeforePrint event macro. You need to insert a command and make it look like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter= Active Workbook.FullName
End Sub

Excel may have also included a Workbook_Open macro. You can delete the two lines associated with it. This macro will run before you print or use print preview. Hence you can test it without having to use up paper by simply using print preview.

These event macros are powerful and useful. You can set up macros to run when you open a file or when you close a file, as well as the other events included in the right-hand drop down box.

Be warned that these macros can slow down operations within the file. Also be careful in their use as they can return error messages in certain circumstances. If you have used macros in the past then these event macros may allow you to extend your developmental possibilities.


Extras

This technique can allow you to insert the contents of a cell into your Header or Footer as well. If you name the cell it makes it easier to do.
If you name a cell FooterText, then this code will insert it in the Footer each time it prints.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter= [FooterText].Value
End Sub

Multiply a range of cells by a factor

I have a range of cells & I need to multiply all the cells in the range by a factor. Is there is any easy solution?

By Neale Blackwood

Solution 1: Formula & Range Name

One way is to use a range name. First enter the factor value in a cell and name that cell Factor.

The easiest way to create a name is to use the name box above the A column heading and below the ribbons – it typically displays the current cell reference. Simply type Factor in the name box and press Enter.

Then in another cell enter the formula:

= Factor

– then press Enter. This cell should display the factor value. Now copy the cell that has =Factor in it. Then select the range you want to multiply by the factor.

Right click the selected range and select Paste Special. Then select Multiply and click OK.

Now all the cells in the range will have *(Factor) added to their formula.

You can change the value in the cell named Factor and the other cells’ values will change.

Solution 2: Paste Special Values Multiply

Maybe only need to do the multiplication once: eg convert positive to negative numbers (or vice versa).

  1. Type the conversion factor in a cell e.g. -1
  2. Copy that cell
  3. Select all the data you want converted
  4. Go to Paste Special & check Values and Multiply

This will multiply all your data by the conversion factor.  Once that’s done, delete the conversion factor.

Turning off Drag and Drop automatically

I have an input spreadsheet that I distribute to users. I have used sheet protection to ensure users can only change input areas. The problem is users are still able to drag and drop within the input areas, which causes #REF! errors. Is there a way to stop users dragging and dropping?

By Neale Blackwood

This solution works on Excel 97 and later versions. In the Tools menu under Options, in the Edit tab there is a check box to “Allow cell drag and drop”. If you uncheck that option it will stop the user from using drag and drop. You can use a macro to turn this option off when you open the file and another macro to turn it back on when the file is closed. To insert the macros, right click the Excel symbol to the left of the File menu and select View Code (bottom of list) and then enter the following two macros in the blank area:

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
End Sub

Users will still be able to cut and paste. The user will not see the arrow-shaped mouse cursor that is used for drag and drop operations. These macros will not stop users from manually turning the drag and drop option back on. This macro does affect the user’s Excel environment. After the user has opened the file the user can’t drag and drop in any open workbook. This may be confusing to some users. This constraint can usually be minimised by instructing users to: open the input file, make the necessary changes and then save the close the file – do not leave the file open while working on other files unless necessary for the entry of data into the input file.


Extras

This requires that the user turns on the macros when he opens the file.
In Excel 2007 the way this happens has changed so that the user can open the the file and use it and not realise there were macros to turn on. There is a section above the formula bar that displays when there are macros in the file and asks whether to enable them. Some users do not notice this.

Shading rows automatically

I’ve seen spreadsheets with shading on every second row. Is there an easy way to do this, other than manually formatting the rows?

By Neale Blackwood

Conditional Formatting is the easy way to achieve this effect. Using alternate shading makes reading wide spreadsheets easier. First select the range you want the effect to apply to. Then click the Format menu, select Conditional Formatting. In the drop down box on the left, select Formula Is. Then enter the following formula in the box on the right.

=MOD(ROW(),2)=1

Then click the Format button, select the Patterns tab and select a colour shading to apply to the rows. Click OK and then click OK again. The shading should then be applied. When you insert or delete rows the shading will automatically change.

The MOD function above returns the remainder of the first element divided by the second element. Hence, it divides the row number by two.

When the result is one – for every odd-numbered row – the shading is applied. Even-numbered rows will have no shading applied. To change the shading to even-numbered rows in the formula above, change the 1 to a 0.


Extras

Excel 2007 introduced a built-in table format which does the alternate row shading. See the Home ribbon tab, Style section, button for Format as Table. If you don’t want to use the Table format see below.

To use the above formula in Excel 2007+ click the Conditional formatting button, click New Rule, Click Use a formula to determine which cells to format. Enter the above formula in the box and click the Format button, click Patterns tab and select a colour click OK twice.

Excel 2007+ has no limit on the number of conditional formats you can apply. Previous versions were limited to three.

Hiding and unhiding quickly

I frequently hide and unhide the same sets of rows in my sheet, so that the detail doesn’t print on my reports. Is there an easier way to do this?

By Neale Blackwood

The feature that would make hiding and unhiding specific rows easier is called Group.

Select the rows you want to hide and click the Data menu, then click Group and Outline, then click Group. This will place a small minus sign to the left of the row numbers. You can click the minus sign to hide the rows and then press the small plus sign to view them. This technique also works for columns. Select the columns you want to hide before following the same steps above. The small minus and plus signs are displayed above the column letters. You can press the F4 function key to repeat the Group feature on other ranges once you have grouped one range. F4 repeats most Excel commands.


Extras

Excel 2007 has Group in the Data Ribon tab in the Outline section.

Financial Year formula

Is there an easy formula to determine the financial year from a date?

By Neale Blackwood

The following formula will calculate the financial year from a date in cell A1, assuming a July to June financial year that uses June year to refer to the financial year. For example, June 2005 is in the 2005 financial year.

=IF(MONTH(A1)<7,YEAR(A1),YEAR(A1)+1)

Format error cells

Is there an easy way to highlight all the cells that have error messages

By Neale Blackwood

There are a couple of ways.

Select cell A1, press F5, click the Special button. Select the Formulas radio button and uncheck all the entries under it except Errors. Click OK. You now have all the error messages selected and you can use Format Cells to select a colour for them.

The second method highlights errors automatically. This method uses conditional formatting and assumes you have no other conditional formats set up in the sheet.

To check to see if there are conditional formats in sheet select A1, press F5, click Special, and select the Conditional formats radio button and click OK.

Automatically highlighting errors will overwrite all other conditional formats. To proceed press Ctrl+ A to select the whole sheet. Then click the format menu and select Conditional Formatting. If there are entries or formats already entered, it means there are conditional formats in the sheet and it would be wise to click Cancel. Use the drop-down box to select “Formula is”. In the box to the right of Formula is, enter the formula:

=ISERROR(A1)

Then click the Format button and click the Patterns tab and select a colour. Click OK and OK again. Now all errors will be formatted with the colour you selected as they occur. They will revert to normal formatting when the error message disappears.


Extras

To use the above formula in Excel 2007 click the Conditional formatting button, click New Rule, Click Use a formula to determine which cells to format. Enter the above formula in the box and click the Format button, click Patterns tab and select a colour click OK twice.

3D SUMIF formula

Is it possible to do a 3D SUMIF? By that I mean to SUM a column of values based on the codes in another column through multiply sheets?

By Neale Blackwood

Yes, but the formula is complicated. Assume three sheets named, Jul, Aug and Sep. In a fourth sheet you want to do a SUMIF in all these sheets to calculate a total for the first quarter. The sheets all have the same layout. Column A contains the code you want to match to, and column B contains the value you want to SUM based on the code in Column A. Cell A4 contains the code to which you’d like to match. Here’s the formula, and note it uses the curly brackets {} of an array formula within the formula itself. This is not an array formula; it is entered normally. The formula for a 3D SUMIF is:

=SUM(SUMIF(INDIRECT({“jul”,”aug”,”sep”}&”!A:A”),A4,
INDIRECT({“jul”,”aug”,”sep”}&”!B:B”)))

To include more sheets, just insert more names separated by commas within both sets of the curly brackets {}. The references “!A:A” and “!B:B” refer to full columns. I did this to shorten the formula and make it easier to read. Referring to full columns is inefficient and would slow the calculation time down if used extensively. It is more efficient to use actual ranges such as “!A1:A1000” and “!B1:B1000”.
If your sheet name are numbered, it may simplify the formula slightly. For example, if the sheets are named M1, M2 and M3 the formula would be:

=SUM(SUMIF(INDIRECT(“M”&{1,2,3}&”!A:A”),A5,
INDIRECT(“M”&{1,2,3}&”!B:B”)))

The INDIRECT function allows you to build cell references from text. The curly brackets work as an array and allow you to create multiple references. The SUMIF function works as per normal using the three separate references created by the INDIRECT functions. The SUM function adds up the three SUMIFs. If you didn’t have the SUM function, the result is a SUMIF on the first sheet in the series only.

 

Today’s date formula

I use =NOW() to update today’s date in a spreadsheet. The problem is that it includes a fraction for the time of day. How can I get just the date without the associated fraction?

By Neale Blackwood

Excel treats dates as numbers, and the time of day is treated as a fraction of the day’s number. The NOW function returns the day with a fraction for the time. The TODAY function returns just the date with no fraction and is the solution to your problem.

Should you wish to remove a fraction from a number, you can use the TRUNC function. This is short for truncate; it will cut off any fractions. So=TUNC(NOW()) will give same result as =TODAY(),which is today’s date with no fraction. TRUNC has an option to Truncate after a specified number of decimal places, similar to ROUND.

For Example:

=TRUNC(1.99,1)

will give you 1.9

=TRUNC(1992,-2)

will give 1900

When you don’t specify the number of decimal places it defaults to zero.

 


Extras

Enter =TODAY() into the cell to use the TODAY function.

Protect and unprotect sheets macro

I frequently protect sheets without using a password to ensure users don’t accidentally change something they shouldn’t. Is there an easy way to unprotect all the sheets in a workbook and then protect them again?

By Neale Blackwood

There is no built-in method to protect or unprotect all the sheets at once, but you can use some simple macros to make the job easier. These all assume no password is used. Note: chart sheets will not be affected by these macros.

The following code will protect all the sheets in a workbook

Sub ProtectAllSheets()
Dim sht As Worksheet
For Each sht In Worksheets
sht.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Next sht
End Sub

The following code will unprotect all the sheets in a workbook

Sub UnprotectAllSheets()
Dim sht As Worksheet
For Each sht In Worksheets
sht.Unprotect
Next sht
End Sub

To protect the active (current) sheet use

Sub ProtectSheet()
ActiveSheet.Protect DrawingObjects:=-True, Contents:=True, Scenarios:=True
End Sub

To unprotect the active (current) sheet use

Sub UnprotectSheet()
ActiveSheet.Unprotect
End Sub

Using MAX and MIN instead of IF function

I must calculate the time-and-a half and double-time hours from a total overtime figure – in column C. The first two hours of overtime are time-and-a-half, the rest is double-time. Can you provide IF functions to do the calculations?

By Neale Blackwood

Many of the questions I receive are based on IF functions and how to use them correctly. Assuming the first row used is Row 2, the formula for time-and-a-half would be

=IF(C2>2,2,C2).

The formula for double-time would be

=IF(C2>2,C2-2,0). In the above case it is possible to calculate both overtimes without using an IF function. The time-and-a-half alternative formula is =MIN(C2,2) and the double-time formula is =MAX(C2-2,0). In both of the above solutions you could also calculate double-time by subtracting the time-and-a-half amount from the total overtime amount in column C.
You can use the MIN and MAX functions to perform some basic logic calculations without using an IF Function. There are times when you need to use only the positive numbers in a list and zero the negatives. Assuming cell A1 contains a number the following formula will display a positive number from A1, but will display 0 if A1 has a negative. =MAX(0,A1).

Change default Cell comments font size

How do I change the default font size of cell comments?

By Neale Blackwood

You need to change a Windows setting to achieve this. Please note this will change the font size of other objects defined as “ToolTip” in applications. You may want to change it back, so make a note of the setting before you change it.

  1. This applies to Windows XP:
  2. Go to Start, click Settings then click Control Panel (Control Panel may already be on your Start menu)
  3. Double click the Display icon
  4. Go the Appearance tab
  5. Click the Advanced button
  6. Click the Item drop down and select ToolTip
  7. You can now change the font size and colour
  8. Click OK, click Apply and then click OK again

All new comments will have the new default settings, but existing comments will not change.

Stop zeroes displaying on line Charts

I have a line chart which has all the months of the year displayed. Each month is linked to a data cell. This means that future months display zero values. The line chart drops down to zero for these future months. Instead of having to change the chart range each month I want to stop the chart plotting the zero values for the future months. Is this possible?

By Neale Blackwood

You can use Excel’s feature of not plotting error values on charts to stop it displaying the zero values. You need to modify the formula to display #N/A if the value is zero. For example if the cell contains the formula =C2 you would amend the formula to =IF(C2=0,#N/A,C2). Do not use inverted commas around the #N/A as this will not give the desired result. Another option could be to hide the columns or rows involved. If you hide columns or rows in the chart’s source data then none of the hidden data appears on the chart.

Formula to stop Error messages

I have a formula that returns an error message. Can I get it to display a blank cell instead?

By Neale Blackwood

Let’s assume that cell A1 has some text in it and B1 has a number in it. A1 sometimes contains numbers and then we need to multiply A1 by B1. The formula =A1*B1 will return a #VALUE! error message when there is text in A1. To avoid this here are three solutions that apply to all Excel versions.

=IF(ISTEXT(A1),””,A1*B1)

=IF(ISNUMBER(A1),A1*B1,””)

=IF(ISERROR(A1*B1),””,A1*B1)

Note: “” displays a blank cell.

The third solution will stop all errors showing. Eg if cell B1 contains text, the first and second solutions will display the #VALUE! error message, whereas the third solution would still display a blank cell.

Because the second solution is used so frequently, a new function was added to Excel 2007 to handle it more elegantly. In Excel 2007 the third solution can be written

=IFERROR(A1*B1,””)

This formula displays the result of A1*B1 unless there is an error, when it will display a blank cell. This saves having to duplicate the formula.

Warning: Because IFERROR is a new Function it cannot be used in previous versions of Excel. Cells with the IFERROR function will display #NAME! error message in Excel 2003 and previous versions.

Using Text Boxes on Charts

I use text boxes to put notes onto charts. But if I move the chart the text boxes don’t move with it. Is there an easy way to achieve this?

By Neale Blackwood

There are a couple of solutions to this problem. The first uses the Ctrl key to select multiple objects. Click the first text box. Then hold the Ctrl key and click the next text box. Keep holding the Ctrl key down and select the chart, making sure it’s the whole chart and not a component of the chart. All objects are now selected. Release the Ctrl key. If you move the chart the text boxes will now move with it. This works for any graphic objects that you want to move together. Click outside the chart area to deselect the objects. If you had held the Ctrl key down as you moved the chart, it would have copied the chart and text boxes.

The other solution allows you to link the contents of the text box to a cell. Click the whole chart then enter = in the formula bar (the space above the column letters and below the toolbar), then click a single cell on the sheet and press Enter. It works best if there is already an entry in the cell. You can move the text box anywhere on the chart and it will always stay in that position when the chart moves. This can be useful to put dynamic notes on charts that change based on text formula in the linked cell. Also, you can link total cells from the chart data for stacked column or bar charts.

Standard text boxes can also be linked to cell contents in the same way. Click the text box and type = in the formula bar and select a cell to establish the link.

Related Articles

Link Text Box contents to cell contents – Text box feature

Text boxes are useful because you can easily place them anywhere on your spreadsheet. You can link the contents of a text box to a cell. First draw a text box on your sheet …
4 Tips to Chart like a Pro – Make your Charts Zing!

Charts are a great way to communicate information in a way that is easy for people to digest and understand. Here are four tips to help you make charts that zing!
Label a single point on a graph …

Pasting Values made easy

I use Edit > Paste Special > Values frequently and was wondering, what is the easiest way to do it?

By Neale Blackwood

The easiest way to paste values is by dragging the range or cell with your right mouse button. When you release the button the menu has ‘Copy Here as Value Only’.

When you drag a range or a cell you need to have the arrow cursor shape visible. This method can also be used if you are copying the values on top of the existing formulae.
Simply drag the range or cell one cell across or down with the right mouse button and then, without releasing the button, take it back to the original range or cell and then release the button. When you use ‘Copy Here as Value Only’ the formula will be replaced by the values.
There is also a Paste Values toolbar icon that you can put on your toolbar. In Excel XP onwards the Paste toolbar icon has a drop-down menu and Values is one of its options.

Hiding cell contents

I use the white font colour to hide values in a cell, but if the cell’s fill colour is changed, it displays the entries. If you select the cells you can also see them. If Black and White is selected in Page Setup the entry will print. Is it possible to stop the values from displaying and printing?

By Neale Blackwood
There is a custom format that will stop the contents of a cell displaying and printing regardless of the fill colour used. The custom format is ;;; (three semicolons in a row).

To set up a custom format click the Format menu, then click Cells and make sure the Number tab is showing. Click Custom at the bottom of the Category box. Delete whatever is in the Type box and enter ;;; then click OK.

It is a good idea to protect any cells that aren’t displayed to stop accidental deletion.

If you use this format frequently you can create a style to make it easier to apply the format. To create a style click the Format menu, click Style. In the Style box enter Hide and click the Modify button. Create the custom format as above and click OK and click the Add button. The Hide Style has been created and is now available to use via Style on the Format Menu or via the Style drop-down box, which can be added to the toolbar.

Formatting a cell using a style means that you can change the cell format at any time by modifying the style. Hence, you could turn the hide off and on simply by modifying the style. Please note that styles are only available in the workbook in which they are created. If you want a style available in all workbooks that you create, then you can create a style in a template and use that template to create new sheets. Search for Merge Styles in Excel’s Help if you want to copy styles between workbooks.

Follow link by double clicking

I’ve used spreadsheets that let you follow a link in a cell by double-clicking on the cell, but I can’t find the setting that makes this work. What setting do I have to change?

By Neale Blackwood

The setting you need to change is named after another feature, which is why it’s hard to find. Click the Tools menu, click Options then select the Edit tab. You need to uncheck the Edit directly in cell’ option. When this option is turned off it allows you to double-click on the cell and Excel will follow the link to its source. If the link is to another sheet it will go to the sheet. If the link is to another file it will open the file. This is a useful technique to follow links and find out how a spreadsheet is built.

Another useful feature is to press F5 and then press Enter to return to the cell that you double-clicked. If you leave the option checked it means you can edit the cell contents within the cell itself. Most users tend to use the formula bar to edit their cell contents, so disabling this setting isn’t a major change.

Fixing downloaded data

I’ve imported data into Excel and I’ve tried to do a VLOOKUP on the data but it doesn’t return any results. What could the problem be?

By Neale Blackwood

It is common when importing into Excel that the data has leading or trailing spaces. These additional spaces can stop your VLOOKUP from returning the result you were expecting. Leading spaces are easy to see because the text is not left aligned. Trailing spaces are harder to identify because when you look at the data it appears correct.

You usually become aware of trailing spaces when you edit a cell and see the cursor a few spaces to the right of the characters in the cell. The TRIM function will remove leading and trailing spaces. Any spaces within the text are unaffected. If A1 contained the text “test data ” the formula =TRIM(A1) would return “test data”.

To remove leading and trailing spaces in a data column you would use another empty column and insert a TRIM function referring to the first cell in the data column and then copy it down as far as the data.  Then copy the column with all the TRIM functions and Paste Special > Values on top of the data column to remove the leading and trailing spaces. Your VLOOKUP should then work.

If that doesn’t work then the problem may be that the data is formatted as text. For example, numbers are left aligned instead of right aligned. If that is the case, you can insert an apostrophe (‘) to the right of the code you are using as your lookup value. This will treat it as text and match the data.

This should allow your VLOOKUP to work!

Fix for missing Fill Colours

Excel won’t display the cell’s fill colours. The colour is there when I print out, but it doesn’t display on the screen. The file is OK when I view it on another computer. Is there a setting I need to change?

By Neale Blackwood

You need to change a Windows setting. Click Start, then click Control Panel, and open the Accessibility Options. Click the Display tab, and then click to clear the Use High Contrast checkbox. Click OK to close the Accessibility Options dialog box. You may need to restart Excel or your computer for the change to take effect.

Counting non-blank cells

I have a range of IF functions that return “” in certain circumstances. I want to count the number of cells without “”, but COUNTIF won’t do it. Is there a way to count the non-blank cells in the range?

By Neale Blackwood

The SUMPRODUCT function can count non-blanks. Assuming the range is C1:C20 the following formula will count the non-blanks, or those cells without “”.

=SUMPRODUCT((C1:C20<>””)*1)

The <> symbols in the formula mean not equal to.
You could also use this formula:

=COUNTA(C1:C20)-COUNTIF(C1:C20,””)

COUNTIF will count blank cells but not non-blank cells.

Print all sheets in one step

Is it possible to print all the sheets in a workbook in one step?

By Neale Blackwood

To print all the sheets, right click any of the sheet tabs and choose Select All Sheets. Then click the print button. As a precaution you should first click the Print Preview button to ensure you aren’t printing more pages than you are expecting. Remember that Excel will print out everything on a sheet unless you set your print area. If you only want to print out a few sheets then hold the Ctrl key down and click the sheet tabs you want to print, release the Ctrl key and click the print button.

Please note: when you select multiple sheets you will see [Group] appear in your Excel title bar at the top of the screen. This is warning you that when you edit you are now editing every sheet you have selected or grouped, so it is important to ungroup the sheets by either selecting a sheet that wasn’t previously selected or by right clicking a sheet tab and then selecting Ungroup Sheets. [Group] should then disappear from the title bar.

Lining up charts and other graphics

Is there an easy way to position charts so that they line up exactly on the left-hand side?

By Neale Blackwood

If you hold down the Alt key (next to the space bar) when you move the chart around you will see that it snaps to the cell grid and makes it easy to line up. This also works for text boxes and other graphic objects you move around on the sheet.

 

Enter & symbol in the Header or Footer

How do you print the & (ampersand) character in the Header?

By Neale Blackwood

The & character is used to define certain information that can be printed in Headers and Footers.

To print the & character itself use &&. Hence, if you want to print Smith & Jones in the Header you would type Smith && Jones.

Change print page to A4

We receive worksheets from other divisions that have the paper size set to Letter. This affects how the pages print out on A4 paper. Is there an easy way to convert all the sheets in a workbook to the A4 paper size?

By Neale Blackwood

The macro below will adjust the paper size to A4 for every sheet in the active workbook.

Sub A4Paper()
Dim sht As Worksheet

For Each sht In Sheets
sht.PageSetup.PaperSize = xlPaperA4
Next sht
End Sub

SUBSTITUTE function example

I have a column of codes that have three hyphens eg WT-123-456-789. I need to replace the first hyphen with a space, but leave the other hyphens alone. The first hyphen comes as the third, fourth or fifth character. Is there an easy way to do this?

By Neale Blackwood

The SUBSTITUTE function will allow you to do this. Assuming the code is in cell A1, this formula will replace the first instance of the hyphen with a space.

=SUBSTITUTE(A1,”-“,” “,1)

The 1 at the end of the formula specifies the first instance of the text to be substituted. A 2 would replace the second hyphen with a space and leave the first space. The instance number at the end of the formula could be a cell reference that has an IF function in it to change the instance number based on code length, or some other criteria.

You can use multiple SUBSTITUTE functions to perform more complex replacements. To replace the first and third hyphen with a space you could use the following formula.

=SUBSTITUTE(SUBSTITUTE(A1,”-“,” “,1),”-“,” “,2)

This formula will leave the second hyphen in the code.