A couple of years ago, I posted an Excel Calorie Counter workbook, which has been very popular. In one of the comments, Autumn suggested that I add a recipe calculator to the file.
Separate Excel List with Automatic Lines
When you create a table in Excel, you can use the built-in styles, to add bands of colour.
The colour bands can make the table easier to read, because you can follow each line across, using the colour as a visual guide.
Separate the Days in the List
If you’re working with a list of tasks or orders, sorted by date, those coloured bands don’t help you see where each day’s data starts and ends.
To make it easy to separate the days, you can add conditional formatting to the table. We’ll add a line at the start of each date, to highlight those rows.
- Select all the cells in the body of the Excel table. In this example, cell A2 is the active cell, and A2:F9 are selected.
- On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule
- In the New Formatting Rule dialog box, click Use a Formula
- In the formula box, enter this formula, which compares the values in cells A1 and A2. There is a $ before each A, because we need an absolute reference to column A.
- =$A2 <> $A1
- Click the Format button
- In the Format dialog box, click the Border tab
- Select a border pattern and colour, to separate the dates. Unfortunately, you can’t select a thickness for the border line.
- Click the top border in the preview window, and click OK
- Click OK to close the New Formatting Rule dialog box.
There will be a border at the start of each date in the table, and you’ll see at a glance where the dates start and stop.
More Conditional Formatting Examples
You’ll find many more conditional formatting examples and tutorials on the Contextures website.
Watch the Excel Video Tutorial
To see the steps for adding lines between the dates, you can watch this short Excel video tutorial.
___________
Data Validation Documenter
If your Excel file has many cells with data validation, it can be difficult to remember what restrictions you’ve put on the worksheet cells.
These tips show you how to find the data validation cells, and create a list of what rules are on the cells.
Select All the Data Validation Cells
If you just want to see where all the data validation cells are, you can use the command on the Excel Ribbon.
- On the Excel Ribbon, click the Home tab
- In the Editing Group, click Find & Select
- Click Data Validation
This command selects all the data validation cells on the active sheet.
Select Specific Data Validation Cells
If you have different data validation rules on the active sheet, you can select only the cells that have the same validation as the active cell.
In the example shown below, cell D6 is active, and it has a data validation drop down list, based on the named range — DaysList.
To select any other cells on the active sheet with the same validation rule:
- With cell D6 active, click the Excel Ribbon’s Home tab
- In the Editing Group, click Find & Select
- Click Go To Special
- In the Go To Special dialog box, click Data Validation
- Click Same, then click OK
In the screen shot below, cell D7 was also selected, because it has the same validation rule as cell D6.
However, cell D4 was not selected, because its drop down list is not based on the same named range.
Create a List of Data Validation Cells
The Go To Special commands let you select the data validation cells, but they don’t show you the data validation rules. You would have to check each cell, or group of cells, to see its data validation rules.
If you want the details on each data validation cell for the active sheet, you can use a macro that compiles a list of details.
On the Contextures website, there is a Data Validation Documenter page. On that page, you’ll find sample code that creates a list of data validation details for the active worksheet.
In the sample file, there are two macros –
- DataValDocumenter and
- DataValDocumenterSheet
Both macros create a list of the data validation cells on the active worksheet, with the following details:
- Cell address
- Data validation type
- Data validation formula.
The DataValDocument macro creates the list in a text file, named Test.txt, in Excel’s default file location.
To see your default location, click the Excel Ribbon’s File tab, then click Options, and click the Save category. (in Excel 2003, choose Tools | Options, General tab)
In the DataValDocumenterSheet macro, a new worksheet is inserted at the front of the workbook, with a list of the data validation on the active sheet.
Download the Data Validation Documenter
You can download the data validation documenter file from the Contextures website.
To run one of the macros:
- Click the Ribbon’s View tab, then click Macros (In Excel 2003, choose Tools>Macro>Macros)
- Select DataValDocumenter or DataValDocumenterSheet
- Click the Run button
_______________________
Select Actual Used Range in Excel Sheet
It’s easy to select the current range in Excel – just press Ctrl + A. That shortcut selects all the cells in the block that surround the active cell. The selection stops at the first blank row and blank column. But how can you select actual used range in Excel sheet?
Track Time in Excel with VBA
Last week, we saw the steps for entering project start and stop times in Excel, by using keyboard shortcuts. Then, Excel formulas can calculate the total project time, based on the start and stop times.
Keep Track of Time in Excel
If you’re working on a project, you might need to keep track of time in Excel, so you can tell a client how much time you’ve spent on their project, and get paid for your work. Or, use the time data to see how much of your day is spent on productive tasks, and how much is spent Googling and tweeting.
Continue reading “Keep Track of Time in Excel”
Filter Excel Data Onto Multiple Sheets
There is a sample Excel file on my Contextures website that has a list of orders, and sales rep names.
It has a macro to filter Excel data onto multiple sheets. You can click a button, and a sheet is created for each sales rep, with that person’s orders.
Excel Formulas Not Calculating
What happens when good Excel formulas go bad? A workbook of mine that worked fine for several years, when using Excel 2003, suddenly refused to update all the formulas, after a switch to Excel 2010. Even more mysterious, the calculations worked fine on some machines, but not others. Why are Excel formulas not calculating on some computers?
Hint: It wasn’t one of the obvious solutions.
Efficient Navigation in Excel Workbooks
When I saw the signs in this shop window, I laughed, and snapped this picture.
How confused are your customers, if you need a sign that says, “This is the door”, and another sign that points to the door handle?
Design Flaws
Later, I realized that it’s not a customer problem – it’s a design problem. A substantial number of people had trouble finding the handle, which looks more like a box. And I don’t remember exactly what the front of the store looked like, but the door must have been hard to identify too.
How about your Excel workbooks – are they easy to understand and navigate? I’ll admit that some of the spreadsheets I’ve built might have similar navigation problems. I’ve added arrows that point to data entry cells, and text boxes with user instructions. Not much different from that door!
Navigating a Workbook
What can you do to make Excel navigation easier?
In a large workbook, a menu sheet, with links to other sheets, is helpful. And all the other sheets should have a link back to that menu sheet.
On a large worksheet, you can add a menu at the top of the sheet, linking to the sections below.
If users can enter data on some sheets, group all the data entry cells in one area, and use colour coding, to make the cells easy to identify.
Worksheet Design Resources
Where do you get your worksheet design ideas? From your own experiments? From sample workbooks that you’ve found online? Somewhere else?
Even though it’s focused on web site design, rather than spreadsheets, I found plenty to think about in Don’t Make Me Think, by Steve Krug.
There are good tips in Professional Excel Development, by Rob Bovey, et al.
What books or other resources would you recommend?
________
Show Specific Info in Tabbed Excel UserForm
To show instructions to users in your Excel files, you can addworksheet comments or text boxes with notes.
Excel UserForm With Help Info
Another option is to put the notes in an Excel Userform, and add a Help button on each worksheet.
The button can open the Help form to a specific page, and show the relevant Help information.
Worksheet Help Buttons
This screen shot shows the UserForm Help page that open when you click a Help button on a worksheet.
In this example, the PivotSales sheet is active in the workbook, and its Help button opens the Pivot page in the UserForm’s MultiPage control.
Download the Sample File
You can download the sample file, and see the written steps, on the UserForm MultiPage Help page, on my Contextures website.
Watch the Video
To see the steps for creating the UserForm and Help buttons, you can watch this Excel video tutorial.
__________