I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the “table headers were actually rows and not columns?” Since I’ve already detailed how this is done in the article mentioned above, I’ll keep this short. The screen shot below is what I’ll be referencing. At the end of the post I’ll give a link to the file I used.
Conditional Drop Down List (Excel)
There are two named ranges,
that refers to the range E1:E3 and
that refers to range F1:G3.
A defined name, myItemListH, … Read the rest
I avoid the use of Volatile Functions, especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook. For very large workbooks with lots of data, it can be significant and irksome.
Worksheet cells that use Data Validation for a drop-down list can simplify the input process, or be used to limit the available choices. But the list needs be expandable. Here are two primary ways to keep your data validation list automatically updated, without having to resort to using the OFFSET function.
Update Your List Range with VBA
… Read the rest
I recently read a good blog post over at Contextures about selecting the actual used range on an Excel sheet, both manually and with VBA. However, using Excel on a Mac makes you keenly aware that there’s no Home button.
The used range on a worksheet starts with cell A1 and ends with the last used cell in the worksheet. This “last cell” is not always apparent, but easily found. Just use the keyboard shortcut CONTROL + G to bring up the Go To dialog box.
Click Special… which will bring up the Go To Special dialog box.
Select Last … Read the rest
I bought a MacBook Pro and installed Windows 7 with Boot Camp Assistant so I could access the “normal” Excel. This was important to me at the time because I was doing Excel development work in versions 2003, 2007 and 2010 on a Windows PC.
I love my MacBook Pro, which I consider my computer of the future. It’s great for dealing with Photos, Movies, Music, and all of my iOS devices.
I purchased the Mac versions of Excel (2008 and 2011) but they seem like a mixture of the three Excel versions mentioned above, and can be very … Read the rest
The other day I read an article on how to prepare my MacBook Pro for Lion, the new Mac OS X coming out this month, and discovered that any application that requires PowerPC is incompatible with Lion and will not work. See update at the end of this post.
So I followed the instructions to check all the installed programs on my MacBook Pro and was surprised to find that Microsoft Query will be incompatible. This will affect any Excel for Mac versions you might have on your computer — 2004, 2008, 2011.
I would venture a guess … Read the rest
I have no earthy idea why it took me so long to figure out how to delete the contents of a cell or range in Excel for Mac. Ever since I bought my MacBook Pro I’ve known the Delete key on a Mac isn’t really a Delete key.
I mean, since my background is with Windows, I have ingrained knowledge on how the Delete Key works on a computer. Ingrained, I tell you.
But all of that knowledge was shattered upon getting a Mac.
Where Is the Excel Delete Button on Mac
After some consternation, I learned where is the … Read the rest
I was surprised to find a List Wizard in Excel 2008 (Mac) because Excel 2007 (Windows) has Tables, and had already been out for nearly a year when Excel 2008 was released.
Here I’ll walk you through the process of setting up a List in Excel 2008 but won’t go into every detail because, as you’ll soon see, there are lots of features in the List Wizard. I’ll start with the same data table from my last post.
To create a List in Excel 2008, select your existing data table and choose Insert > List…, and the … Read the rest
Adding the Repeat command icon to the Quick Access Toolbar in Excel for Windows is a simple process that I explained in a blog post last week. Excel for Mac — 2011 or 2008 — doesn’t have a Quick Access Toolbar, but you can customize the toolbars and menus.
Add the Repeat Icon to the Standard Toolbar
- Right-click the toolbar and select Customize Toolbars and Menus…
- At the top of the dialog box select Toolbars and Menus
- Select the Show checkbox for the Worksheet Menu Bar
- In the Worksheet Menu Bar click Edit
- Select Repeat and while holding down the
… Read the rest
If you’re curious about the version of Excel you’re using and don’t know how to go about it, you’ve come to the right place. It could be as simple as selecting the Help Menu and clicking About Microsoft Office Excel, but then again, maybe not. The five options below will help you sort things out.
Excel for Windows
If you’re using a Windows computer, chances are you’re using one of the three most recent versions of Excel.
Select the Help menu, then click About Microsoft Office Excel.
This also works with earlier versions of Excel (2002 … Read the rest
One key to understanding Excel 2001 for Mac is knowing the keyboard shortcuts, but to communicate them requires displaying the symbols that are specific to a Mac keyboard. The Media Browser has a nice area for Symbols that gave me what I needed.
There are a few different ways to access the Media Browser. One way is to click the Media Browser icon on the toolbar above the ribbon. Another is to click the View menu and select Media Browser, or use the keyboard shortcut ⌃⌘M. And yes, those shortcut symbols were taken from the Media Browser.… Read the rest
Sorry if I get transfixed by shortcuts in Excel 2011, but they are a vital part of how I use Excel and so much different than Excel for Windows, so anything that helps the learning curve is worth the effort. In looking for something else, I discovered you can change the keyboard shortcuts in Excel 2011, but the benefit for me is that every shortcut is listed by menu function.
Why that’s important is that in Excel 2011 they left the old menu with the new Ribbon, and this creates a common reference between the Windows and Mac versions … Read the rest
How many files will Excel show in the Recently Used File List? Well, it depends on the version of Excel you’re using and vary between 9 and ninety-nine files.
Here is a list of Excel versions, menu navigation to the recent file list, and the max files allowed.
Excel 2010, 2007
File>Options>Advanced tab>under the Display heading:
Show this number of Recent Documents: 50 max
Recently used file list: 9 max
Excel for Mac 2008, 2011
Show this number of recent documents: 99 max
A Related Feature in Excel 2010
At the bottom … Read the rest
I’ve a simple formula for calculating the Xth Weekday of ANY month. It takes four inputs: Year, Month, Week, and Day. And requires a couple of lookup tables for data validation, one with special formatting.
It all started when my wife mentioned there were a couple of meetings she had to schedule at work, one on the second Tuesday of every month and the other on the third Tuesday of every month. That got me to thinking about how to create a formula in Excel to calculate those dates.
After getting a partial solution, other things … Read the rest
I’m a longtime user of Excel for Windows, but recently a newbie with Excel 2008 for Mac where I found that out the Windows shortcut F4 doesn’t work to switch between relative and absolute references when editing a formula. Thankfully Excel 2011 has a Switch Reference toolbar icon on the Formulas Ribbon, as shown below.
Keyboard Shortcut in Excel for Mac
In trying to find a shortcut, I opened up Excel 2008 for Mac, went to the Help search bar, typed in “Excel Keyboard Shortcuts,” and was able to find out that Cmd+T toggles the formula reference style between absolute, … Read the rest
Here’s the picture I entered for the Spreadsheet Challenge.
I’ll point out the obvious and not so obvious stuff in this picture.
- Two monitors showing Excel 2007, Excel 2003, and Excel 2010 spreadsheets, and
- PDF versions of Excel 2007 Formulas, Excel 2003 Power Programming with VBA, and Excel 2010 Bible
- A MacBook Pro showing Excel 2011 and Excel 2008 spreadsheets
- An iPad showing the Documents To Go® Premium App with a spreadsheet open
And hopefully that’s going to get me a prize. 🙂 o_O
… Read the rest