Student Excel webinar - your questions answered

We recently ran a record-breaking webinar for ACA and other ICAEW qualification students, with almost 1,000 live in the audience.  In this blog post I will be going through the large number of questions we received, including those we didn't have time for on the day.

You can download an example file containing all the pictured solutions at the bottom of this post.

Is this webinar recorded for future reference?

Yes, the recording is available here.

Is there an Excel workbook that we should have open?

The workbook that I presented from is available to download from the webinar resources should you want to follow along with the recording.

If I have completed my CFAB qualification, but not progressing further with my study, can I still register for the Excel Community?

Many jobs require proficiency in these excel functions and more - e.g. test you on these proficiencies at the interview stage. Can you please provide websites with examples to work through so we can gain proficiency?

While students get Excel Community for free while they're studying, anybody can sign up for the Excel Community.  Find out more here.

I was wondering if there is an easy way to stack up variances month to month (current year to prior year) in a pivot table. For example amount of sales Jan 2020 and variance % from sales Jan 2019 and then Feb 2020 Feb 2019 so on.

This should do it:

  1. Insert the PivotTable with Dates as the Row Labels and Sales as the Values
  2. Group the dates by month and year
  3. Use the menu to move the year element to a Column Label instead
  4. Click on Sum of Sales and go to Value Field Settings
  5. Choose Shows Values As => % of => Date (Year) => (previous)

Are there any functions in Excel to count letters/signals? (I mean not numbers)

Yes - COUNTIFS can do this, e.g.:

=COUNTIFS(range, target letter or word)

How did you enter criteria for SUMIFS and COUNTIFS?

When entering the inputs to a formula, you can either use your mouse to drag and select the cells you want in the range, or use the arrow keys on your keyboard to do the same (holding Shift to select a range).

What is the difference between SUMIF and SUMIFS? Both return the same result.

When you have just a single criterion, they're the same - but SUMIFS lets you have more than one.  The inputs for SUMIF are in a different order to SUMIFS, so I recommend only ever using SUMIFS even if you only have a single condition, so it's easier to add more later if you need to.

If you have 2 columns of debits and credits, all positive, is there an easy way to move all the credits into the debit column as negative values?

You can use an IF to sort these out:

I'm wondering if I can email you some questions?

Excel Community members can post questions in our Discussions forum, or they can send questions to excel@icaew.com.

I couldn't find the free access to the faculties as you mentioned. It seems like there will always be a standard cost.

The special link for students to sign up is here.

When you are showing examples it shows criteria/range etc in a little box under the cell - how you do get it to do that?

This is the default behaviour for Excel - check that you haven't somehow turned off the ToolTip in Excel Options.

When using the TODAY formula, is there anyway of freezing it for next time I open the spreadsheet?

No - like all functions, TODAY is updated automatically.  If you just want a static date, you can quickly type it with Ctrl ;

How do you add a number of days to a specific date?

You can just do a straight addition:

Does the date formula include the date of purchase?

Date subtraction asks the question "How many days from point A to point B" - it won't include the start date in the total.

Is there a way we can set up a cell at the top of the sheets in a workbook, to show the last date that individual sheet was edited?

Only by writing a VBA function; a better way would be to have users update a cell when they make changes, or to share the file via SharePoint or a similar file-sharing system that keeps an edit history for all users.

Is there a separate formula to use to add years instead of months?

No, the EDATE function is used for both.

Hi, does Excel recognise British or American date formats?

It uses the same date system as your computer - so whatever you have set.

How can we easily reconcile bank statement with bank ledger? Debits column of bank ledger with credit side of bank statement and voice versa?

There's no one way to do this, but tools such as lookups, totalling with SUMIFS, or PivotTables - all covered in the webinar - can be a big help.  It depends a lot on what your data looks like and what you need to check.

Is it mandatory to use = or can we also use the prefix @ for the formulas to be invoked?

It has to be =.  Some simple arithmetic functions that start with a + or - will be recognised as functions by Excel, but even then it will just write the = in for you.

Sometimes my Excel can't read list of dates in the format mm.dd.yyyy - such that I couldn't change it's format (long date, short date..), is that a way to go around it?

Often I find data changes from DD/MM/YYYY to MM/DD/YYYY - is there a quick way of restoring this to DD/MM/YY?

Could you use a find and replace to correct the date format from a "." to a "/" ?

Any kind of date format that Excel doesn't recognise will usually lead to the value being stored as a date.  In this case, use Data => Text to Columns to separate out the three elements of the cell, then recombine them using =DATE:

For specifically US-format dates, this won't necessarily work, as US format dates like 01/31/2020 won't be recognised by Excel and will remain as text, while dates such as 05/06/2020 (May 6th) will be misinterpreted (as 5th June).  There is a solution using Power Query, but it's more involved - but I have an upcoming Excel Tip of the Week about it in the calendar for the end of June!

A find-replace can work depending on the format.  Make sure to select the data first so you don't replace every . in the sheet!

Can you show a formula that returns the month (in number) from a specific date?

MONTH does this:

Re. Finding a month as you have just shown. Can you then SUMIFS based on that month?

Yes, you could add a column of MONTH formulas and then use SUMIFS.  Alternatively, insert a PivotTable, add Date to the Row Labels, and right click to group by month.

Is there a logic being used in random sampling - is it based on quantity or value?

The example shown in the webinar just uses RAND functions to shuffle the items being sampled - each row is equally likely.  Weighted sampling would still use RAND but in a more complex application.

Could you show us again the formula of how many A with negative balance, combination of COUNTIFS and SUMIFS?

This was an on-the-fly example I made showing how to use multiple conditions - here's a recreation:

I would like to know by using the IF function and date functions, could we remind the user in excel that the product is 50 days old and should be replaced?

Yes - you could check if an expiry day is in the past, like this:

What keyboard function did you use to check the end of the data?

Is there a keyboard short cut to get to the bottom of a large data set?

You can quickly zip to the end of a block of data by holding Ctrl and pressing the appropriate arrow key.  Hold Shift too and you will select all the cells you move through.

How to C&P data to only visible cells (e.g. after filtering) in a table?

Alt ; will select only the visible cells.

With lookups if you have a long list of names, some featuring more than once can you filter so that each name only appears once?

For INDEX MATCH, can you return occurances of more than one instance of the order number?

If you have duplicates, a lookup isn't the right approach - a SUMIFS or PivotTable may be more appropriate.

Can I use MATCH to look for text in a list? If not, is there a function for this?

Yes, MATCH works fine on either text or numbers (if they match exactly).

Why are INDEX MATCH and XLOOKUP more reliable than a VLOOKUP?

Both can be used to look up data to the left of the identifier, or horizontal data, whereas VLOOKUP can only search vertically and return to the right.  Both alternatives also don't break if a column is inserted into the master table later on.

XLOOKUP is a single function, it also defaults to using the exact search method - making it easier to avoid mistakes - and has several other useful add-ons.  But for now it's only available in Excel 365.

For MATCH formula, if we have a very long list, does that mean we have to count it ourselves even though it shows a very large number?

No - MATCH will find the match value's location for you, which you can then plug in to an INDEX. 

What is difference between VLOOKUP and HLOOKUP?

Whether the search is Vertical or Horizontal.  INDEX MATCH can replace both.

For the lookup field column, I always wondered if there is a way to have 2 cells' value together but have 1 space between the values, for example when it comes to the names.

You can insert text into a combination using multiple & and any standard text between quotation marks, e.g.:

In later Excel versions, you can insert a separator character between a whole range of text inputs using TEXTJOIN.

What should we do with n/a error in VLOOKUP?

Any of the lookup functions will return this error if there is no match for the search term.  If you think that there should be a match, check that e.g. one cell is not formatted as text and another as a number.

Whats the best way of dealing with scientific numbers? i find i get rounded 0's often when dealing with account numbers

What's the best way of dealing with leading 0's? These often get removed from account numbers.

Excel only allows values of up to 15 digits in numbers - anything longer than that gets rounded off.  And it will also drop leading 0s from numeric cells.

You can label longer values or ones with leading 0s as text, either by formatting cells as Text or typing an apostrophe before the value, to prevent this happening.

How can you layer conditional formatting?

You can just apply multiple conditional formats to the same cells, one after the other.  Watch out for clashing rules!

How do we sign up for the pivot table webinar?

If there is a link to access for more info of pivot table?

It's on the 23rd June - sign up here.

Have a question on the Goal Seek function. Say we are to calc the rate of increment from two figures (e.g. $100 in FY20) and end figure (e.g. $1,680 in FY25), how can we execute the goal seek function?

This isn't really a good example of when to use Goal Seek, as it's solvable with direct calculation:

Goal Seek is used where the relationship between an input and an output is complex - e.g. how an assumption affects a financial model.  You just go to Data => What-If Analysis => Goal Seek, and then tell Excel what output value you want based on changing a target input cell, and then Excel does the trial-and-error for you.

Do you mind briefly running through the INDIRECT formula please?

INDIRECT can be used when you have something that looks like a cell reference, to tell Excel to really treat it as one.  So for example if you have a cell where a user will indicate an Excel Table by typing its name, you could then use =SUM(INDIRECT(A1)) to convert that name into the cell reference(s) it represents.

Please could you show how you made a new NET column in the pivot table?

This was added from PivotTable Analyze => Fields, Items, & Sets => Calculated Field.

How do you lock $ for both row and column?

What keyboard function did you use to lock the cell using the $ signs?

Taking a step back - $ signs are used in a reference to indicate to Excel that the reference shouldn't be changed if the formula is pasted elsewhere.  You can insert them:

  • Nowhere - A1
  • Everywhere - $A$1
  • Row only - A$1
  • Column only - $A1

While entering a cell reference, pressing F4 will cycle between those four options in that order.

Will the IF function take a hidden row when you select a range?

If we use filter to our ledger, how we can use sum function without losing the correct number ?

Almost all Excel functions use values from cells whether hidden or not.

It's worth noting that IF is designed for single-case comparisons, so you wouldn't normally select a range with it.

There is a way to double click on the formula and it goes to the cell. How can that be done?

This is just done by double clicking the formula cell to edit.  Pressing F2 also works.

Is there a way to use conditional formatting to change colour when text is written in another cell?

Yes - by going to Home => Conditional Formatting => New Rule => Use a formula to determine which cells to format:

Can you sum or count cells based on their format?

Not by default.  I've written custom functions that you can install to do this here, but even these won't work with colours from conditional formatting, which are invisible to formulas.

Is there an easy way to find a compete list of functions available?

There's a list on the Microsoft Help pages for Excel, but there are hundreds and hundreds of them, so a better approach is to explore by category via the Formulas tab, which also has a search function under Insert Function.

Is there a way of using the INDEX MATCH function to show data that isn't present? e.g. When comparing nominal ledgers to see which codes are new?

You could do this just using a MATCH - if you MATCH each code in the current-year ledger against the prior-year list, any new codes will show an #N/A! error.

When exported data is returned as text, despite being numbers, is there a way to fix the format to recognise the number?

After making sure that the cells are formatted to something other than Text format, type a 1 in a cell, copy it, select the data, and then use Paste Special => Multiply.

Can you please explain the GETPIVOTDATA function?

In brief, this function is used to extract values from PivotTables in a way that doesn't break when the Pivot is updated.  A more detailed explanation in this Tip of the Week post.

Can I create 2D maps using postcodes - i.e. client postcodes? Can I also layer these maps like a 3D map?

In later Excel versions, yes - from Insert => Map or 3D Map.

Is there an option to check number of months between two dates?

How can we find out the differences in two dates in month, year and days?

You can use DATEDIF for this - the "m" means "months", but "y" would work for years:

Although you could use "d" for days, as mentioned above simply subtracting the earlier date from the later one also works and is much easier!

Is there a way to select a random number between 0 and 1?

This is what RAND does (to fifteen decimal places).

With the RAND function, it updates every time you refresh the cell. If you wanted to take the highest 5 values for example to pick 5 random cards, would that still work just using the Data function?

Although the RAND functions will recalculate, the sorting of the data into a random order won't change until you sort the data again.

When you got the 6 for the month on the dates (month) example, is there any formula you can use that converts this 6 into the text June?

You can directly change the date to show only the month name by opening the formatting menu with Ctrl 1, and then using the custom number format mmmm.  You can do this with a formula also:

For the IF formula, instead of the usual TRUE & FALSE output generated if they meet the set criteria, can I replace TRUE & FALSE to show as a particular number instead or must the output generated must be in text format?

Yes - the example I used had text outputs, but you can put anything as the output of an IF - such as a number, a calculation, or even an entire other formula!

Anonymous