Dashboard style report - How to create a list?

Cell I8 on the Dashboard style report, is a list of all the months, years or date range. To create this list first define all the content that you want within the drop down in a selection of cells. Once you have done this select the cell that you to have the dropdown be contained in and then set the list by…

1. Go to the "Data" tab, and select "Data validation"…

2. Then a pop should appear, then select "List" from the dropdown.

3. Then select the source field, this should then close up the box then select the data that you would like to appear within the list.

4. Once you have done this it should reopen the screen, and look a little like the below.

5. If you would like to add a note to the cell, select the "Input message" tab, add a heading, and content then select ok

6. And you have now created a nice simple list. Done!!

To err is human...

Well it is always going to be our fault so we have to fix it!

So if you find that you have errors within you’re excel sheet what should you do…

The errors that you may see are:

1.  ##### – this is simply, your column is not wide enough!

2.  #DIV/0! – this is where you are dividing by 0. So you can use a simple iserror Excel 03 or lower or iferror if you have Excel 07 or you can use a selection of if statements to check whether the field =0 or “”.

3.  #NAME? – this is where Excel does not recognise the name of the formula so you need to double check your spelling or try again!

4.  #NUM! – this is most likely going to be that you are trying to do a formula against a piece of text, you may have a rouge character that you have written within the field remove this and use the number formatting to set how you want to display it as.

5.  #REF! – the cell that you were calculating does not exist, you may have deleted this row/column that it existed with.

6.  #VALUE! – this could be because the formula contains text within instead of a number value, or one of the cells contains text. You can use the same rules used for #DIV/0! to solve this problem.

If you have a  problem then drop me an email at emails-logical-test@gmail.com or leave a comment!

Cheers
Logical Test

Would you like a date?

If you use dynamic dates and would like for the dates to not show excessive characters then try the below formula, just replace [start date], and [end date] with the fields that contain the start and end date…..
 
=IF(AND(DAY([Start date])=DAY([End date]),MONTH([Start date])=MONTH([End date]),YEAR([Start date])=YEAR([End date])),TEXT([Start date],"D Mmmm YYYY"),IF(AND(MONTH([Start date])=MONTH([End date]),YEAR([Start date])=YEAR([End date])),TEXT([Start date],"D")&" - "&TEXT([End date],"D Mmmm YYYY"),IF(AND(YEAR([Start date])=YEAR([End date])),TEXT([Start date],"D Mmmm")&" - "&TEXT([End date],"D Mmmm YYYY"),TEXT([Start date],"D Mmmm YYYY")&" - "&TEXT([End date],"D Mmmm YYYY"))))

Examples and what will happen…

1.  5 March 2010 – 5 March 2010
•  Will equal…. “5 March 2010”
2.  5 March 2010 – 6 March 2010
• Will equal…. “5 – 6 March 2010”
3.  5 March 2010 – 6 April 2010
•  Will equal… “5 March – 6 April 2010”
4.  5 March 2010 – 6 April 2011
•  Will equal… “5 March 2010 – 6 April 2011” (What were you expecting the Easter bunny!! :-) )

If you know a better way why not let me know and we can create useful code for all.

Funnel visualisation report

Hi all,

As promised but a little late, a new report.

This is my version of the Google Analytics funnel visualization report, this is great for defining a funnel and then looking for those weak pages.

As is always the case, if you have any questions or queries then leave a comment and I can get back to you.

This report is currently only available from
http://www.scribd.com/doc/27322109/Funnel-Visualisation-v1-for-Excel-03

but I hope to have a more versions available soon.

Cheers
Logical Test

A dashboard style report for Excel 2003

Hi all,

Just a quick not to let you know that this report is also available for Office 2003, from

http://www.scribd.com/doc/26686010/A-dashboard-style-report-version-2-for-Excel-2003

Also if you have any ways on improving these report then let me know.

Cheers
Logical Test

Just checking in....

Ok so its been about a week since I uploaded the second version of the Dashboard style report, there are not a huge amount of changes from the first, however in my opinion I feel like I have cleaned up the whole report.

The main changes are that the charts are little bit more dynamic for the user, allowing for different data to be displayed and all the data has been moved into its own spreadsheet.

I’m thinking that it would be quite cool for the user was if the four charts were completely in their control they select the type of information they want and the data that they want to display, but I’ll look at into that later, unless of course people really want it, but you have to let me know.

I’m not sure what sort of reports you would all like but hopefully should be uploading my second, this is loosely based on the funnel visualisation available in Google Analytics (free website analytical tool). This will mainly show the visual representation of what you can create in excel and it uses some of same logic available in the Dashboard style reports.

Hopefully I should have this available by the end of the week, so watch this space.

Oh yeah before I forget to ask….

I want you all to able to download these report for free from the best places, so if you have a favourite place that you like to download reports from then let me know and maybe I will be able to add it there.

Cheers
Logical Test

A dashboard style report version 2

Hi all,

Well I've been tinkering with the original report, and its available from:

A dashboard style report v2
http://office.microsoft.com/en-us/templates/TC300089241033.aspx

only made a few tweaks, I've made all the charts a bit more flexible and cleaned up where all the data is stored.

If you want any help then just drop a comment or email me at email.logical.test@gmail.com

Cheers
Logical Test


If you have found this useful why not donate via pay pal