Adding up Text Numbers In Excel Another Technique

If you have a list of numbers that are a text numbers or a combination of text numbers with real numbers there is a technique I covered in this blog post to add them up. But if the range also contains text then the technique won’t work. There is the work around. The solutions below work in the subscription version of Excel. Check the comments section below for a solution for all versions.

Excel Variance Formula

IF function to the rescue

When calculating variances between actuals and budget, you typically have a positive value representing a favourable (good) variance and a negative value for an unfavourable (bad) variance. When looking at revenue and expenses together this poses a problem for the variance calculation. The calculation needs to be different for revenue and costs. Here’s a way to use a single formula for both.

Monitor Cells with Excel’s Watch Window

Don't wait, watch

When you are developing a file for a report, budget or forecast you may need to keep track of certain cells. They could be validations or profits or some other important value. The Watch Window can help you monitor multiple cells in one place.

In the Formulas tab in the Formula Auditing section is the Watch Window icon.

Clicking the icon opens the Watch Window. It will be blank.

You can click the Add Watch button to add a cell to monitor. You can chose cells from any sheet.

Click Add to watch the cell. You can widen the Watch Window and change column widths as well.

You can click the column headings to sort by the column.

You can also select a range to watch. but it will list the range as a series of cells – see images below.

When you save and close the file the watch entries are saved. When you open the file the Watch Window won’t be open, you will have to re-open it.

The shortcut Alt M W (pressed in sequence, not held down) will open the Watch Window. The same shortcut also closes the Watch Window.

 

Free Webinar Recording – Excel Format as Table Feature

Feedback score 95% based on 58 responses

In March 2020 I presented a session on Excel’s Format as Table Feature. I covered it’s advantages and how to use it to improve your Excel files.

The detailed pdf manual and example file can be downloaded using the button below. Content is listed below the video.

Download Materials

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

In this session you will learn all about Excel’s formatted tables.

Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.

Formatted tables can allow you to create powerful reports like those in a relational databases.

Topics covered

  • advantages and limitations of formatted tables
  • keyboard shortcuts
  • using formatted tables with formulas
  • solutions to some of the limitations of formatted tables
  • using range names with formatted tables
  • using formatted tables with data validations
  • creating a running total
  • using PivotTables
  • Relationships (Data tab)

As always I will share a few other tips.

Free Webinar Recording – Excel Budget Challenge Solution

Feedback score 92% based on 83 responses

In February 2020 I reviewed my solution to an Excel Budget Challenge from late 2018. This included some advanced Excel budget techniques.

The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video.

Download Materials

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

This session includes a lot of useful content and it brings together a number of different techniques. The content is worthy of a paid session but since it was a public challenge I am presenting the session for free.

The materials includes the challenge documentation, the pdf manual and the solution file. I assume you have read the documentation. You also receive the blank Excel file if you want to create your own solution.

Techniques/topics covered include

  • using INDEX-MATCH (better alternative to VLOOKUP)
  • 3-D formulas to summarise all report sheets and techniques to make using them easy
  • using a reporting template to speed up creation
  • validations
  • extracting sheet names
  • automating reports