Tag Archives: Excel 2011

How to add cells in Excel (screen shots of cell data added up via the SUM formula).

How to Add Cells in Excel to Sum Up Totals Automatically

Excel’s great for displaying data and even better at crunching numbers. Here’s how to add cells in Excel to sum up totals automatically… Even when you change the numbers.

A great feature that Excel has to offer is its use of formulas. Since Excel is often used to organize numerical data for a variety of operations, it can be beneficial to have an ‘addition’ function. In this guide, we are going to discuss the various ways we can add cells in Excel.

Why is the Addition Function Useful When You Need to Add Cells in Excel?

As we already know, in Excel Worksheets we can add columns. However, sometimes it is necessary to add cells that are not in columns or are not even next to each other.

For example, let’s say you are performing a survey to present data. You are interviewing random students to find out the most popular subjects taken in school. Your poll is anonymous, allows students to select only one option, and is organized by gender and grade levels Freshman, Sophomore, Junior, and Senior. The survey offers several subjects.

This survey is set up to give you a wide range of information and a variety of totals for you to examine. You could add these totals yourself, or you could use the many ways to add these numbers to Excel.

How to Add Cells in Excel: Method 1 (via Individual Table Cells)

In addition to adding columns in Excel, you can also add horizontally in rows. Let’s say you want to know how many senior males were surveyed. You can do this by adding up all the numbers in the Senior Male row. Let’s say the Senior Males row is row two in columns B2 through H2. One way to add your numbers is to select cell I2 and type the equals (=) sign, followed by the numbers in each cell in that row. For example, it may look something like this:

=5+6+2+7+0+9+1

Then, hit the enter button and you will get your total. While this method works, you will not be able to change the numbers in your cells without also changing your formula.

Because of this problem, it is better to enter the cells than the information within them. Rather than the above example, your formula will look similar to this:

=B2+C2+D2+E2+F2+G2+H2

When you hit Enter after typing this, you will still get your total. However, this time, you will be able to change the information in these cells, and the change will automatically affect the total.

How to Add Cells in Excel: Method 2 (Sum Function)

There is another method to adding rows. In this method, select your total cell. In our case, it has been cell I2. Type the equal (=) sign. Next, click the first cell in your row, or B2. This cell will become highlighted and appear next to the equal (=) sign. Then, type the plus (+) sign and click the next cells in the row. Continue this until you have clicked every cell in the row. Hit Enter to view your total.

Finally, rows can be added in the same SUM Function that we learned with columns. In your total cell, type =SUM. Then, type or click your beginning cell. Type the colon (:) mark and then type or click on the last cell in your row. It will look something like this:

=SUM(B2:H2)

Hit Enter when you have completed this function, and your total will show up. In this function, you can also change the values in each cell, and the changes will be reflected in the sum.

How to Add Cells in Excel: Method 3 (Adding Up Individual Cells In Different Rows and Columns)

The great thing about the addition function in Excel is that you not only get to add rows or columns. You can also add whatever cells you want to. Let’s stay with our survey example. Perhaps you want to examine precisely how many girls prefer math and science. Excel allows you to do this.

Label your first cell to maintain organization in your document. Click the cell next to it to begin your equation. Type the equal (=) sign, followed by all the cells you would like to add together. For example, your equation may look like this:

=C3+C5+C7+C9+D3+D5+D7+D9

Hit the Enter button when you are finished to get your total.

Of course, as we have already learned, it can be a bit tedious and take a while to type all of that. The alternate method would be to click instead of type. In your total cell, type the equal (=) sign. Then, click your first cell. Type the plus (+) sign and click your next cell. Continue this process until you have clicked all the cells you want to add. When you have selected all your cells, hit Enter to get your total.

Using this method allows you to select any cell that contains a numerical value. The values do not have to be in the same row or column or be next to each other. This method also allows you to change information in cells used in your formula. The modified data will automatically update the total to reflect the new numbers.

Conclusion

It was certainly good news to hear that we can add columns of information within our Excel Worksheets. It made life a lot easier and minimized the work we had to do.

Now, we see that we can also add rows as well as individual cells. This guide has expanded what we can do with the addition function. This function could be vital information when it comes to surveys, projects, and organizing business or personal information.

Follow this step by step guide to add cells in Excel using the addition function to its fullest, incorporating rows and cells into your skill set.

Where is Control+Home for Excel on a Mac

I wrote a post stating that I could not find the Windows Ctrl+Home keyboard shortcut equivalent on a Mac. Well I’m here to tell you that I found the keyboard shortcut combination that does the same thing on a Mac. The Excel Gods are with me. Hallelujah!

Finding My Way Home

The key to finding this elusive keyboard shortcut is in the Keyboard Viewer. On your Mac select the Apple icon () and click System Preferences… Select Keyboard, and then make sure to click the Keyboard tab. Check the box for: Show Keyboard & Character Viewers in menu bar.

Excel home key on mac

Click the Keyboard Viewer icon Keyboard Viewer Iconin your Mac menu bar and a nice replica of your Mac keyboard will appear.

Keyboard Viewer Mac

You will notice that this viewer reflects the keys you tap on your keyboard. The screen shot above shows the Command and Shift keys are depressed. The Keyboard Viewer will also show different symbols when you press various keys, like fn, Control, Option, Command, etc.

This is where I noticed something interesting. While depressing the fn key, the left arrow button changes its angle to point up about 30 degrees. Knowing that allowed me to do a little testing in Microsoft Excel 2011 for the Mac.

Excel Control+Home Key on Mac

What I found is that the Windows Control+Home keyboard combination can be replicated on a Mac by either of the following keyboard shortcut combinations. This is the home key on Mac:

fn+Command+Left Arrow

fn+Control+Left Arrow

Another mystery solved.

Keyboard Shortcuts

Another aid in finding keyboard shortcuts comes in the form of an overlay for your Mac keyboard. The kind folks over at Excel Skin™ gave me an overlay that slips over the Mac keyboard and shows, via color coding, a wide array of shortcuts that work in Excel for Mac. Here is why you might want an excel skin.

Excel without a Mac

I sold my 15″ MacBook Pro yesterday and am waiting on a 21.5″ iMac to arrive in 2-3 weeks. So no more adventures with Excel 2011 for a bit.

Time to dust off the Dell desktop and reacquaint myself with the “real” Excel. That of the Windows variety. I’m putting Excel 2003 in my rear view mirror (finally) and will focus on Excel 2007 and 2010.

I would love to get Excel 2013 and test it out, but the Dell will need to be replaced this year so I’ll wait until I have a new PC. I would like it to be a PC / Tablet combo machine with Windows 8, but am going to wait until the dust settles on the new operating system.

When upgrading to a new version of Windows I usually make it a practice to wait until after “service pack 1” is released before making the switch.

So Excel without a Mac is a survivable condition when you have a Windows PC for a backup. The converse is not true. Having a Mac without Excel would be the worst form of torture. Living with Numbers is not, in my opinion, a survivable condition. Excel on a Mac is a challenge, but nirvana when compared to Numbers.

Which leads me to wonder when the next version of Excel for the Mac will come out. I have high expectations: Power Pivot, Name Manager, Evaluate Formula dialog box, Status Bar Functions that aren’t circa Excel 2003, and elimination of the Menu bar.

However, my expectations might just be the result of some wishful thinking rooted in a dream state.

20130208-185246.jpg

Copy Symbol Image

Add Macro Button to the Toolbar in Excel 2011

You can add an icon to the toolbar in Excel 2011 for your Personal Workbook Macro. In an earlier post I created a short macro to imitate the Control+Home keyboard shortcut in Excel for Windows. You can add an icon to the toolbar to run that, or any other macro with a few quick steps.

  • Right click on the toolbar and select Customize Toolbars and Menus… then
  • Click the Commands tab, then
  • Scroll down and select Macros from the Categories pane, and
  • Drag the Custom Button with a smiley face to the toolbar, then
  • Click OK to get rid of the dialog box.

Next you:

  • Right click on the smiley face, select Assign Macro… and
  • Pick the macro you created (in your personal macro workbook), then
  • Click OK.
  • Change the Smiley Face Image

    If you don’t like the smiley face icon staring back at you want to change it (like I did) that’s easily done as well.

  • Right click on the Custom Button icon and select Properties… then
  • Type a name in the Name: box, then
  • Click the drop-down arrow beside the smiley face and select from the icon list, then
  • Click OK.
  • Use a Custom Image for the Toolbar Button

    The icon list is rather short and pathetic, but you need not restrict your choices as Excel allows you to copy and paste an image into the button face. Here is how I changed my smiley face image.

  • Choose Insert > Symbol
  • Click Shapes (at the top of the dialog box)
  • Right click on your favorite shape and click Copy, then

  • Copy Symbol Image

  • Close the Media dialog box, and
  • Right click on the smiley face toolbar icon, then
  • Click Properties…
  • Click the drop-down beside the image, and
  • Click the option Paste Button Image, then
  • Click OK.
  • Move Your New Macro Button

    In case you didn’t get your button located in the right place on your toolbar when we first started this exercise, the button is easily moved.

  • Right click on the Toolbar and select Customize Toolbars and Menus…
  • Drag your icon to a position of your choice, then
  • Click OK.
  • Personal Macro Workbook GoHome Code

    Control + Home in Excel for Mac

    One of my all-time favorite keyboard shortcuts in Excel is CTRL+Home, but on a Mac there is no Home button. Hence a constant source of frustration these last two years.

    I finally decided to do something about that and recently figured out a solution using VBA and the Personal Macro Workbook. But before I go straight to the answer, let me tell you how I got there.

    My Journey

    I knew that VBA was going to enter into the equation, so I started to record a macro on a Windows PC while using the Control+Home keyboard shortcut. What I found out is that Excel does not record that keyboard shortcut. Nothing, nada, zip.

    I then noticed that Control+Home acted differently, depending if the sheet panes were frozen or not. When sheet panes are not frozen, the Control+Home shortcut took me to cell A1. When the sheet panes are frozen, then the upper left corner cell of the window was selected.

    To make a long story short, I found out that the active window’s scroll row and column were being selected when the CTRL+Home shortcut is executed in Excel for Windows.

    The Control+Home Macro

    I also knew that if a Chart sheet were selected the macro would fail, so I crafted the following macro to mimic the CTRL+Home shortcut on a Mac. (Works on a Windows PC too.)


    Sub GoHome()
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Make sure the active sheet is a worksheet,
    ' then locate the active window's scroll row and
    ' column, and activate that cell.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim lngRow As Long
    Dim lngCol As Long

    If ActiveSheet.Type = xlWorksheet Then
    lngRow = ActiveWindow.ScrollRow
    lngCol = ActiveWindow.ScrollColumn
    Cells(lngRow, lngCol).Activate
    End If
    End Sub

    The next thing I did was create a shortcut for the macro, but realized that the macro would only work with the current workbook. Rats!

    I wanted the macro work automatically on every Excel file so I chose to store this macro in the Personal Macro Workbook, which solved the problem.

    However the shortcut key combination I assigned did not work in Excel for Mac. There was a conflict. It seems that COMMAND+OPTION+H is a reserved keyboard shortcut for the Mac. I found out this shortcut hides all windows except the one that is active.

    How I Created the Control+Home Keyboard Shortcut on my Mac

    Here are the steps I took, with a few false starts along the way.

  • Open Excel 2011 and choose Tools > Macro > Record New Macro…
  • Type a name for the macro. I used GoHome.
  • Enter a Shortcut key. (I used h, which didn’t work so I changed to g as you’ll see below.)
  • Where you see Store macro in: click the drop-down and select Personal Macro Workbook.
  • Click OK.
  • Record Macro Dialog Box

    I got a warning that the keyboard shortcut Option+Command+h was reserved.

    Reserved Shortcut

    So I chose g instead and clicked OK.

  • Next choose Tools > Macros > Stop Recording. This will effectively end the macro recording without recording anything.
  • Choose Tools > Macro > Macros…
  • Macro Dialog Box

  • Click the Step Into button on the Macro dialog box, which will take you directly inside the macro in the VBA Editor. (Note: While this takes you directly to the macro, it also starts the macro running inside the VBA Editor.)
  • Reset the Macro

  • Click the square Reset button to stop the macro program execution. (Note: If you know how to navigate the VBA editor, you can skip this last step and choose Tools > Macro > Visual Basic Editor and then locate the macro.
  • Select the entire GoHome code from this article. Or click this link and copy from a new window.
  • Copy the text,
  • Switch back to the Excel VBA editor,
  • Select the entire GoHome subroutine, and
  • Paste the code.
  • Personal Macro Workbook GoHome Code

  • Close the VBA editor by choosing Excel > Close and Return to Microsoft Excel.
  • Close VBA Editor

    Now here is the important part so pay attention. You have to save changes to the Personal Macro Workbook. You will be asked to do this when you Quit Excel.

  • Choose Excel > Quit Excel and the following dialog box will appear.
  • Save Personal Macro Workbook Changes

  • Click the Save button when asked, “Do you want to save the changes you made to the Personal Macro Workbook?”
  • Run the GoHome Macro

    Now lets check it out. Open Excel 2011 and select any cell that is not A1, then use the shortcut (mine was Command+Option+g) and watch the active cell change to cell A1.

    If your shortcut doesn’t work you can set it now. Choose Tools > Macro > Macros… and select the GoHome macro, then click Options. Type in a shortcut key and click OK.

    Now choose any cell in the top left quadrant of the current window, like C5. Choose Window > Freeze Panes. Next select any cell except C5 and run the shortcut combination for the macro, and watch the curser jump to cell C5.

    The Personal Macro Workbook will load each time you open Microsoft Excel so it’s always in the background, and you don’t get that annoying “enable macro” pop-up screen.

    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, is created with the following formula =INDEX(myTableH,MATCH(Sheet1!A2,myCategoryH,0),0) that will return a row that is matched by the contents of cell A2.

    In cell A2, add a Data Validation list with the source being =myCategoryH. In cell B2, add a Data Validation list with the source being =myItemListH in the conditional drop down list from Excel.

    Now you’re done.

    Cell A2 will give you a drop-down list of Fruit, Vegetables, or Other Stuff. And cell B2 will read the value in cell A2, match and return the proper row number, and return an array of values for that row.

    Two Ways to Use the INDEX Function to Return an Array

    This is a simple case of using the INDEX function in a slightly different way. Normally, to return column 2 of a named range, you would use the following formula =INDEX(myTable,0,2). The zero means: return all rows (of column 2).

    If you want to return row 2 of the named range you would use =INDEX(myTable,2,0). Here the zero means: return all columns (of row 2).

    This comes from the Help section of the INDEX function where in Excel 2011 it reads:

    If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press ⌘+RETURN.

    Practical Considerations

    Adding to each category list across columns is problematic. For one thing adding more data to the table will require inserting a column within the table range to avoid renaming the range. Obviously a standard Excel “Table” won’t work with this type of list. And if you have a very large spreadsheet the number of columns will become limiting long before the number of rows would.

    Download the File

    Horizontal-Dynamic-Dependent-Drop-Down-List.xlsx

    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

    Put your data validation drop-down cell on one worksheet and the reference list range on another worksheet. Example: Sheet1 contains a cell with data validation. Sheet2 contains a data range (the list) that is given a defined name of myList. Add some VBA code in the Sheet2 Deactivate routine to update the named range.


    Private Sub Worksheet_Deactivate()
    Dim rng As Range
    Set rng = Sheet2.Range("myList").CurrentRegion
    Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
    rng.Name = "myList"
    End Sub

    This is an event-based programming technique, which I commonly use with Excel 2003.

    Data Validation List Lookup 1 Sheet

    Use Some Table INDEX Magic

    This is a variation of the same thing, but no VBA programming is warranted. Instead, use a Table for your reference list data. Then create a defined name with the INDEX Function, and use that name for the data validation list.

    Tables automatically update their ranges when expanded and the INDEX function will too. Example: Create the defined name myListFormula and use =INDEX(Table1,0,1) as the formula. Then when setting the data validation list, use =myListFormula as the list reference.

    Data Validation List Lookup 2 Sheet

    Example Worksheet

    I’ve put together a workbook with the two examples listed above. The first technique, with VBA, uses two worksheets: Lookup 1 and Data 1. The Data 1 worksheet has the VBA code, which updates the named range when deactivated. You can add or subtract to this list and the data validation list on the Lookup 1 sheet will automatically be updated.

    Both Lookup sheets have data validation in cell A2, which is a list of names. I’ve added another column for the city that uses a formula to get the right value from the list.

    Lookup 1 Sheet

    Lookup 1 Sheet

    Data 2 with VBA Code

    Data 2 Sheet with VBA Code

    The second example uses Lookup 2 and Data 2 worksheets. The Table is on the Data 2 worksheet. When you add or subtract data from this Table and the defined name myListFormula will automatically update the data validation list on the Lookup 2 worksheet. Be sure to look at the Define Name dialog box (on the Mac) or the Name Manager (Windows) to see the INDEX formula.

    Define Name Dialog Box

    Download the file: Data_Validation_List_Update.xlsm

    Since there is no OFFSET function, updating at random intervals, in either of these examples, I can rest easy. That’s one less thing to slow down your worksheet.

    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 cell and click OK.

    The last cell may sometimes surprise, because Excel considers cell formatting as being “used” so you may see blank cells that are way outside your data range. Tip: Sometimes you can delete the seemingly extra rows and columns outside your data range and it will reduce the file size.

    Select the Used Range by Navigating Back Home

    Once you find the last cell, you can then hold the Shift key down and click cell A1 to select the entire range. Of course if you can’t see cell A1 in the current window there is no Home button on the Mac to help you out. (Major bummer)

    The next best thing is to hold the COMMAND + Shift keys down while you tap the left arrow and up arrow keys until you reach cell A1. This can be simple, or time-consuming depending upon size and shape of your worksheet.

    Selecting the Used Range

    To select the entire used range with VBA is a simple matter. Choose Tools > Macro > Visual Basic Editor, then choose View > Immediate Window, and type activesheet.usedrange.select inside the immediate window and hit enter.

    Create a Macro to Select the Used Range

    You can also create a macro to select the entire used range by opening the VBA Editor, inserting a Module, and entering the following code.


    Sub ActiveSheetUsedRange()
    ActiveSheet.UsedRange.Select
    End Sub

    Or you could get fancy with this code.


    Sub SelectUsedRange()
    Dim rng As Range
    Set rng = Range("A1").SpecialCells(xlCellTypeLastCell)
    Range(Cells(1, 1), rng).Select
    End Sub

    Add a Keyboard Shortcut for the Macro

    To make things simpler to run the macro, you can enter a shortcut. Just choose Tools > Macro to bring up a dialog box.

    Macro dialog box

    To add a shortcut, select a macro and click Options… which will bring up the Macro Options dialog box.

    Enter a shortcut key by clicking inside the Shortcut key box and pressing a key on the keyboard. I pressed the “u” key on the keyboard, and consequently the keyboard combination is shown as Option + Cmd + u, as you can see in the screen shot. (Depending on the key, you may also include COMMAND, Control, Shift into your shortcut.)

    Macro Options dialog box

    Enter a description if you wish, and then click OK. Select the Cancel button on the Macro dialog box to make it disappear.

    Now you can run the macro by simply using the keyboard shortcut Option + Cmd + u.

    Note: This macro will not work if a Chart sheet is selected.

    Used Range verses Actual Used Range

    The actual used range might be different than the used range. Meaning that some blank cells that are formatted might be included in the used range. Most likely you will only want to deal with a range that has some actual values. This would be the actual range.

    Please refer to the aforementioned blog post over at Contextures to see a couple of different examples of code that you can use to select the actual used range. These examples are short and use the VBA FIND function to get the job done.

    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 frustrating at times. There are a few things missing in the latest Excel Mac version, like the Name Manager and the Evaluate Formula dialog box, to name just two.

    Despite these shortcomings, I’m seriously thinking about selling my desktop PC and going exclusively with the Mac. That’s the crossroads I’m at right now. Letting go of the PC, but keeping the Windows versions of Excel.

    My Hangups

    BootCamp allows me to run Windows, and the Excel versions I need, but is very restrictive in the sense that I have to shut down my Mac, then restart with BootCamp to run Windows 7. This is a real pain in the you-know-what.

    There are other problems with running Windows on a Mac. For instance, I haven’t been able to get my Apple Magic Mouse to work with Windows 7. (Go figure.) And using Dropbox was the best way to get Excel files between Windows 7 and Mac OS X.

    My Saving Grace

    All of those issues were solved when I purchased the Parallels Desktop software for Mac. Now I can switch to Windows without having to shut down Mac OS X Lion. That’s just plain awesome!

    I’m still getting used to how Parallels works, but this software is easily worth the purchase price. And my Apple Magic Mouse, just works.

    Parallels Desktop on my MacBook Pro

    Here’s a screen shot of the Parallels Desktop open on my MacBook Pro. You can see that I have Microsoft 2010 (Windows) open, but I want you to notice that I also have the Finder dialog box open.

    Parallels Desktop

    I dragged the Card Size Lookup.xlsx file from Finder onto the Excel 2010 (Windows) program and it opened. I then made a change to the spreadsheet and saved it. The file was saved back on my Mac from whence it came. This is the miracle of modern technology.

    I still have lots of testing to do but it looks like the Parallels Desktop has made my life much, much easier. I know now which way to turn at the crossroads.

    Pay Periods and Funky PivotTable Controls

    I like to use a PivotTable to figure out simple problems in Excel. So for this post I’m going to use Excel 2011 (Mac), where PivotTable controls look funky when compared to their Windows counterpart.

    Since I get paid every two weeks, certain months in a year will contain three pay periods. Planning future vacations during these months isn’t a bad idea, so I’m going to look at pay periods for the next three years.

    Add a Column of Dates

    Two Week Pay PeriodsI’ll enter the first pay period, then create a formula that adds 14 days and copy it down to get my date range.

    Since a PivotTable will “see” the underlying serial date, I’ll need to add another column for Month and give it a “Month-Year” format so the PivotTable will group similar Months together. For this I’ll use the TEXT formula. The “Pay Period” Date is used for the first value argument, and then “mmm-yy” for the format_text argument.

    So the formula in cell B3 =TEXT(A3,”mmm-yy”)

    As you can see in the screen shot, the months Dec-11 and Jun-12 have three pay periods. A PivotTable will quickly summarize more than one year and show the number of times a pay period happens each month.

    Add a PivotTable

    The steps to create a PivotTable in Excel 2011 are as such.

    • Select a cell inside the data range
    • Click the Data tab on the Ribbon
    • Click the PivotTable drop-down arrow and select Create Manual PivotTable…

    Create Manual Pivot Table

    • On the PivotTable dialog box, click OK

    Create PivotTable Dialog Box

    You’ll get a new worksheet that shows an empty PivotTable Layout. There’s an introductory PivotTable popup box that has a link to Learn more about PivotTables, which brings up the Help system topic About PivotTables. Click the x to dismiss this help box.

    PivotTable Help Dialog Box

    The PivotTable Builder box is also shown. This object looks quite a bit different from the traditional Windows counterpart. My first reaction was that it looks funky. Nevertheless, it’s the functionality that counts.

    PivotTable Builder Blank

    Arrange the PivotTable Layout

    Click and drag Month from the Field name area to the Row Labels area. Then click Month again and drag it to the Values area. (Yes that’s right, you’re dragging Month twice.)

    PivotTable Builder Month

    In the Values area you should see Count of… and to see the rest, just click the i to bring up the field name list.

    PivotTable Field Dialog Box

    Sort the PivotTable

    Click inside the Data area (like cell B5) of the PivotTable and then select Descending from the Sort icon drop-down list on the Toolbar.

    Descending Sort

    The top of the list shows months with 3 pay periods. Just what I was looking for.

    Sorted PivotTable

    You’ll notice the descending sort doesn’t leave the Row Labels in ascending order. (Nov-12 doesn’t follow Jun-12, etc.)

    A Better Formula

    You can change the Month formula to =TEXT(A2,”yyyy-mm”) and the Row Labels will show up in year-month format in ascending order.

    PayPeriod Sorted Month Ordered

    While this took some time to explain, the reality is when I do this it takes about two minutes. And the bulk of the time is generating the dates and adding the formula.

    Zoom to 125 Percent – Excel 2011 Default Workbook

    I want to have Excel 2011 (for Mac) open to a blank worksheet that is zoomed to 125% and it has taken a very long time to figure this out. It involves saving a template to a buried location, then manipulating the file in Finder to remove the extension. Simple, once you know the trick.

    The buried location is:

      Macintosh HD:Users:username:Library:Application Support:Microsoft:Office:User Templates:My Templates:

    Or you can check to see where the Excel startup files are located on your computer. Just to go Excel > Preferences then select General and click the Select… box for At startup, open all files in. This is where a modified template file should go.

    Excel 2011 Preferences General

    Change the Default Workbook in Excel 2011

    • Open Excel 2011
    • Choose View > Zoom, then select 125% and click OK
    • Choose File > Save As
    • In the Save As: box type in Workbook
    • Click the Format: drop-down button and choose Excel Template (.xltx)
    • Make sure you save it to the startup location (discussed above)
    • Click Save

    Excel 2011 Save As Template

    Note: Keep in mind that I have set Excel preferences to have my workbooks open with just one worksheet. If you have several worksheets in the Workbook Template file, you may have to set the zoom for each sheet.

    The Default Workbook Secret

    The trick now is to open Finder and navigate to the startup folder, select the Workbook.xltx file you just created, and delete the .xltx portion of the file name. I did this by selecting the file, clicking once to highlight the name, then selected the .xltx extension and hit the delete key.

    Excel will warn you that this may be dangerous but stay the course and click Remove.

    Remove .XLTX Extension

    Now open Excel 2011 and you’ll be looking at a worksheet that is zoomed to 125% in the default workbook.

    One More Thing – New Worksheets

    We’re not done just yet because we need to do the same thing once again, but this time save the file with the name Sheet, so that when you add a new sheet to a workbook the zoom will automatically be set at 125%.

    My Summary

    The default files Excel uses for a new Workbook and a new Sheet are template files, with the extension removed, and located in the startup files location.

    A common fallacy is that Excel for Mac uses the Normal Template. This belief took me in the wrong direction for quite some time. Hopefully this post will set the record straight.

    Obviously, you can set more preferences than having a 125% zoom view of the worksheet. All you have to do is open these template files, make the changes and save (overwrite) them as template files. Then make sure the file extension is removed.

    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 that 99.4% of all Excel users have never used Microsoft Query, but I’m one who does and thought this might be significant to a select few. (Notice that Open XML for Excel and Charts uses PowerPC too.)

    Microsoft Query allows you to pull data into an Excel spreadsheet from “behind the wall,” so-to-speak, of a database or ERP system. I made a career out of doing this very thing. Getting data that others could not.

    In Microsoft 2010 (Windows) the new PowerPivot can replace Microsoft Query but in Excel for Mac there’s no replacement.

    Excel for Mac users should beware, Microsoft Query is an incompatible app in the upcoming Lion upgrade for Mac OS X.

    [UPDATE]

    Since I wrote this post Microsoft updated their Office 2011 software. If you have Microsoft Office version 14.1.2 or later Microsoft Query has been updated to Intel, as you can see in the screen shot below, instead of PowerPC, and will now work on your Mac with OS X Lion.

    MS Office Update for MS Query

    Thanks to J. Monroe who pointed out the update by Microsoft in a comment.

    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 delete button on a Mac. To press the delete button on Mac computers you have to hold down the fn key and the Delete key at the same time when you want to delete something on a Mac. (Skip to video)

    After a while, you get used to the idea that the Delete key on a Mac is really a backspace key and using fn+Delete gives you the real Delete key action. 🙂

    Of course if you’re a long time Mac user you probably think I’m cuckoo. But hey, this is my blog, think what you like. I’m not the only one who’s decided to start using a Mac after a lifetime of Windows abuse use.

    Excel for Mac

    Anyway, when using Excel on a Mac — I’ve got versions 2008 and 2011 — you run into a learning curve with all the unusual shortcut keys, function keys (1, 2), and menu and ribbon things that are different from the Windows version of Excel. So there’s a tendency to forget about how the Delete key works on a Mac.

    I mean, this is Excel we’re talking about here. Hitting the Delete key is supposed to delete the contents of the active cell, for cryin’ out loud.

    In Excel for Mac it does that, but the cursor also gets stuck inside the cell in edit mode. You have to hit the enter key to finish deleting the contents, but this act also moves the active cell to the next cell down.

    And if you’ve selected a range and hit the Delete key, the active cell contents are deleted and the cursor is stuck inside the cell in edit mode. You have to hit the Enter key, which does nothing but take you to the next cell. The range contents are still there, with the exception of the active cell.

    Not the kind of behavior that occurs in Excel for Windows.

    How to Delete Cell and Range Contents in Excel for Mac

    The trick is to remember that fn+Delete is really a keyboard shortcut to the Delete key on a Mac. Then the world rights itself and the planets align. Frustration abates. You’ve finally found the magic. Your mojo is back!

    Watch this 54 second video to see what I’ve been babbling about for the past 454 words.


    YouTube link

    A Dynamic Dependent Drop Down List

    A Dynamic Dependent Drop Down List in Excel

    A Dynamic Dependent Drop Down ListThe other day I was reading a post over at the Contextures blog about Dynamic Dependent Excel Drop Downs and realized that using an Excel Table would provide an alternative method that is both simple and flexible. Tables are available in Excel versions 2007, 2010, and 2011.

    In this post I’ll create a Table to hold the Category’s and Items, create three defined names using dynamic formulas, then use Data Validation to create two drop-down lists, the second being dependent upon the first.

    Create at Reference Table

    Here’s a Table with Category names in the header row and Items in the columns. I just typed in the information then converted to a Table.

    Category and Item Table

    Create a Dynamic Defined Name for Category List

    Create a defined name for the Table1 Header row range by using the formula =Table1[#Headers]. Please note that Table1 is the name of the Table created in the step above.

    This defined name is dynamic, meaning it will expand when more columns are added and shrink if any columns are deleted. It will return the header row of the Table, which we’ll use in the next step. I used myCategory for this defined name.

    Create Category Defined Name

    To create a defined name in Windows choose Formulas > Name Manager then click New…. On a Mac choose Insert > Name > Define…. This will bring up the New Name dialog box that looks like the Edit Name screen-shot shown above.

    Create a Category Drop Down List with Data Validation

    Type Category in cell A1 for the column heading. Next select cell A2, then choose Data > Data Validation > Data Validation… and in the Data Validation dialog box (shown below) select List from the Allow box, then type in =MyCategory in the Source box, and click OK.

    My Category Data Validation

    Now cell A2 has a drop down button that shows the Header row of the Table. (Don’t worry about extending this Data Validation down to more rows, we’ll take care of that later.)

    Category Drop Down List

    Column B will hold a drop-down list for the Item, which is dependent upon the Category. This takes two defined names to work properly.

    Create the First Defined Name for Items

    [Update: Select cell B2 before you follow this next step.]
    Create a defined name with the following formula:

    =INDEX(Table1,0,MATCH(Sheet1!A2,Table1[#Headers],0))

    that I’ll name myItemList.

    My Item List Defined Name

    This formula will return a reference to the Table column that matches the Category selection in cell A2 on Sheet1.

    If I use Data Validation to create a drop-down list with the myItemList defined name I’ll get eight items returned because Table1 has eight data rows. As you can see in the picture below, the Vegetables item list has two blank lines, and the Other Stuff item list has one blank line.  Not an elegant solution.

    Table Column Full List

    However, if you have a table that always has equal items (rows) in each column then this defined name formula will will work well for a Data Validation list.

    Create a Second Defined Name for Items

    We can create a second defined name that will give us a dynamic list with the exact count. The following OFFSET formula will do the trick.

    =OFFSET(myItemList,0,0,COUNTA(myItemList),1)

    This formula uses the first defined name (myItemList) but alters the height by counting the items. I named this defined name myItem, as you can see below in the Name Manager screen shot.

    Name Manager for myList

    Create an Item Drop Down List with Data Validation

    Type Item in cell B1. Select cell B2 and open the Data Validation dialog box. Choose List and enter the following formula =myItem, then click OK.

    Data Validation Item List

    The result is a dynamic drop-down list in the Item column that’s dependent on the Category selection in column A, and returns the exact list.

    Dynamic Dependent Drop Down List

    Convert to a Table

    Now it’s time to covert this to a Table, and by doing so the Data Validation will be preserved and automatically expand with the addition of more rows.

    Select cell A2, then in Windows (Excel 2007, 2010) choose Insert > Table, verify the information in the Create Table dialog box, and click OK.

    Create Table in Windows

    On a Mac (Excel 2011), select cell A2 then choose Tables > New > Insert Table with Headers.

    At this point I normally turn off the Data Filter because it’s rather annoying.

    As your Table expands, with more rows or columns, this dynamic drop-down list will work just fine. To create a new row in the Table with Data Validation press the Tab key while the active cell is the last column of the last row.

    Potential Problem with OFFSET

    As you might have guessed the OFFSET formula depends on having items at the top of the list. Should your data have blank rows in the middle of the column, the drop-down won’t have all the items listed yet show blank cells in the list.

    Blank Cells and Missing Data

    In this case it’s best to use the first defined name (myItemList) in creating a dependent, drop-down list for Item. At least you’ll get all the data, even if it does have some blanks in the list.

    Blank Cells and No Missing Data

    Reference Table Location

    For illustration purposes I’ve shown the reference Table on the same worksheet as the Category and Item Table. Normally I would place the reference Table on a different worksheet. In this instance none of the formulas would change.

    [UPDATE]

    I’ve had numerous questions about the details of this post so I’m putting a link here to download the file.

    Where is the Insert Tab in Excel 2011?

    Switching to the Mac platform finds you with certain disappointments challenges. One of these is finding no Insert Tab on the Excel 2011 Ribbon.

    Windows versions of Excel (2007 and 2010) both have the Insert Tab on the Ribbon. So when you start using the new Mac version of Excel 2011, you can quickly become confused at the whereabouts of some familiar controls.

    Excel 2011 has a Charts tab on the Ribbon that doesn’t exist in the Windows versions (2007, 2010). This is where the Charts and Sparklines Groups on the Insert tab are located.

    Excel 2011 reminds me of a hybird version because while there’s a Ribbon, they left the old Menu bar at the top. This is reminiscent of Excel 2003 (Windows) with familiar menus for File, Edit, View, Insert, Format, Tools, Data, Window, and Help.

    Generally speaking, the Insert menu in Excel 2011 holds most of the items in the Illustrations, Links, Text, and Symbols groups from the Excel 2010 Insert tab.

    A Table to Help

    I put together a table of each item on the Excel 2010 Insert tab, and it’s location in Excel 2011.

    In the table below the two left columns show the Group and Item on the Excel 2010 Insert tab. The column on the right either shows the Ribbon location or the Menu location.

      • Data > Analysis > PivotTable is an item location on the Ribbon.
      • Insert – Photo – Picture from File… is an item location in the old Menu system.

    You’ll also notice a few items that don’t exist (to my knowledge).

    Excel Insert Tab Item Location Excel 2011

    A quick alternative to some of these items in Excel 2011 is the Media Browser icon on the Toolbar, which gives you access to Photos, Audio, Movies, Clip Art, Symbols, and Shapes to insert into the worksheet.

    Since you may want to refer to this table a little more closely here’s a PDF file to download.

    Related Posts Plugin for WordPress, Blogger...