If you create a spreadsheet that the user needs to fill in, you can help your users out by shading all the cells they need to complete. Once they’ve filled in the cell the shading will disappear, and it will be easy to see what still needs to be filled in. Continue reading ‘Shading Cells the User Hasn’t Completed’ »
Hiding Zeros in Excel Stacked Column Charts
If you have a stacked column chart, where some of the values are zeros, you may want to hide the data labels for those zero values. If not, the values can overlap unattractively. Continue reading ‘Hiding Zeros in Excel Stacked Column Charts’ »
Add a Macro To Set the Print Area on Multiple Sheets in Your Workbook
If you have a spreadsheet with a large number of sheets, it can be a hassle to change your print area. This macro will let you define the print area for all the selected sheets, rather than having to do it on a page by page basis. Continue reading ‘Add a Macro To Set the Print Area on Multiple Sheets in Your Workbook’ »
HLookup
Format
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
- Lookup_value The value you are looking for, usually the value the table is organized on. It might be an ID, date, or other value.
- Table_array Where you are searching.
- row_index_num The column that contains the value you want returned.
- range_lookup Determines if it only finds exact matches.
VLookup
Format
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
- Lookup_value The value you are looking for, usually the value the table is organized on. It might be an ID, date, or other value.
- Table_array Where you are searching.
- col_index_num The column that contains the value you want returned.
- range_lookup Determines if it only finds exact matches.
Find and Replace in Word: The Basics
As a tech writer, I’ve often run into a situation where I’m writing a manual for a software package that doesn’t have an official name yet. Instead, the developers are calling it SuperProduct for now. So I write the documentation, and eventually they tell me what they’re really going to call it. Thank goodness for Find and Replace. I can quickly replace all occurrences of SuperProduct with the new official name, OkieDokieProduct:
Copy A Tab from One Spreadsheet to Another (so you can edit it)
- Open the spreadsheet that has the data you want to copy or move in Excel.
-
Right-click on the tab of the data you want to copy or move and select Move or Copy….
Continue reading ‘Copy A Tab from One Spreadsheet to Another (so you can edit it)’ »
Using Lookup Functions
Lookup functions are very powerful tools in Excel. However, they are also some of the most misunderstood functions.
A big part of the problem is due to the fact that Excel actually provides users with three different lookup functions, and each one works differently. If you get them confused, you can end up with unexpected results. Or worse, a bunch of errors.
What Do Lookup Functions Do?
In general, all lookup functions do the same thing. They take a value you supply, look it up in a table and return another value based on where they find your value in the table. Continue reading ‘Using Lookup Functions’ »
Switch Between Absolute and Relative References when Entering Formulas
I find entering formulas in a spreadsheet annoying at times. First I set up the formula to reference the correct cells, then I go back and manually add any $ needed to make references absolute. However, Excel has a quick keyboard shortcut that lets you cycle through the relative and absolute references quickly for the cell reference you just entered. I just learned this trick and it’s already saved me a lot of time!
While your cell is still in edit mode, and while your cursor is in the cell reference you want to change, press F4. It will cycle through all four combinations, so just keep pressing F4 until things look the way you want. No need to grab your mouse and click, no need to type.
Here is the order Excel follows when you press F4:
- A1
- $A$1
- A$1
- $A1
Create a Drop Down List in Excel
You can easily create a drop down list in your Excel spreadsheet, to allow users to quickly select a value from a list.This makes it faster to complete entries, and makes sure users spell everything the same way.
Create Your Spreadsheet
Create your spreadsheet, and determine which cell will contain your pull down list. For this example, we’ll create a simple task list. Each task has three possible statuses, Not Started, In Progress, or Completed.
Create your list
Next, you will define the options to appear on the drop down list by entering the values you want, and giving the range a Name. You can create the list on the same worksheet in a hidden area, or on another worksheet. For this exercise, We’ll use the second worksheet.
Click on Sheet2, and enter your options.
Select your options, and select Insert|Name|Define. You see the Define Name dialog box.
Name the range (for example, TaskOptions) and click OK. You can now easily reference this list from your spreadsheet.
Create the Dropdown
Return to Sheet1. Select the cells that will contain your drop down list.
Select Data|Validation. You see the Data Validation dialog box.
From the Allow list, select List.
In the Source field, enter = followed by the name you gave your range of cells. (For example, =TaskOptions.)
Click OK.
Now, when users click on a sell in that column, users will be able to select from the options you have defined.
