My wife and I are expecting our first child in just 5 days now. It’s a very exciting time in our household and it has taken a few months to get everything ready for the newborn.
To ensure I have everything I need, I made a checklist (in Excel of course) and worked my way through it.
If you want to learn Excel basics, this spreadsheet features a few fantastic tools that I thought you may be interested in seeing.
Download the new baby checklist.
- Check boxes to make it easy for the user to mark of items as they go
- Conditional Formatting to cross out the items with a strikethrough when it is checked
- A Thermometer Chart to show progress through the list and how close it is to completion (See how to create a thermometer chart in Excel).
- The COUNTIF function to count the items as they are checked off
The items in the checklist have come courtesy of www.newparent.com.
The popular data entry form from Excel 2003 was removed from the Ribbon in Excel 2010. I am often asked where this feature went. So what is this feature and where can it be found? Find out in this Excel tips 2010 guide.
What is the Data Entry Form?
The Excel 2010 data entry form makes it easy to;
- Add new records to a table
- Edit and delete records from a table
- Search for records in the table
The data entry form is very user friendly and is a must for anyone who performs alot of data entry, especially in a large table.
If you wish to protect the cells on a worksheet that contain formulas you will need to lock the cells first, whilst also ensuring that the cells users should be able to change are unlocked.
It may have been a while since you worked on this worksheet and you are not sure which cells are locked and which are not. You need a fast method of locating the locked cells. Let’s use Conditional Formatting to highlight the cells that are already locked so they are easy to identify. Continue reading
A very powerful function in Excel that is unknown by many is the Cell function. Use the Cell function to find information about a cell. This can include the cell’s number format, row number or whether it is locked or protected.
There are times when this kind of functionality becomes extremely useful. Especially whilst Excel does not provide many other means of finding the number format of a cell, or whether it has been locked. Continue reading
Entering fractions into Excel can be confusing. When a fraction is entered, Excel converts the value to a date format e.g. 1/2 is stored as 01-Feb.
This is frustrating and without storing the value correctly as a fraction, any formulas dependent upon this value will not work correctly. Continue reading
I was asked yesterday how to highlight dates that were over 30 days old. The situation was that the date of a transaction is recorded and the team need to be alerted when that date is older than 30 days.
Because they wish to highlight the cell, Conditional Formatting will be used. A new rule will be created with criteria to find those dates older than 30 days. Continue reading
Due to popular demand I have created an Excel fixtures and league table generator.
Following on from my fixture list creator and league table generator files, I was asked to create a combined file to create a fixture list and also generate a league table from results entered.
Download the Excel fixtures and league table generator.
To use the file;
- Enter the teams competing and how many times they will play each other on the “Teams” sheet
- Enter the results from fixtures on the “fixtures” sheet
- View the league table on the “League table” sheet
An interesting question came up during one of my consultancy visits last week. They needed to find the top 5 values in a list based on multiple criteria. They then also needed the name of the company achieving that value. Continue reading
It is possible to perform quick calculations in Excel without having to write any formulas. This technique is brilliant and provides the formula result you need quickly and easily.
You can count, sum, average and find the maximum or minimum number in a range simply by selecting the numbers you want to calculate. Continue reading
If you collect email addresses in your spreadsheet. You may want to validate an email address in Excel.
Using Data Validation you can check the syntax of an email address to ensure it is legitimate. It cannot check the existence of an address, or who it belongs to. But it will ensure the existence of the @ symbol, a period (.) and no spaces in the address. Continue reading