peltier tech utilities
Learn how to create Excel dashboards.

Categories

30 Excel Functions in 30 Days

Archives

Preparing for an Excel Expert Exam

Have you ever written an Excel proficiency exam? Maybe you'll have some advice or tips for the person who wrote to me this week, asking for help with the Excel Expert 2007 exam. He's having trouble with the macros and custom functions that will be part of the test.

It's been a long time since I wrote the Excel Expert exam, that was part of the old Microsoft Office User Specialist series. The exam has probably changed many times since then, but back then it was a mixture of multiple choice questions and simulated workbooks (if I'm remembering correctly!)

Anyway, I passed, and the certificate is still proudly displayed on my office wall. Well, it's pinned to the wall, behind the door, but it's still in good shape! Wow, June 1999 – that was a long time ago.  

ExcelExpert01 

The Excel Expert Test

The Microsoft website has a list of topics that are covered on the exam, including this section on Managing Macros and User-Defined Functions:

  • Record and edit a macro.
    • This objective may include but is not limited to: recording a macro and editing a macro in Visual Basic for Applications (VBA)
  • Manage existing macros.
    • This objective may include but is not limited to: moving macros between workbooks, assigning a shortcut key to an existing macro, assigning a macro to a button in a worksheet, and configuring macro security levels
  • Create a user-defined function (UDF).

Record and Edit a Macro

There are written instructions and a video on the Contextures website, for recording and testing a macro in Excel. That article briefly discusses macro security levels, and showing the Developer tab.

To see how to edit a recorded macro, you can watch the video on this blog post: Excel VBA Edit Your Recorded Macro. There are written instructions there too, in case you'd prefer to read about it.

Manage Existing Macros

If you need to copy macros into a workbook, or from one workbook to another, there are instructions here: Adding Code to a Workbook

For details on assigning a macro to a worksheet button, take a look at this page: Excel VBA Worksheet Macro Buttons. To see the code and buttons, you can download the sample workbook from that page.

image

And, of course, if you want an extensive online course that covers all this, and much more, you can enroll in Chandoo's Excel VBA school.

______________

Hide Pivot Table Detail Without Filtering

To focus on specific data in a pivot table, you can use report filters or field filters.

FilterIntro07

However, using those filters affects the pivot table subtotals and grand totals. Only the amounts for the filtered data are included in the results.

FilterIntro01

Collapse or Expand the Fields

Another option for hiding the pivot table details is to collapse one or more of the items, so only its subtotal is showing. In the screenshot below, I'm about to click on the Collapse button for the Bars category in the pivot table.

PivotTableExpand02

The Bars data is still in the pivot table, but only its subtotal is showing, not the City detail rows. The subtotals for East and Bars are not affected.

PivotTableExpand03

Show the Details Again

After you're finished focusing on the specific data, you can show all the detail rows again.

A quick way to show all the details for a field is to right-click on an item in the field, such as Bars.

Then, in the popup menu, click Expand/Collapse, and click Expand Entire Field.

PivotTableExpand01  

Watch the Pivot Table Video

To see the steps for hiding or showing the detail items in a pivot table, you can watch this short Excel video tutorial.

Or watch on YouTube: Hide Pivot Table Detail Without Filtering

___________

Excel UserForm Data Entry Update

Someone emailed me this week, about a problem he was having with my sample Part Data Entry UserForm.

PartsInventoryUserForm01

When I took a look at the workbook, everything seemed okay, and the code had been copied and altered correctly.

Then I noticed that there was a formatted Excel table on the data collection sheet, which wasn't in my original file. That can cause problems if you're using Excel VBA to add data to the first blank row on the worksheet.

Change the Last Row Code

In the comments for my Find First Blank Row blog post, Rick Rothstein suggested this code revision:

LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

Rick mentioned that this formula ignores cells with formulas that are displaying the empty string. If your situation is such that you need to identify formula cells that might be displaying the empty string, then change the xlValues argument to xlFormulas.

So, I changed the Part Data Entry code, to use the Find method for finding the last row. I replaced this old line of code:

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

With this line of code:

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
       SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

Parts Data Entry UserForm With Combo Boxes

There is another version of the Parts Data Entry UserForm, and it's a little fancier, with combo boxes to select parts and locations. I have updated the Parts Data Entry UserForm With Combo Boxes too, with the revised last row code.

PartsInventoryUserForm02 

Get the Updated Sample Files

You can download the updated versions of the parts data entry forms on the Contextures website.

Excel VBA School

If you're struggling with Excel programming, Chandoo has re-opened his Excel VBA school and registration is ongoing. You can sign up for the VBA classes only, or choose a package that includes Excel School, and you could even add the Excel Dashboards class, and become an Excel master at your own pace.

The VBA course starts at the beginner level, and there are 13 weeks of classes that are designed to take you to the advanced skill level. Online videos show you the steps, and there are workbooks and code examples to download.

You can see the details, and sign up here:  Chandoo's Excel VBA school

_________________

Updated Excel Weight Loss Tracker

You know how tough it can be to maintain two versions of the same file. It creates twice as much work for you, and no extra rewards!

So, instead of having two versions of the Excel weight loss tracker – pounds and kilograms – I've rolled them into one workbook. For now, there is a separate file for the stone measurements, because it has a different layout on the data entry sheet. If possible, I'll roll that version in later.

Select a Measurement System

When you open the Excel weight loss tracker, go to the WeightGoals sheet and select a measurement system – pounds or kilos – from the data validation drop down.

weightlosstracker01

That changes the labels and calculations in the worksheet. Since we've selected Pounds in this example, the labels say "Lbs to Lose" and "Height (Inches)". Those would show "Kgs to Lose" and "Height (cms") if we had selected Kilos.

weightlosstracker02

Measurement System Lookup Table

The workbook has a measurement system lookup table, which uses INDEX and MATCH formulas to find the correct information for the measurement that you selected. Even the chart title changes, based on your selection.

weightlosslookup 

Weight Loss Tracker Improvements

In this version of the weight loss tracker, there is a pivot table to summarize the data (why didn't I think of that before!). After you enter data, just click Refresh All, and the pivot table and dashboard chart are updated. You can add as many rows of data as you need, without struggling to change the data range.

The chart has a target line too, so you can see how close you're getting to your goal.

weightlosstracker08 

Download the Excel Weight Loss Tracker

You can download a zipped copy of the Excel weight loss tracker, and try it for yourself. The sheets are protected, with the data entry cells unlocked, and there's no password on the worksheets.

There are sample files for Excel 2007/Excel 2010 (updated version), and Excel 2003 (old version of the file) on the Contextures website: Excel Weight Loss Tracker

If you can think of any enhancements for the next version, please let me know.

______________

Excel Drop Down From List in Different Workbook

To make it easier for people to enter data, you can create drop down lists on an Excel worksheet.

Usually the source lists are stored in the same workbook as the drop downs. However, with named ranges, it is possible to use a list in a different workbook.

In the screen shot shown below, the original list is in the workbook at the left. The drop downs are in a different workbook, on the right.

DataValListLinked

There Is a Catch

My preference would be to keep the lists and drop downs in the same workbook, but if you need to have them in separate files, this technique will allow you to do that.

There's one catch though, when using this data validation technique. The source workbook, which contains the original list, must also be open, when you are using the drop down lists.

So, it's not a perfect solution, but it's fairly easy to implement, as long as you remember to open the other workbook too.

Excel 2010 Instructions

I've just uploaded a video with instructions for this technique in Excel 2010, so you can see the steps for creating the named ranges and data validation drop down lists.

The written instructions for Excel 2007 and Excel 2010 are in this blog post: Data Validation List From Different Workbook

You can also watch the video on YouTube: Excel Drop Downs From List in Different Workbook

________________

Change All Pivot Tables With One Selection

Happy New Year! I hope you had a safe and happy New Year's Eve celebration, and are off to a good start in 2012. Things got a bit rowdy at the Contextures office party, and I found these guys passed out on the floor, the next morning.

pirates

Change All Pivot Table Filters

Despite the wild parties, I was able to get some work done over the holidays. There is a new sample file on the Contextures website, that changes all the pivot tables, when you change a report filter in one pivot table.

For example, if you change the "Item" report filter in one pivot table, all the other pivot tables with an "Item" filter will change. They get the same report filter settings that were in the pivot table that you changed.

pivotmultichange01

Select Multiple Items

In this version of the sample file, the "Select Multiple Items" setting is also changed, to match the setting that is in the pivot table that you changed.

In the screen shot below, the Item field has the "Select Multiple Items" setting turned off. If any other pivot tables in the workbook have an "Items" filter, the "Select Multiple Items" setting for those fields will also change.

pivotmultichange02

How It Works

The multiple pivot table filtering works with event programming. There is Worksheet_PivotTableUpdate code on each worksheet, and it runs when any pivot table on that worksheet is changed or refreshed.

For each report filter field, the code checks for the Select Multiple Items setting, and changes it on all the pivot tables with the same report filter field. The code loops through all the worksheets in the file, and through each pivot table on each sheet.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean

On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
                pt.ManualUpdate = True
                Set pf = pt.PivotFields(pfMain.Name)
                        bMI = pfMain.EnableMultiplePageItems
                        With pf
                            .ClearAllFilters
                            Select Case bMI
                                Case False
                                    .CurrentPage = pfMain.CurrentPage.Value
                                Case True
                                    .CurrentPage = "(All)"
                                    For Each pi In pfMain.PivotItems
                                        .PivotItems(pi.Name).Visible = pi.Visible
                                    Next pi
                                    .EnableMultiplePageItems = bMI
                            End Select
                        End With
                        bMI = False

                Set pf = Nothing
                pt.ManualUpdate = False
            End If
        Next pt
    Next ws
Next pfMain

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Download the Sample File

To test the code, you can download the sample file from the Contextures website. On the Sample Excel Files page, in the Pivot Tables section, look for PT0025 - Change All Page Fields with Multiple Selection Settings. The file will work in Excel 2007 or Excel 2010, if you enable macros.

Watch the Excel Video Tutorial

To see the steps for copying the code into your worksheet, and an explanation of how the code works, you can watch this short Excel video tutorial.

Or watch on YouTube: Change All Pivot Tables When One Changes

______________________

Excel Pivot Table from Multiple Sheets Update

If you have similar data on two or more worksheets, you might want to combine that data in a pivot table, to show the summarized results. Unfortunately, the pivot table from data on multiple sheets can be a disappointment.

pivotmultipleconsol01

Create a Pivot Table with Programming

A couple of years ago, Excel MVP, Kirill Lapin (KL), shared a sample file that he created, with amendments by Hector Miguel Orozco Diaz. It uses code to automatically create a pivot table from multiple sheets in a workbook.

You can read the details here: Create a Pivot Table from Multiple Sheets.

Revised Solution

Kirill's sample file was created as a conceptual prototype, and targeted advanced VBA users. The code has minimal error handling and compatibility checks.

However, the sample file was extremely popular, and Excel users at all skill levels wanted to adopt this solution in their own applications. To make things easier, Kirill has created a similar solution based on ADO.

Advantages:

  1. No need for temporary file generation
  2. The code is faster and less prone to errors

Disadvantages:

  1. No manual refresh of the PivotTable
  2. Need to rebuild connection from the scratch to update the cache with new data

Download the ADO Sample File

You can download the new ADO version of the file from the Contextures website: PT0024 - Pivot Table from Multiple Sheets - ADO version. There is also a "Plug and Play" version of the file, at the same link.

________________

Change Excel Comment Shape

When you insert a comment in Excel, a rather boring yellow rectangle appears, where you can add your text.

commentshapechange00

That's all very proper and dignified, but sometimes you want something a bit more attention-getting.

commentshapechange01

In the good old days of Excel 2003, it was easy to change the comment shape, with a simple right-click. In Excel 2007 and Excel 2010, you need to add a command to the QAT, so you can change the comment shape.

Add the Change Shape Command to the QAT

  1. At the right end of the QAT, click the drop down arrow
  2. Click More Commands
  3. In the Choose Commands From drop down, click All Commands
    • commentshapechange02
  4. In the list of commands, click Change Shape, and click Add, to move it to the Quick Access Toolbar
    • commentshapechange03
  5. Close the Excel Options window.

Change the Comment Shape

  1. Right-click the cell which contains the comment.
  2. Choose Edit Comment
  3. Click on the border of the comment, to select it.
    • commentshapechange04
  4. On the QAT, click the Change Shape command, and click on a shape to select it.
    • commentshapechange05
  5. If necessary, drag the corner handle of the comment, to adjust its size, to fit the text.
  6. When finished, click outside the comment.

Watch the Video

To see the steps for adding the Change Shape command to the QAT and changing the comment shape in Excel 2010 or Excel 2007, please watch this short Excel video tutorial.

Or watch the video on YouTube: Change a Comment Shape in Excel 2010

 

_____________________

Show Personalized Excel Message Box

Uh-oh! It's almost Christmas and you haven't mailed any greeting cards yet. Don't worry, you can create a personalized Excel message box instead! That will warm your co-workers' hearts, and it saves paper and postage costs too.

excelmessageusername01

Get the Application UserName

In Excel VBA, you can use Application.UserName to get the registration name for the Microsoft Office applications.

excelmessageusername02a

However, some people don't update that setting, and it might show a company name, instead of the user's name.

excelmessageusername02

Get the Network UserName

Another option is to get the network user name, with a Windows API call. There is sample code in the Microsoft Knowledgebase: Visual Basic Procedure to Get Current User Name, and I've used a variation on that code in my sample file.

You can find other API code examples on the VB.Net site, and API code on The Access Web, to pull more information, such as Computer name, or locale settings.

In the sample code shown below, the network username is pulled from the DLL. This code is stored in a regular code module.

'==================================
' Access the GetUserNameA function _
' in advapi32.dll and
' call the function GetUserName.
Declare Function GetUserName _
    Lib "advapi32.dll" _
    Alias "GetUserNameA" _
     (ByVal lpBuffer As String, _
        nSize As Long) As Long
'==================================
' Main routine to Dimension variables,
' retrieve user name
' and display answer.
Function Get_User_Name() As String

' Dimension variables
Dim lpBuff As String * 25
Dim ret As Long, UserName As String

' Get the user name minus any
' trailing spaces found in the name.
ret = GetUserName(lpBuff, 25)
Get_User_Name = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
End Function
'==================================

Create the Personalized Excel Message

Next, you can create an Excel message box, and show the network username in the message text. In this example, I've also calculated the year for the current date. Then, add 1 to the year number, to offer best wishes for the upcoming year.

'==================================
Sub ChristmasMessage()
' show greeting with username and
' upcoming year
 MsgBox "  Merry Christmas, " _
    & Get_User_Name & "    " _
    & vbCrLf _
    & "  and best wishes for " _
    & Year(Date) + 1 & "!    "
End Sub
'==================================

Download the Sample File

You can visit the Contextures website, to download the personalized Excel Message Box sample file. The file is in Excel 2007/2010 format, and is zipped.

In the sample workbook, the Workbook_Open event runs this macro, so the personalized message appears when the file is opened, if macros are enabled.

Watch the Video

To see the steps for adding the GetUserName code to your workbook, and creating a personalized Excel message, you can watch this short Excel video tutorial.

Or watch on YouTube: Excel Message with User Name

__________

Excel Christmas Planner

I'm just getting back from a vacation in South Carolina, where I didn't use Excel too often, except to add up all my Christmas shopping expenses. If you're on my shopping list, you might be getting a basket from the Charleston market.

DSC_0211

Or maybe you'd like some of the local barbeque sauce or a bag of grits?

DSC_0208 

Christmas Planner

There are only 10 days until Christmas, and I'm almost ready. How about you? If you're still planning and shopping, there is an Excel Christmas planner on the Contextures website, that you can use to help you stay organized.

The Excel Christmas planner has a budget sheet, where you can plan and track your spending.

image

Christmas Gift List

If you're lucky, you don't have too many gifts to buy. But even for a few gifts, it helps to make a list to keep track of costs. The Excel Christmas planner helps you track your gifts too.

ChristmasGiftList

Christmas Tasks

The Excel Christmas planner has other sheets too, including a Christmas task list, so you can keep track of all those important things you're supposed to do over the next few days. You don't want to realize on December 24th that no one has ordered the turkey!

ChristmasTaskList 

Winter Weather

It was nice to spend some time in the warmer weather, and yes, it's colder back here in Canada, but at least we don't have to watch for alligators! Now I'd better go and order that turkey.

DSC_0161 

_______________

Related Posts Plugin for WordPress, Blogger...