Blog & Excel Tips

A common problem is entering long numbers in Excel. You will know you have this problem if Excel - Replaces some of the last characters of the long number with zero's - Seems to round long numbers to zero- Refuses to accept the number you type- you enter numbers that have 15 or more characters (think credit card numbers, ID etc)

You've built your pivots, they work great so you create a few more so that your dashboard can show the information in different groupings. Perhaps a daily pivot chart to show the trend and then the same data shown monthly for reporting purposes. The problem is that when you change the one pivot's grouping, to say monthly, all the other pivots also change to monthly.

A common requirement in spreadsheet work is to create a drop down list in Excel and based on the chosen item, lookup another item. The way to do this would involve a tool and a formula. The tool will be the Data Validation tool and the formula that will help is the VLOOKUP formula

Creating a listing of dates is a common Excel requirement. You want to enter the first date and then drag down so that it fills in the next dates. But what if you want it to only fill a date series in Excel which contains workdays only?

When using a Excel you may need to/ want to repeat pivot table row labels. Some of the reasons you may want to do this are that you plan to copy and paste the pivot table into another sheet to sort or filter and you need the blanks filled in (see here how to manually do this quickly), or the sheet is very long and as you scroll down you can't remember what section you are looking at

How to (easily) count email addresses from the same domain in Excel using a COUNTIFS trick to count items CONTAINED in a cell

Have you ever needed to move AND insert an Excel column quickly. If you are trying to move it to a place that already has information you first need to insert a column otherwise it wants to overwrite the information. The place it moved from will also have a blank column which you may want to delete.

One of the most common practices in Excel is to change (manually) the colour of a cell based on what is in the cell. So if you are doing school reports you want the marks less than 50% to be red, higher than 80% to be green and perhaps a variety of colours in between. The manual process is tedious (and prone to error). But Excel can automatically change the cell color based on the value of the cell.