Tag Archives: Excel 2008

Horizontal Dynamic Dependent Drop Down List Example

A Dynamic Dependent Drop Down List with a Horizontal Table Reference

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)

Horizontal Dynamic Dependent Drop Down List Example

There are two named ranges,

    1. 1)

myCategoryH

    1. that refers to the range E1:E3 and

 

    1. 2)

myTableH

    that refers to range F1:G3.

A defined name, myItemListH, … Read the rest

How to Update a List or Range without OFFSET

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

Select Excel’s Used Range on a Mac

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.

Go To dialog box
Click Special… which will bring up the Go To Special dialog box.
Go To Special dialog box
Select Last … Read the rest

Excel 2010 icon

An Excel Crossroads – Mac and Windows

Excel 2010 iconI 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.

MacBook ProI 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

Microsoft Query Incompatible with Mac OS X Lion [Updated]

Lion picThe 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.

Microsoft Excel for Mac and PowerPC

I would venture a guess … Read the rest

That Damn Delete Key in Excel for Mac

Where is the delete button on Mac - ExcelI 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

Create a List in Excel 2008

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

The Repeat Command in Excel for Mac

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…

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

Customize Toolbars and Menus

  • Select Repeat and while holding down the
… Read the rest

What Version of Excel am I Using?

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.

Excel 2003

Select the Help menu, then click About Microsoft Office Excel.

About Excel Versions 2003

This also works with earlier versions of Excel (2002 … Read the rest

Apple Keyboard Symbols in Excel

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.

Media Browser IconThere 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

Keyboard Shortcuts in Excel 2011

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

Recently Used File List in Excel

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

Recent Document List Setting

Excel 2003
Tools>Options>General tab
Recently used file list: 9 max

Recent Document List Setting Excel 2003

Excel for Mac 2008, 2011
Excel>Preferences>General
Show this number of recent documents: 99 max

Recent Document List Setting Excel for Mac

A Related Feature in Excel 2010

At the bottom … Read the rest

Calculate the Xth Weekday of Any Month in Excel

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.

Xth Weekday of a Month

The History

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

Switch Between Relative and Absolute References in Excel for Mac

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.

Switch Reference

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

My Spreadsheet Challenge Picture

Here’s the picture I entered for the Spreadsheet Challenge.

ESP Spreadsheet Challenge Picture

I’ll point out the obvious and not so obvious stuff in this picture.

  • Me
  • 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

Related Posts Plugin for WordPress, Blogger...… Read the rest