Hello Excellers, time for some more formula fun, or some fast formula writing fun. Let's look at using one of Excel's features - Auto Correct and put it to another use to speed up Formula Writing. With the Auto Correct feature in Excel, we can specify our own Auto Corrects, essentially we can use shortcuts of text to get Excel to automaticially fill in the beginning of the formula we are wanting to type. For example, let's say we want to type VL and get Excel to auto correct us and type … [Read more...]

## Formula Friday – Calculating The Percent Of Year Completed And Remaining Using The YEARFRAC Function.

Hello Excellers, time for another Formula Friday. Today let's look at calculating the percentage of the year that is completed and that is remaining really easily using the YEARFRAC function in Excel. This figure can be used as a standalone number or calculation for the audience of your report, or used as part of another calculation in your report. Syntax of YEARFRAC YEARFRAC(start_date, end_date, [basis]) Where Start_date this is required. A start date. End_date this is … [Read more...]

## Macro Mondays – Write A Macro To Set All Pivot Table Data Items To Sum

Hello, welcome to Macro Monday, and it is time for some more Macro fun. Have you noticed that when you create a Pivot Table, Excel by default will either SUM or Count your items. To read more details about why this occurs you can check out my blog post here. But, today I want to show you how to set all of your Pivot Table data items to SUM. If Excel decides to COUNT your items for example if you data contains text or blanks, if your data set is obviously numeric then SUM will be used. If … [Read more...]

## Formula Friday – Why is my formula showing in a cell and not the result??

You may have already come across this problem, and if you have not, you probably will at some point when using Excel. The mysterious case of your formula is showing in the cell not the result of the formula looking like this. Whilst it is really frustrating there are a number of reasons this can happen, and usually I just work my my way through them to resolve the problem. 1. You have show Formulas selected. You have have in error or accidentally hit CTRL+’ (the back quote symbol), … [Read more...]

## Formula Friday – How To Use RANK In Excel But Ignore Zeros

Happy Friday Excellers. Today I want to share with you how to RANK a data set, but ignore zero values. I use RANK a LOT, and sometimes you just do not want to include zero values in your results. If you want to download the Formula Friday examples you can so here- Ranking Ignoring Zeros We actually are going to make use of the IF function as well as RANK in this solution. Here is sample data set I want to rank. I have 10 lines of data and 2 of them are zeros which I do not … [Read more...]

- 1
- 2
- 3
- …
- 11
- Next Page »