# Avoid the #DIV/0! Error in Excel

### DIY Function

If you have the subscription version of Excel you can create your own functions. One that you may want to create avoids the #DIV/0! error.

# Free Webinar Recording – Excel Formatting Tips

### Feedback score 94% based on 70 responses

In October 2019 I re-ran my Formatting Tips session. The detailed pdf manual and example file can downloaded by using the button below. Content listed below the video.

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 covers:

• a format to avoid and the one to use in its place
• keyboard and mouse shortcuts
• how to use and create customer number formats
• about Styles and how they can make your formatting more consistent
• that colours can be used to filter, sort and even find things in your sheets
• how to stop zeroes displaying plus other general formatting tips
• a quick demo of Flash Fill

# Custom List Trick

### Create looping sequential codes

It is common to use Q1 for quarter one. Excel will even cycle through Q1,Q2,Q3 and Q4 when you drag a cell contain Q1. What if you want to use the sequence M1 to M12 for months?  Custom Lists to the rescue!

# TRANSPOSE Function in Excel

### Plus a trick to avoid zeroes displaying

The TRANSPOSE function is one of only a few functions that must be entered as an the array using keyboard entry Ctrl + Shift + Enter (CSE). It allows you to switch a range from going across the sheet, to go down the sheet and vice versa.

# Excel Data and Formula Entry Tips

### Speeding up data entry

There are number of shortcuts you can use to speed up your data and formula entry in Excel.

Use the number keypad on the right of the keyboard. This has all the numbers, as well as most of the formula operators (+  * –  /), you need to create formulas. It also has a large Enter key. The numbers are laid out like a calculator and so are easy to use.

# Handling Text in Calculation Cells in Excel

If you need to do mathematical calculations on cells that may contain numbers or text, then you can use the IFERROR function to ignore the text.