Category Archives: Beginner

Beginner level information

Excel 2011 Preferences

In my opinion, there are some default Preferences that should be changed in Excel 2011 [Mac] after taking it out of the box, like changing worksheets to the Normal view. I’ll share specific picks for General, View, Edit, Calculation, and Ribbon preferences.

Excel Preferences

General Preferences

Uncheck Open Excel Workbook Gallery when application opens. It’s rather annoying.

Change the number of recent documents shown to the maximum amount: 99. However, you may feel a lesser amount is adequate for your needs. I don’t recall the original default, but more is better. Think “memory aide”.

I set the preferred file location to my most frequently used folder, which saves me a lot of time.

Excel General Preferences

View Preferences

I set the Preferred view for new sheets to Normal just as fast as I figured out this preference existed. I guess the Mac programmers wanted a different look from the Windows version of Excel. Bad move. I mean Excel is Excel, right?

Excel View Preferences

Edit Preferences

Check Automatically convert date systems. You don’t want to have to think about Date systems. (Excel for Windows has no such setting.)

Excel Edit Preferences

Calculation Preferences

Kindly un-check Use the 1904 date system unless your a hardcore Mac user who loathes everything Windows. The entire Excel world practically runs on the 1900 date system. Use it.

Excel Calculation Preferences

Ribbon Preferences

The Ribbon takes up too much screen real estate and with the Mac version of Excel there’s also Menu bar. Uncheck the Expand ribbon when workbook opens to give you more room.

One-click any Ribbon tab to temporarily expand the Ribbon for access to commands. Double-click to keep it expanded.

You may have noticed I also checked the box to show the Developer tab on the Ribbon. Not everyone will need this, but if you think you might, turn in on while you’re thinking about it. It won’t hurt anything and you may learn something new.

Excel Ribbon Preferences

Do you agree with my choices?

Copy a Help Topic URL in Excel 2010

Excel 2010 Help is normally connected to Office.com so each Help topic has a specific web address (URL) that can be easily copied. This is only for Windows computers running the latest Excel version 2010.

You can check the connection status to Office.com by hitting the F1 key to bring up the Help screen, then look in the bottom right corner of the page to see the connection status. There will be one of two options showing.

  • Connected to Office.com
  • Offline

Help Connection Status Windows

Get a Help Topic URL

Make sure you’re connected to Office.com then use the keyboard shortcut F1 to bring up the Help window. In the search box type in something like Excel keyboard shortcuts and press the enter button.

Help Windows Screen

You’ll get search results (shown below) and to complete this example click the link for Keyboard shortcuts in Excel 2010.

Help Search Results

The topic will appear in the Help window (shown below). Right click on the topic title (highlighted in red) then select Properties from the pop-up box.

Help Topic Title Keyboard Shortcuts

In the Properties dialog box, triple click on the Address (URL) to select it, then use the keyboard shortcut Ctrl+C to copy the web address (URL) to the clipboard.

Help Topic Properties URL

Another method is to click the Address (URL) and using the keyboard shortcut Crtl+A to select the entire URL, then Ctrl+C to copy it to the clipboard.

You can use a URL copied to the clipboard in several ways.

  • Email it to someone
  • Use it as a hyperlink in Excel
  • Paste it into your browser to see the topic in a browser window
  • Use it in a web page as a link

Bonus Tip – Insert a Help Screenshot

Since Excel Help pops up in a separate window, it can be inserted into the worksheet as a picture. I find this to be useful at times to keep a bit of Help knowledge in the worksheet.

Here’s a couple of tips I use. First, size the Help window appropriately before inserting into the worksheet, you only get what’s showing in the window so make sure the text you want is visible. Secondly, after inserting the picture, you can use the Picture toolbar to Crop, Resize or modify the picture as you see fit.

To Insert a Help window screenshot:

  • Bring up a Help topic
  • Size the window
  • Make sure the text you want is showing in the window
  • Choose Insert > Illustrations > Screenshot
  • In the Available Windows drop-down click the Help window

Help Topic Screenshot Insert

I cropped the picture above with the Picture tools and could have done some fancy stuff, but that will have to wait for another day.

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 and 2000).

Excel 2007

Select the Office button and click the Excel Options button, then select Resources from the left-hand pane and click the About button.

About Excel Versions 2007

Excel 2010

Click the File tab on the Ribbon, then select Help from the left-hand pane. The right-hand pane has the Version information.

About Excel Versions 2010

Excel for Mac

If you’re using a Mac the two most recent Excel versions, 2008 and 2011, just select the Excel menu, then click About Excel.

Excel 2008

About Excel Versions 2008

Excel 2011

About Excel Versions 2011

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.

Symbols can be filtered by category, as shown in the picture below. The Apple symbol  is located in the Special category and the Keyboard category holds the symbols that are distinct to the Mac.

Apple Keyboard Symbols 1

Insert these symbols into cells and you get the following depiction when viewed on a Mac.

Apple Keyboard Symbols 2

When this same spreadsheet is viewed on a Windows machine, the following characters will show.

Windows version of Apple Keyboard  Symbols

Where you see they got it mostly right, with the exception of the Apple symbol. (Now why did I have a feeling this was going to be the case?)

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 of Excel. To see shortcuts in Excel 2011 go to the Tools menu and select Customize Keyboard. (Works in Excel 2008 too.)

Create Keyboard Shortcuts Undo

You’ll notice the Categories correspond to the Menu, and the right-hand pane shows the items within the selected category. In the picture above I’ve selected the Edit menu and Undo is selected in the right-pane. The Current keys assigned to the Undo action are shown as Control+Z, F1, Command+Z. (Now I know why F1 doesn’t work for bringing up the Help system.)

Checking for a Redo shortcut (in the picture below) shows none listed. I added a shortcut key for Redo by clicking in the box Press new shortcut key, which activated the Add button, then pressed the keyboard combination Control+Y,  then clicked Add. Underneath the shortcut key box you see some text that shows the Currently assigned key.

Create Keyboard Shortcuts Redo

I tried to add another shortcut key, Command+Y, but the currently assigned key was Repeat. So I didn’t add that shortcut. But that left me thinking, “Is Repeat and Redo the same?” (Something for another day.)

Anyway, you can peruse the different menu items and look for shortcuts, or the lack of, in this Customize Keyboard dialog box. I learned that to increase the font size one size larger, the shortcut Command+Shift+. will do the trick and Command+Shift+, will take the font size down one level. Kind of handy when adjusting headings for a data table.

To use a keyboard shortcut that’s the same as a default Mac OS X keyboard shortcut, you must first turn off that Mac OS X keyboard shortcut.

  • On the Apple menu, select System Preferences.
  • Under Hardware, click Keyboard, then click the Keyboard Shortcuts tab.
  • Find the keyboard shortcut  you want to turn off, then clear the check box.

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 of the Recent Workbooks list, Excel 2010 has an option you can check to:
Quickly access this number of Recent Workbooks: x

Checking this box adds recent files to the left pane so you can always see the file names. They’re not really the most recent files because they include any files you have pinned to the top of the list, even if they haven’t been opened in a while.

(I covered this feature in a microblog article on Tumblr and in a recent post with video here on this blog.)

Use Function Keys Normally in Excel 2011

Since I’m relatively new to the world of Mac but an old hand with Windows, making an effort to discover Excel 2011 for Mac comes with some adjustments. Using the Function keys is one such irritation adjustment.

You see, using Excel on a Windows machine allows you to use the function keys, like F1, F2, F4, F5, and Alt+F11, which become second nature. But with a Mac you can’t just press, for example, the F5 function key all by its lonesome self. You’re forced to use the keyboard combination fn+F5.

You can change the Mac system preference to use all Function keys without having to press the fn key. Just go to System Preferences > Keyboard > select the Keyboard tab, and check the box to: Use all F1, F2, etc. keys as standard function keys.

Mac Function Key System Preference

Of course, all the function keys mentioned above don’t work with Excel 2011, with the exception of F5, which opens the Go To dialog box, and Att+F11 that opens the VBA Editor (even though the documentation doesn’t mention it).

And speaking of documentation, if you want a reference list of Function keys for Excel 2011 for Mac you can always look in the Excel Help system documentation, or just go to this link on the web and click on Function Keys. The list you see is considerably different than Excel for Windows, where you can view here.

Logical Operations in Excel

Every once in a while you get fixated on something and the only way to let it go is to share it with others. So …. while reviewing the logical IF function and nested IF statements, I came up with a chart or table, that sort of evolved out of curiosity.

It started by looking at two conditions, both having true or false possibilities, as shown in the table below, where A and B have four possible outcomes.

Logical Operations Four Outcomes

If you type the lowercase true or false into a cell they will change to uppercase TRUE or FALSE. That’s because Excel interprets them as logical functions.

You can also type =TRUE() or =FALSE() to get the same thing. Both functions have no arguments, yet return the logical values TRUE and FALSE, respectively.

TRUE FALSE cell alignmentAlso take notice that text values align to the left side of a cell, numbers to the right, but logical values align to the middle.

Logical Operations Add MultiplyTRUE and FALSE both have numerical values that you can see when mathematical operations are performed on them. In the table to the right, multiplying A times B gives values of zero (0) or one (1), but adding A and B gives values of zero (0), one (1), or two (2). Which proves that TRUE = 1 and FALSE = 0.

Logical Operations Add Multiply And Or

When the outcomes for A and B are used as arguments for the AND and OR functions, it gives you a sense for the rules of how they operate.

The AND function will return TRUE if all of its logical arguments evaluate to TRUE. In this example, observation shows this to be the same as multiplying A and B together, given the numerical values of TRUE (1) and FALSE (0).

The OR function returns TRUE if any argument is TRUE, and returns FALSE if all arguments are FALSE.

Another logical function is NOT, which simply reverses the value of its logical TRUE or FALSE argument. Using AND inside the NOT function gives us the reverse of the AND function and is know as NAND. (see chart below)

Using the OR function inside the NOT function is called NOR and gives us the reverse of the OR function, as shown.

Logical Operations

Finally, using an equals (=) comparison between A and B is done to come up with a completely different set of TRUE and FALSE values for these four outcomes. It shows if A and B are the SAME.

Using the Equal comparison inside the NOT function gives us the reverse values, or not the same.

You can now put this completely out of your head and get on with your life.

Worksheet Navigation in Excel

The other day I found myself switching frequently between three worksheets in an Excel workbook. Having to grab the mouse, select another worksheet tab, then switch my hand back to the keyboard became annoying, so I looked up the keyboard shortcuts.

Excel for Windows:

Ctrl+PgDn — takes you to the next worksheet
Ctrl+PgUp — takes you to the previous worksheet

Excel for Mac:

OPTION + RIGHT ARROW — takes you to the next worksheet
OPTION + LEFT ARROW — takes you to the previous worksheet

Navigation Shortcut with the Mouse

The worksheet tab controls come in handy with too many worksheets in the workbook.

Worksheet Tab Controls

Right-click on these controls and a pop-up list appears, then click on a sheet name to activate it.

Right-Click Sheet List

For more than 15 worksheets, at the bottom of the list you’ll see an entry for More Sheets…. Click to bring up an Activate dialog box that shows all the worksheets by utilizing a scroll bar.

Right-Click Sheet List More Sheets

I wish Microsoft would allow the pop-up list to be more than 15 sheets.

Quick Access File List in Excel 2010

I just found a new feature in Excel 2010 that will save you some time. There’s an optional feature at the bottom of the File>Recent menu to Quickly access this number of Recent Workbooks.

When you already have a file open in Excel, clicking the File menu will default to the Info tab so you have to click the Recent tab to see the Recent Workbook files. Checking the option mentioned above throws the Recent Workbook files (you choose how many) into the left-pane of the File menu, so you can see the files listed when clicking the File menu.

My short video shows how this new feature works in Excel 2010.

Indent Left and Right to Align Cell Contents

Formatting is second nature with Excel because ergonomically you want people to notice the information in your data and not spend time finding it, either because it doesn’t stand out, or it blurs together. Recently I came across a new wrinkle with the indentation of cell contents and thought to share it with you here.

Alignment of Cell Contents

Numbers are aligned to the right side of a cell and text is aligned to the left side. In the picture below this works to blur the Pub Date and Link data together, making it difficult to read. Not good.

A simple fix is to select the data in the Link column and click the Indent button on the Home tab of the Ribbon.

Indent Button on Home tab

Left Indent

One click of the Indent Button inserts a Left Indent, with an Indent value of one (1), as you can see in the Format Cells dialog box below.

Format Cells Horizontal Alignment Left Indent

This works well in making text more readable when next to a column of numbers.

Right Indent

There’s also a Right Indent, which works well with moving numbers away from the right side of the cell. This feature is utilized in the Format Cells dialog box. Note: only for Windows versions of Excel.

Just select the range of numbers you want to format, use the keyboard shortcut Ctrl+1 to bring up the Format Cells dialog box, select the Alignment tab, and under Horizontal click the drop-down arrow, select Right (Indent), then change the Indent value to one (1).

Format Cells Horizontal Alignment Right Indent

As you can see in the picture below, the Pub Date data has a Right Indent and the Link data has a Left Indent.

Formatted Numbers and Text

More Indenting

As you may have noticed in the screen-shot above, there’s a Distributed (Indent) option for Horizontal alignment. I didn’t test it, but wanted to point it out nonetheless.

If you’ve experience with this feature leave a comment and let us know how it works.

Note: Excel for Mac only has Left Indenting.

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, relative, and mixed.

So I go back to Excel 2011 for Mac and, lo and behold, ⌘+T works there as well.

Gotta love that keyboard shortcut list in Help. It’s my new best friend in Excel for Mac. 🙂

Convert Seconds with the TIME Function in Excel

I often download an iTunes playlist just to see the time increments for each song, but iTunes only gives you the number of seconds for each song duration. In a previous post I spent a great deal of time showing how to use a combination of various formulas to convert these seconds, when it’s really very simple.

Excel Formula to Convert Seconds to Minutes

You see, the Excel formula to convert seconds to minutes can be used easily with the TIME Function. All you have to do is format a worksheet cell with the proper time format.

Excel Formula to Convert Seconds to Minutes

The formula for Min:Sec in cell F2 is shown in this Function Arguments dialog box.

Function Arguments Dialog Box Seconds Conversion

The TIME Function has three arguments, but in this formula the first two (Hour, Minute) are not needed. Excel takes the last argument for Second and converts to a time serial number. You can see the formula result is 03:57 because cell F2 is using a custom time format.

Format Cells Custom Time Format

To format a range with a custom time format that shows minutes and seconds, use the keyboard shortcut Ctrl+1 to bring up the Format Cells dialog box, select the Number tab, and under Category select Custom, then enter mm:ss in the Type text box and click OK.

Bonus Formula and Formatting

The Cum Time column has a custom time format of [h]:mm:ss and the formula in cell G2 is:

=SUM($F$2:F2)

which is copied down to give the cumulative time for all song durations. Adding the [H] allows the Hour to show when the cumulative time adds up to over an hour.

The IF Function in Excel

The IF Function is used to evaluate a logical test, then if TRUE do something or if FALSE do something else. A familiar construct. The IF Function has three arguments:

IF(logical_test, [value_if_true], [value_if_false])

Argument 1

The first argument, logical_test, is required. It’s any value or expression that can be evaluated to TRUE or FALSE. This argument can use any comparison calculation operator. There are four types:

  • Arithmetic operators ( +   –   *   /   %   ^ )
  • Comparison operators ( =   >   <   >=   <=   >< )
  • Text concatenation operator ( & )
  • Reference operators (colon, comma, space )

Argument 2

The second argument, value_if_true, is optional. This value is returned if the logical_test argument evaluates to TRUE.

I didn’t know this second argument was OPTIONAL, but nevertheless you can omit this argument by placing a comma after the first argument, followed by nothing. An example would be =IF(A1= 5,). But here’s the weird part, if the second argument is omitted and TRUE, the function returns a zero (0), and if the second argument is omitted and FALSE the function returns FALSE.

When TRUE = 1 and FALSE = 0 then everything is right with the Excel world. When TRUE = 0 then things are messed up. I think they messed up here. Although how many people omit the second argument in an IF statement? But I digress.

Argument 3

The third argument, value_if_false, is optional. This value is returned if the logical_test argument evaluates to FALSE.

Two Common Uses for the IF Function

Division by zero is undefined mathematically so Excel provides a glaring #DIV/0! when that happens. And sometimes we don’t want to show the result of a formula unless there’s some actual data to calculate.

Avoid Division by Zero

The intent for column D is to divide Num1 by Num 2, but avoid division by zero. The IF function is looking to see if Num 2 is zero (0) and if so, then zero(0)  is returned, otherwise the calculation is done (Num 1 / Num 2). The formula in cell D2 is =IF(C2=0,0,B2/C2).

IF Function Divison by Zero

Knowing what I do now about omitting the second argument, this formula could be re-written as =IF(C2=0,,B2/C2) with the same result. However, not a very good practice.

More commonly this formula is written differently as =IF(C2<>0,B2/C2,0) with the same result; doing the calculation if Num 2 is not zero (0) and returning a zero (0) when it is.

Calculate or Leave Blank

Many times formulas extend beyond currently entered data. This is where the IF function can be useful. The formula in cell D2 is =IF(C2<>””,B2+C2,””), which adds Num 1 and Num 2 if Num2 is not blank, and returns a blank (“”) otherwise.

IF Function No Blanks

This formula could also be written =IF(C2=””,””,B2+C2) which reads like this: if C2 is blank, then return a blank, otherwise add B2 and C2. Both of these formulas give the same result.

Absolute and Relative Cell References in Excel

I normally don’t think absolute and relative cell references are difficult, until I try and mix them in one formula with two cell references. Come along and see what I mean.

Relative Cell Reference

Relative Rows FormulaThe first example is a worksheet with data for Quantity, Price, and Total. The formula in cell C2 is Price times Quantity, or =B2*A2.

There are two cell references in this formula, B2 and A2. Both have a column reference (B and A) and a row reference (2).

Relative RowsYou’ll notice that B2 changes to B3, B4, B5, and A2 changes to A3, A4, A5 when copied down.

As we copy the formula in cell C2 all the way down to cell C5, both of these cell references change automatically. They are relative references. Copying down changes the row reference.

We can look at this same data, transposed to columns. Now the formula in cell B3, Quantity times Price, is =B1*B2.

Relative Columns Formula

When I copy this formula across to C3, D3,and E3 you’ll notice the row stays the same, but the column reference changes.

Relative Columns

Still a relative reference, but copying across changes the column reference.

Absolute Cell Reference

Absolute Row FormulaI’ve changed my spreadsheet example to calculate the Tax for Quantity times Price. The Tax Rate is located in cell B7.

The formula for Tax in cell C2 is Quantity times Price times Tax Rate, or =A2*B2*$B$7.

The cell reference for B7 is an absolute reference, which is needed because the Tax Rate is fixed in one place.

Absolute Rows FormulasThe reference to cell B7 is modified by using the dollar sign ($) before the column and row reference.

By doing so, B7 will remain constant as I copy the formula down to C3, C4, C5.

The other two cell references are still relative references and change as the formula is copied down. Although, if you’ll notice, neither of the column references change, they’re still A and B.

What this means is that the reference to cell B7 needs only an absolute row reference for this formula to work. As you see below, B$7 is now the cell reference and row 7 will not change when you copy the formula down.

Absolute Row Only Formula

If we transpose this data to a column setup and use the formula =B1*B2*$B$5 the first two cell references are relative, and $B$5 is absolute.

Absolute Columns Formula

Copying the formula across changes the first two cell references, but not the cell reference for Tax Rate.

Absolute Columns

Notice the row reference doesn’t change, but the column reference does, so we could have used $B5, freezing the column reference with the dollar sign ($) and the formula would’ve worked perfectly.

Changing Absolute and Relative Cell References

Instead of manually typing in the dollar sign ($) there’s a shortcut to changing the cell reference. You have to be in Edit mode for this to work. Select a cell to modify, then enter Edit mode by pressing the F2 button or use the mouse to click inside the formula bar.

Select the cell reference you want change and press the F4 button to toggle through the different states. If your formula is =A1 hitting the F4 button repeatedly will give you =$A$1, then =A$1, then =$A1, then back to =A1.

Note: Don’t try this in Excel 2008 for Mac.

Absolute verses Relative Cell Reference — The True Test

The spreadsheet below is a square footage reference table for a particular room size. The formula in cell C3

=B3*C2

can be modified with mixture of absolute and relative references so that it can be copied down and across to fill the entire table. I usually funk this test, hence the reason I wrote this post.

Absolute and Relative Example

Side 1 data is fixed in row 2 and not going to change. As we learned above, copying down will change the row reference so I’ll put a dollar sign ($) in front of the reference to row 2 to make it absolute.

Side 2 data is fixed in column B and since copying across will change the column reference, I’ll put a dollar sign ($) in front of the column B reference to make it absolute.

Now my formula is

=$B3*C$2

which will work for all the cells in this table. Here’s a look at how some of these formulas appear when copied.

Absolute Relative Formulas

To keep the formatting from being copied and utilize a new feature in Excel 2010, Copy the formula in cell C3 and Paste as Formulas by doing the following:

  • Select cell C3
  • Copy, then select cells C3:J12
  • Click the Paste drop-down from the Home menu and hold the mouse over the Paste as Formulas icon to see a preview of what the paste operation will look like (new in Excel 2010) then click to complete.

Paste As Formulas Excel 2010

I’ve dealt strictly with cell references here, but ranges can also be relative, absolute, or mixed references.

Related Posts Plugin for WordPress, Blogger...