Create a battery chart in Excel just like you see on your smartphone, tablet device or even PC. It is a great visual way of viewing the percentage remaining of a value.
In Excel a battery chart could be used to view the amount remaining of a budget, the work remaining on a task, or number of people still to attend training.
There are many reasons why you may want to visualise an amount dropping until it is empty, or complete. A battery chart provides a method that people can relate to easily.
The image below shows a battery chart being used to show the amount outstanding on a training budget.
Drop down lists can be created in Excel to assist with data entry and to prevent potential typos and misspellings. The Data Validation tool in Excel can be used to easily create lists.
However if you have many entries, you may need to break the list into multiple dependent drop down lists. By creating a cascading set of lists, the entry in one is dependent upon the selection made in another.
In Excel 2010 a feature was introduced to easily rank the fields of your PivotTables.
Yes, you can sort the fields of a PivotTable to view items in order from largest to smallest, or smallest to largest depending on what you are trying to achieve. But you may wish to keep your list of products, customers, salespersons or whatever the field is your are ranking in alphabetical order.
Take the table below for instance. This PivotTable displays the number of sales and conversion rate for the members of a sales team.
We would like to add a rank field to the PivotTable for both fields.
This week I am pleased to announce the release of a new online course on How to Create Sports League Tables and Tournaments in Excel.
I have blogged and created videos in the past demonstrating spreadsheets that calculate team rankings and generate cup draws etc. I get many, many messages about these posts so have been planning for a while to put this course together.
The course is now open for enrolments. In the course you will learn:
- How to automatically calculate league table rankings for players or teams as results are entered.
- How to automatically create a random cup draw for knockout tournaments.
- Different formulas to calculate various sports statistics and to determine match results.
- How to calculate different cricket statistics such as net run rate, bowling strike rate, number of balls bowled and more.
You can join this course now using the link below for just $19.
Many Excel users are familiar with lookup functions such as VLOOKUP, INDEX and MATCH to look up information in a list. But how about performing a picture lookup to return a picture dependent upon the contents of a cell.
This requires a little extra thought as a standard VLOOKUP is not capable of returning a picture from a list.
In this blog post we will explore how to create a picture lookup. We will look at how to return the picture of a flag dependent upon the country name that is selected from a list.
This blog post looks at how you can create a looping PowerPoint introduction. This is when a few select slides are set to loop continuously before the main presentation begins.
It provides something for the audience to watch whilst they are gathering in the room. It can be a useful technique for creating a buzz, and setting the expectations for what is to follow.
To do this we will need to create two custom shows. One for the looping introduction, and another containing the main body of the presentation. We will then need to link the two shows so that the presenter can easily exit the loop and begin the presentation when needed. Continue reading
Microsoft Project has many settings that control the behaviour of the application, and of your projects.
These settings can be accessed by clicking the File tab on the Ribbon and then Options. There are many useful options in here, but this blog post focuses on 5 MS Project settings you need to know.
Calculate Multiple Critical Paths in a Project
Although a project can only have one true critical path (the tasks that directly affect the finish date of the project). You may wish to view the critical path for each independent series of tasks.
This can be done to view the critical path for each subproject for a large master project, or maybe you have different independent series of tasks that are performed by different departments or regions in the project.
In Microsoft Project you can display different project information next to the task bars in a Gantt Chart. This ensures that you can see the information you need to see about a task without having to keep looking over to the table.
You can display the information in different positions in relation to the bars such as to the left, right, above, below or in the middle. However, Project only allows one piece of information per position.
This can limit the feature somewhat so we will look at how to add multiple fields next to the task bars in a Gantt Chart.
When a range of cells are selected, Excel automatically displays formula results on the status bar. This can be an incredibly useful feature of Excel, especially when combined with filtering data.
Excel can display the sum, average, max, min, count of cells containing values and count of cells that are not blank in the status bar.
This blog post looks at how you can get Excel to display your own function on the status bar. For example, you may need to know the median or mode value, or count the number of blanks in the selected range.
In this post we will get Excel to display the number of blank cells in the selected range on the status bar.
For Microsoft Project to accurately calculate your project schedule, you will need to specify the working days for the project.
Part of this will include adding recurring holidays and any ad-hoc non-working days to the calendar. These ad-hoc nonworking days could for instance be caused by bad weather, a national celebration day, or another project taking priority.
Specify the Working Days for the Project
The default base calendar of MS Project, the Standard calendar, assumes a Monday to Friday working week. However your project working days may differ from this.