Loading...

Follow The Spreadsheet Guru on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


How To Troubleshoot This Persistant Error Message

If you are reading this article you have probably been frustrated with a persistent error message that Excel keeps showing you stating:

“Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct.”
— Love, Microsoft Excel

Well, thank you Excel for giving us a blanket, catch-all statement that is essentially telling us, "I've found an issue with your spreadsheet, I'm not going to tell you where it is or even what type of object is causing this...but enjoy scouring the entire file! Oh, and I almost forgot, I'm going to keep reminding you about this error until it's fixed."

So after clicking OK about ten times, you've probably more than a little time trying to figure out what is causing this message to keep popping up. Well, I have been in your shoes feeling the exact frustration and hopefully I can guide you through the different parts of your spreadsheet that may be housing this invalid formula reference. Best of luck!

Isolate The Specific Spreadsheet Tab

Sometimes if you have a enormous file with lots of tabs, you may want to start by isolating the specific tab(s) that are causing this error message. You can do this by copying over your tabs one-by-one and determining which tab in your new workbook triggers the error message. Make sure you save after each tab is copied over in case the Saving event is what triggers the error message.

Check External Links

Navigate to your Data Tab >> Queries & Connections >> Edit Links and see if any of your links contain errors in them. If you find any, simply remove (or break) those particular links. This may or may not solve the issue, but at the very least you are getting rid of some garbage that could causing corruption to your file down the road.

Check The Name Manager

It is a good idea to scroll through the Name Manager (dialog box that allows you to manage all your named ranges) and see if any of the formulas associated with your Named Ranges include errors (ie #N/A, #REF, #VALUE!, #NAME?, #DIV/0). Go ahead and delete all of the Named Ranges with errors and see if that action fixes your issue. There is even an option to filter on Named Ranges with errors as shown below that can make this process even faster. 

click to enlarge

Check Your Charts

I'm not going to lie, Chart object errors causing this message are the most painful to track down, especially if you have a large number of charts on a given tab. Typically, if the error is within a chart it will pop up after you have selected the chart or when you are entering the Select Data Source dialog box.

Below is a list of locations to check for errors.

  • Check for errors in the chart's source data (aka the cells feeding your chart)
  • Check for errors in each chart series formula (within Select Data Source dialog)
  • Check for errors in the horizontal axis formula (within Select Data Source dialog) 
  • Check for errors in the chart's Secondary Axis (if applicable)
  • Check for errors in any linked Data Labels, Axis Labels, or the Chart Title

Finally, if all else fails try Copy/Pasting the chart and see if the new copy gives you the same error message. If not, simply delete the old chart and use the copy going forward.

Check Your Pivot Tables

There could be an error residing in your Pivot Table's Data Source formula. Select your Pivot Table and navigate to PivotTable Tools >> Analyze >> Change Data Source >> Change Data Source... and see if your formula has any errors in it.

Any Other Solutions I Missed?

I believe I have covered all the problem areas I have seen in my years of working with Excel, but if you have come across other locations within your spreadsheet file that have caused this error message to show, please let us all know about it in the comments section below.

I wish you the best of luck and I hope this article gets you heading in the right direction!

Chris :)

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Lately, I have been writing a lot of VBA code dealing with the creation of shapes and managing them. I want to share one powerful little trick with you that makes the management part a lot easier. I've used this technique in projects such as the Guru Tab PowerPoint add-in and the Tickmark Excel add-in.

Let's walk through a very simple example and you'll better understand the types of scenarios I'm talking about.

How You Might Use VBA & Shapes In The Real World

For this example I'm going to use my most frequently used shape macro of all time called RedBox. I use this macro almost everyday to add a red box around selected cells that I want to bring attention to on a given spreadsheet. It looks a little something like this:

Sub RedBox()
'PURPOSE: Create a Red Box to Highlight Cell Selection
'SOURCE: www.TheSpreadsheetGuru.com

Dim Box As Shape

'Create a Red Box based on Cell Selection
  On Error GoTo Select_Cell:
    Set Box = ActiveSheet.Shapes.AddShape( _
      Type:=msoShapeRectangle, _
      Left:=Selection.Left, _
      Top:=Selection.Top, _
      Width:=Selection.Width, _
      Height:=Selection.Height)
  On Error GoTo 0

'Format the Red Box
  Box.Fill.Visible = msoFalse
  Box.Line.Visible = msoTrue
  Box.Line.ForeColor.RGB = RGB(255, 0, 0)
  Box.Line.Weight = 2.25

Exit Sub

'ERROR HANDLER
Select_Cell:
  MsgBox "Select a cell in order to use this button."
    
End Sub

 

Now, what if I want to write another macro that removes all the red boxes from the spreadsheet? Most people would probably write some VBA that loops through all the shape objects on the spreadsheet and deletes them.

Sub DeleteAllShapes()
'PURPOSE: Remove All Shape Objects From The Active Worksheet (Excludes Charts/Comments)
'SOURCE: www.TheSpreadsheetGuru.com

Dim shp As Shape

For Each shp In ActiveSheet.Shapes
  If shp.Type msoChart And shp.Type msoComment Then shp.Delete
Next shp

End Sub

This might work in most cases, but what if you added others shapes in addition to the red boxes? Now all your shape objects have been wiped out!

Tagging, A Simple Solution To Track Your Shapes

My strategy for managing shapes within my VBA code is to manipulate the shape name. What I do is predetermine a unique tag to add to either the beginning or the end of the newly created shapes name. This way I can bucket the shapes based on my tags.

So let's take that same "Create a Red Box" macro and add the tag "_RedBox" to the end of the shape name so we can easily track it later on.

Sub RedBox()
'PURPOSE: Create a Red Box to Highlight Cell Selection
'SOURCE: www.TheSpreadsheetGuru.com

Dim Box As Shape

'Create a Red Box based on Cell Selection
  On Error GoTo Select_Cell:
    Set Box = ActiveSheet.Shapes.AddShape( _
      Type:=msoShapeRectangle, _
      Left:=Selection.Left, _
      Top:=Selection.Top, _
      Width:=Selection.Width, _
      Height:=Selection.Height)
  On Error GoTo 0

'Rename Box with Tag
  Box.Name = Box.Name & "_RedBox"

'Format the Red Box
  Box.Fill.Visible = msoFalse
  Box.Line.Visible = msoTrue
  Box.Line.ForeColor.RGB = RGB(255, 0, 0)
  Box.Line.Weight = 2.25

Exit Sub

'ERROR HANDLER
Select_Cell:
  MsgBox "Select a cell in order to use this button."
    
End Sub

After adding over 100 red boxes and 100 circle shapes to the spreadsheet, lets say we decide we want to make all the red boxes blue instead. Since we have both circle and box shapes throughout the spreadsheet, we need to find a way to specifically target just the red boxes.

Let's assume our Red Boxes were created with a macro that added the tab "_RedBox" to the end of the shape name. We can easily loop through all the shapes on the spreadsheet and zone in on a specific tag. Below is an example macro of how you could target just the red boxes and turn them to blue boxes by using my tagging methodology.

Sub RedBox_to_BlueBox()
'PURPOSE: Convert All Red Boxes To Blue Boxes

Dim shp As Shape
Dim Tag As String

'What is the Tag Name for the Red Box Shapes?
  Tag = "_RedBox"

'Loop through each shape in ActiveSheet
  For Each shp In ActiveSheet.Shapes
    
    'Test for Tag Name at the end of Shape Name
      If Right(shp.Name, Len(Tag)) = Tag Then
        
        'Change Line Color To Blue
          shp.Line.ForeColor.RGB = RGB(0, 0, 255)
        
        'Change Shape Name Tag
          shp.Name = WorksheetFunction.Substitute(shp.Name, Tag, "_BlueBox")
          
      End If
      
  Next shp

End Sub

The end result has our green circles remaining untouched and the red boxes converted into blue boxes! Now this is a very basic example, but hopefully it illustrates the logic behind how you can identify objects.

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! 

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris :)

 

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
It's That Time Of Year Again!

If you love analyzing numbers (and chances are if you are a fan of this website you do), then I am sure you can relate that your skill of tracking digits carries over to your home life as well. I am so excited to share with you one of my personal Excel trackers that I use to keep a close eye on my Christmas present spending. It comes complete will a nifty Christmas-themed dashboard so you can instantly know how you are doing throughout the holiday season. I think you will enjoy having this simple tracking spreadsheet and hopefully you'll be able to keep your spending under control this Christmas season!

An Awesome Status Dashboard

Dashboards seem to be all the rage currently and it's very easy to understand why -- they can instantly tell you the story behind your data. Below is what my Christmas expense tracking template dashboard looks like.

Two Simple Tables To Track Your Progress

I like keeping things simple, because who really wants to spend all their time creating a bunch of data points that aren't really that important. With my present tracking template you have two tables to populate.

The first one (on the left) lets you set a budget for each one of your friends & family members who will be receiving a Christmas gift from you this year.

The second table (on the right) is where you will record how much you have spent on each person. 

Both of these tables feed into your dashboard, allowing you to have a visualization of your data in real-time!

All Graphics Made With Only Excel Shapes

OK...this may not be a cool feature, but I just wanted to highlight that you can make beautiful icons and graphics without super expensive software. And it's REALLY EASY! If you are curious how the any of the graphics are made, you can simply unprotect the dashboard (Review Tab > Unprotect Sheet -- there is no password) and see for yourself how they were created. Below is a breakdown of how I made the reindeer.

Get Yourself A Copy Now!

It is super easy to download this Excel template. You will just need to provide me with your email address within the form below and the file download will be instantly sent to your inbox. The email subject line should say "You Got Christmas Spending Tracker!"

While I am offering this template for free, I inevitably always get people who want to chip in a few bucks as a thank you. So it's up to you, you can keep the price at $0 or pay whatever you want :) You have the option to pay via credit card or PayPal.

... What Do You Think?

As this is a personal template, I would love to get you input on how to make it better. Also, if you notice any bugs please let me know in the comments section below. I will always update this post with the latest version as well as record what the latest version is (see above). I look forward to your feedback :)

Have a Merry Christmas!!!

Chris "Macro" Newman

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I came across this oddity today where I was going to print a report someone sent me and the data didn't appear to expand all the way to the right. My printout was looking like the below screenshot with a huge right margin space!

After fiddling around for what seemed like forever in the Print Settings, I discovered that the Print Titles section was causing the issue.

After deleting the Columns to Repeat at Left range, my print margins appear to return to normal.

I have no idea why this occurs but hopefully, this post prevents you from continuing to pull out your hair with this weird margin issue.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

As Microsoft continues to persuade people to sign up for their Office 365 subscription in lieu of purchasing individual copies of Office, I am hoping that the Office team moves towards sending out more updates on a regular basis (similar to how Google does with their Chrome browser).

With this in mind, I wanted to take the time to air some of my grievances with Excel. While Excel is an awesome program and the Microsoft team continues to develop amazing updates, it seems like there are a bunch of little tweaks they could make that would go a long ways in helping us daily users become more efficient.  Here is my current list:

1. The Concatenate Function

One of the functions that always has annoyed me is the CONCATENATE function. It only accepts individual cell addresses instead or range references. For example =CONCATENATE(A1:C1) is NOT valid, while =CONCATENATE(A1,B1,C1) is. In my mind, this function is useless, since writing =A1&B1&C1 is much quicker.

Oh, and adding an optional delimiter input would be a nice touch too :)

2. Case Sensitive Cell Formatting

I love using dates as my headers for months. What really annoys me is you can't make a cell format for JAN. You would think by using "MMM" the formatting would capitalize the three-letter abbreviation for the month....but it doesn't. I wish Cell Formatting was case sensitive so "MMM", "mmm", "Mmm" each would output different formats.

3. Updated Userform/ActiveX controls designs (they are ugly!)

If you have ever seen an Office userform, you might think it was created a long time ago...we are talking about 1990s old! It would be great if Microsoft could either update the appearance of the whole suite of userform and ActiveX controls or add more customizing capabilities so we can bring them up to date ourselves.

4. Center Across Ribbon Button

It has been well documented that people should stay away from merging cells and use Excel's Center Across functionality. So why don't we add a button right below Merge & Center for Center Across? There's even room for it!

FYI, I took matters into my own hands and created an Excel add-in that does this for you. You can learn how to download it for free here.

5. Unhide Multiple Worksheets

Why can we only unhide one worksheet at a time? At least let us use the Ctrl key to select multiple sheet names from the list.

6. Ability to Remove Cell Styles

Have you ever received a file with (literally) thousands of Cell Styles stored in it? This can cause errors in your spreadsheet and makes the file size HUGE. So why can't we just have a button that lets us reset/remove all these useless things?!

Does anybody even use cell styles anyways?

7. Paste Special Row Height

Why can we paste special column width but not row height?

8. Custom/More Icons in the QAT

Have you ever been frustrated when you spend a bunch of time creating this super awesome macro and then when you go to add it to your Quick Access Toolbar, you can't seem to find an icon that makes sense? You then cycle through the 30-some icons over and over again.....then you just say screw it and pick the smiley face.

It would be nice to have the ability to upload our own icons or at least have the ability to call any of the icons from the Microsoft Icon Library.

Also, it would be nice if we didn't have to have those default green circle icons for the features that didn't make the ribbon cut so they don't have their own unique icon. How are we suppose to tell one green button from another?

9. Custom VBA Functions Recalculate Reliability

I would love to use custom VBA functions within my spreadsheets, but I am always scared to use them because I never know if they will automatically recalculate when changes occur to my numbers. I know you can write code to make the function volatile, but I always seem to have problems getting it to recalculate every time.

10. Turn Off Page Breaks

I understand the concept behind the page break indicators, but they really annoy me. They annoy me some much, that I have a dedicated macro for turning them off in my QAT. I wish there was an option where I could permanently turn off page breaks so I never have to see them again!

11. Tabbing In Text Boxes = Too Long

I absolute love to use text boxes to add instructions for my workbooks. One thing I can't comprehend is why when you TAB inside a text box, it equates to 28 spaces (yes, I took the time to count out the spaces just for this article). I wish tabbing was a bit more reasonable...maybe around 4 spaces for each tab? Just a thought :)

12. ENTER Key Disabled Inside Text Boxes

Why won't the ENTER key on your keyboard's 9-digit number pad function to go to a new line inside a text box????

13. Inserting Copied Rows Multiple Times In A Row

This also occurs while copy/inserting entire columns, but I get very frustrated when I need to copy 3 specific rows and insert them into the middle of my spreadsheet multiple times. Excel will only let you insert copied rows once, then you have to re-copy the rows and repeat. This can be super time consuming!

14. Use Arrow Keys To Move Chart Data Labels

Have you ever had a bunch of trend lines and needed to include data labels? Nine times out of ten when you add the data labels, you need to manually move them so they don't overlap each other. Unfortunately, the only way to nudge the labels is by using your mouse.Why can't Microsoft allow the arrow keys to move the data labels when they are selected individually?

 Time To Air Your Own Grievances!

Now that I've laid out my own list of wishes, it's time for you to share yours! Leave a comment below and I might even add it to the article list! Hopefully one day some Microsoft developers stumble across this article as they search for new ideas to improve Excel (you never know, it could happen). I look forward to reading through your ideas!

[UPDATE] Microsoft Now Has A Website For This!

As of November 2015, Microsoft has set up a UserVoice website solely dedicated to gauging the popularity of feature requests for Excel. This leads me to believe that the Excel developers are planning to start releasing added features on a more frequent basis now that they have a large subscriber base through Office 365. I've added some of the Excel weaknesses I'd like to see addressed to the site, so if you have a couple minutes adds some votes to what I've posted so we can get these things fixed!

Here are links to my suggestions:

 

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Slicers Are Great, But Have Limitations

Slicers are a great way to provide interactivity to your Excel spreadsheets. They allow you to ditch the need for drop-down lists and instead give your users the ease of simply selecting a button to filter/change the displayed data.

Unfortunately, Microsoft has left these handy little buttons very simple in their functionality which has hindered some of us creative folks from pushing them past their limits. One of these limitations is manipulating the order in which the Slicer buttons are organized.

When you are creating Slicer buttons you typically want to keep your most used buttons on the top row. This is because most people are going to scan through the buttons similar to how they read a book and you don't want to waste your user's time by placing the most popular buttons somewhere in the middle of the sea of buttons.

Slicers only allow you to organize your buttons in alphabetical order (ascending or descending). Since you can't always depend on your most popular buttons starting with the letter "A" this could pose a problem.

Real World Example With Quarters and Months

Let's look at an example situation where we want to create Slicer buttons that give our users the ability to filter on a particular month or quarter. In this scenario, the quartes are going to be the more popular way at looking at the data as the team that uses this file only reports results every 3 months.

With quarters being classified as the "popular" buttons, we will definitely want to make these show up on the top row. While you can force them to the top row sorting Z to A, they read backwards which is most likely not what you will be wanting.

One solution you could implement is to number your buttons, however this could potentially mess up other parts of your spreadsheet (ie lookup formulas) or cause confusion (ie "05. Jan" -> Isn't January the first month?). So this might not be a viable solution for your situation.

My Solution: Use A Custom List

In my proposed solution, I will be using Custom Lists to tell the slicers the specific order I would like the buttons to be in.

You may not realize this, but you use Custom Lists all the time! Have you ever dragged a cell with the value "January" across to list out all the months? The reason this work, is because Excel comes with a pre-built in list that lists out all the months when certain values are dragged.

Below are the 4 lists that exist by default with Excel:

So, let's try and create a Custom List based on how we want the buttons to read. In the blue table below, I have listed out the order of how I want the buttons to be laid out reading left to right, moving downward. You can see that I would like each quarter going across in the top row and then the 3 months that make up that quarter underneath.

Now that we have some cells with the precise order we want, let's go ahead and carry out the following steps to build our Custom List:

  1. Select the File tab
  2. Click Options
  3. Pick the Advanced section in the left-side pane
  4. Scroll all the way down to the bottom of the options list
  5. Click the Edit Custom Lists button

Once you are viewing the Custom Lists dialog window, you have a couple of options to create your list. You can either manually type out your list in the text field or you can use the Arrow button to select cells with the list values in them. In this example, I have already listed out the values on my spreadsheet, so there is no point in re-typing them manually. So let's take the following steps:

  1. Select the Arrow button and highlight the cell range listing the values you would like to add to your Custom List
  2. Click the Import button and you should now see your values populate the List Entries listbox
  3. Click the OK button to confirm your new Custom List

Now that we have created our custom list, we can right-click on the Slicer object and sort from A to Z to get our desired result!

Things To Note Afterwards

Custom Lists are an Application-Level setting! This means if you open another file and drag values residing in a Custom List, Excel is going to use the next list value and not just duplicate the original value. So don't be confused when this happens! That's why I recommend deleting the Custom List unless it is a list you plan on using in your spreadsheets a lot.

If you re-sort, you could lose your order! If you happen to take my advice and immediately delete the Custom List you used to reorder your buttons, be aware that if you try to sort the buttons in the future, the slicer buttons will revert back to an alphabetical order.

 How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! 

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris
Founder, TheSpreadsheetGuru.com

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Referencing Worksheets in Excel

When you are working with VBA inside Excel, it is more than likely that you will be automating some sort of changes to a worksheet inside your file. The following VBA guide is intended to show you have your can target specific worksheets within your workbooks in order to apply changes to them.

 Reference Worksheet By Code Name [BEST PRACTICE!]

Sheet1.Range("A1").Value = 100

Reference Worksheet By Name

ThisWorkbook.Worksheets("Summary Tab").Range("A1").Value = 100

Reference Currently Viewed Worksheet

ActiveSheet.Range("A1").Value = 100

Reference Worksheet By Position

ThisWorkbook.Worksheets(3).Range("A1").Value = 100

Reference Last Worksheet In Workbook

ThisWorkbook.Worksheets(ThisWorkbook.Sheets.Count).Range("A1").Value = 100

Reference Worksheet Within Another Workbook

Workbooks("Book2").Worksheets("Sheet1").Range("A1").Value = 100

Store Worksheet To A Variable

Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets("Summary Tab")

Store Newly Created Worksheet To A Variable

Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets.Count)

Loop Through Every Worksheet In A Workbook

Sub WorksheetLoop()

Dim sht As Worksheet


For Each sht In ThisWorkbook.Worksheets
  
  sht.Range("A1").Value = 100

Next sht

End Sub

Loop Through Every Worksheet In Reverse Order

Sub WorksheetReverseLoop()

Dim x As Long

'Reverse Loop Through Sheets
  For x = ThisWorkbook.Worksheets.Count To 1 Step -1
    ThisWorkbook.Worksheets(x).Range("A1").Value = 100
  Next x

End Sub

Loop Through A List Of Worksheet Names

Sub WorksheetListLoop()

Dim SheetList As Variant

'List Sheet Names into an Array Variable
  SheetList = Array("Sheet1", "Sheet4", "Sheet6")

'Loop through list
  For x = LBound(SheetList) To UBound(SheetList)
    ThisWorkbook.Worksheets(SheetList(x)).Range("A1").Value = 100
  Next x

End Sub

 How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! 

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris
Founder, TheSpreadsheetGuru.com

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Excel's row/column grouping functionality is a handy way to consolidate or provide a hierarchical order to your spreadsheet data. The concept is pretty straight-forward, you select some rows or columns and click the Group button within Excel's Data tab.

This action adds expand/collapse buttons either to the side or top of your spreadsheet cells. Below are two common views in which you might organize your data with the Outline groupings.

Steps To Change Collapse Direction

By default, the Excel settings for groupings are set to "Summary Rows Below Detail" and "Summary Columns to Right of Detail". Here are the steps to change the vertical or horizontal direction of Excel's Outline Groups:

  1. Select the Data Tab
  2. Within the Outline group, click the dialog launcher button
  3. The two checkboxes within the Direction section of the Settings Dialog box will allow you to control which direction your outline groups expand/collapse
  4. Click the OK button
How To Change Settings With VBA

If you happen to need to modify these outline group settings with VBA code, the following snippets show how on the currently viewed spreadsheet.

Collapse Detail is Located Above the Total Row (Summary Row Below)

Sub CollapseRowsBelow()

ActiveSheet.Outline.SummaryRow = xlBelow

End Sub

Collapse Detail is Located Below the Total Row (Summary Row Above)

Sub CollapseRowsAbove()

ActiveSheet.Outline.SummaryRow = xlAbove

End Sub

Collapse Columns to the Right

Sub CollapseColumnsRight()

ActiveSheet.Outline.SummaryColumn = xlRight

End Sub

Collpase Columns to the Left

Sub CollapseColumnsLeft()

ActiveSheet.Outline.SummaryColumn = xlLeft

End Sub

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! 

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris :)

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Why Change Your Userform's Cursor?

There may be certain instances where it makes sense to change the look of the mouse cursor to notify your users of a particular action. A classic case for this is when you hover over a button. Typically, your mouse cursor will change from the pointer arrow to a pointer hand.

By default, Microsoft has picked a cursor type for each control available to add in a VBA userform. However, there are steps you can take to change the cursor image that appears while interacting with these controls. Here are a list of all the available Mouse Pointers you can use within your userforms

  • fmMousePointerDefault
  • fmMousePointerArrow
  • fmMousePointerCross 
  • fmMousePointerIBeam
  • fmMousePointerSizeNESW
  • fmMousePointerSizeNS
  • fmMousePointerSizeNWSE
  • fmMousePointerSizeWE
  • fmMousePointerUpArrow
  • fmMousePointerHourGlass
  • fmMousePointerNoDrop
  • fmMousePointerAppStarting
  • fmMousePointerHelp
  • fmMousePointerSizeAll
  • fmMousePointerCustom

Most of these should be self explanatory but if you are unsure you can always test them out to see how they appear.

You'll notice that one very important cursor image does not appear on this list and it is the Pointed Finger (aka Hand) icon shown in the image above with the green Accept buttons. I will discuss later on in this article how you can use the fmMousePointerCustom option to load a custom cursor icon. This will allow you to use any cursor icon you want.

How To Change the Mouse Pointer

Here are the basic steps to change which mouse pointer image displays while using a specific control in your VBA userforms.

  1. Ensure your userform is showing in the Visual Basic Editor (keyboard shortcut Alt + F11)
  2. Ensure the Properties Pane is visible (keyboard shortcut F4)
  3. Select the userform you wish to modify
  4. Within the Properties Pane, find the Property called MousePointer
  5. Select the mouse pointer image you would like shown when you hover over the control with your cursor (fmMousePointerDefault is the default option)
Using A Custom Mouse Pointer

I you do not find any of the built-in mouse pointers suitable for your userform you also have the option to upload your own cursor icon. The steps to do this are as follows:

  1. Ensure your userform is showing in the Visual Basic Editor (keyboard shortcut Alt + F11)
  2. Ensure the Properties Pane is visible (keyboard shortcut F4)
  3. Select the userform you wish to modify
  4. Within the Properties Pane, find the Property called MousePointer and change it to the fmMousePointerCustom option
  5. Also within the Properties Pane, find the Property called MouseIcon and click the little gray box with three black dots that appears on the far right of the property
  6. Find the icon image you would like to appear (file type must be .ico or .cur)
Where Do You Find The Hand Icon on The Web?

I had spent years trying to find a quality hand icon that would be compatible with Microsoft's userforms. I dug through forums, articles, and everything in between but could only find pixelated hand icons that did not look like the current native hand cursor the Windows OS typcially uses. 

About a year ago I finally found a file download that matched the quality I was looking for. I don't have the slightest clue where I found it but I made sure to save several copies so I would never have to find it again. With this article I also want to save you the pain of having to search for this gem.

Here is a direct link to download this cursor icon: Hand Cursor.ico

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! 

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris :)

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Why Manually Adjust Your Y-Axis?

By default, Excel will automatically decide the minimum and maximum values of your vertical (Y) axis. For the most part this will visually work for graphing your data, but there are certain instances where Excel's guessing may make your chart look weird or prevent it from telling the story you are trying to portray. Let's look at a couple of examples to show you what I mean.

The Waterfall Chart Example

Waterfall charts are used to show causes for the ups and downs (variances) between two data points. They are very good ways to quickly display root causes for changes in metrics and are used quite frequently in the corporate world. 

The two charts below are the same exact charts except the one on the left has the minimum value for the Y-Axis starting at $0 while the one on the right has the minimum value starting at $450. It should be pretty apparent that the chart on the right with the $450 Y-axis starting point is much more presentable than the one starting at $0.

Now most chart experts would argue that even though the chart on the right looks more appealing, it skews the perception on how big of an impact the $10, ($5), and $20 changes are in relation to the $500 starting point. And this brings us to a fork in the road where you will need to make a decision based on your audience. If you are going to be presenting a chart to an audience of finance-minded executives or managers, chances are they are going to know how big of a deal a $10 change in your sales is. However, if you were a journalist writing a piece on the economy for the general public you might not want to risk this distorted view as some in your target audience may not be able to process both the visual and the data to come to a conclusion.

In case you were wondering, these charts were created with my Waterfall Chart Creator add-in for Excel. If you like Waterfalls you'll love this add-in (there's even a free trial!).

If you wanted to play it safe, you might display your chart with an added metric to right size the data. I would add percent change to the graphic as shown below.

The Year-Over-Year Trends Example

There will be times as an analyst where you are not trying to create a pretty chart, you are just trying to simply "analyze" data! This can often times take the form of looking a seasonal trends over multiple years to ensure a forecast is directionally correct.

Let's take a look at the below two line charts that are analyzing crop yields (ie what percentage of your land created food you could sell). This might be useful if you are trying to determine if a 2016 forecast looks in line with historical trends.

The problem that you might pick up on while looking at the first chart is depending on your data, it may be very difficult to look at the changes for the trended years. By changing your Y-Axis Min/Max (effectively zooming in), you can clearly see in October your 2016 forecast might need a little adjusting.

Again, if you were to present this chart you would need to have confidence that your audience is savvy enough to read the scale and realize the axis starts at 70% instead of 0%.

To bring attention, you could try highlighting the 70% with a box.

How Do I Manually Adjust The Y-Axis Min/Max?

Here are the steps:

  1. Select the Y-Axis
  2. Right-Click the highlighted Y-Axis and select Format Axis... from the menu
  3. Make sure you are within the Axis Options section of the Format Axis Pane
  4. Within the Bounds section, manually input your Minimum and Maximum axis limits
Getting VBA To Do The Work For You

Adjusting your minimum and maximum bounds to your chart axis is pretty straight-forward, however it can get rather cumbersome if your data is frequently changing (think dashboard) and you are required to keep adjust your axis bounds to make the chart useful.

This is where VBA can be extremely useful! Below is a VBA macro I created that will go throw all the charts on your worksheet and adjust the y-axis according to the minimum and maximum values within the charts specific data. There is even an input to add a little extra padding to your axis so the axis' bounds is a bit under or over your min/max data points.

Sub AdjustVerticalAxis()
'PURPOSE: Adjust Y-Axis according to Min/Max of Chart Data
'SOURCE: www.TheSpreadsheetGuru.com

Dim cht As ChartObject
Dim srs As Series
Dim FirstTime  As Boolean
Dim MaxNumber As Double
Dim MinNumber As Double
Dim MaxChartNumber As Double
Dim MinChartNumber As Double
Dim Padding As Double

'Input Padding on Top of Min/Max Numbers (Percentage)
  Padding = 0.1  'Number between 0-1

'Optimize Code
  Application.ScreenUpdating = False
  
'Loop Through Each Chart On ActiveSheet
  For Each cht In ActiveSheet.ChartObjects
    
    'First Time Looking at This Chart?
      FirstTime = True
      
    'Determine Chart's Overall Max/Min From Connected Data Source
      For Each srs In cht.Chart.SeriesCollection
        'Determine Maximum value in Series
          MaxNumber = Application.WorksheetFunction.Max(srs.Values)
        
        'Store value if currently the overall Maximum Value
          If FirstTime = True Then
            MaxChartNumber = MaxNumber
          ElseIf MaxNumber > MaxChartNumber Then
            MaxChartNumber = MaxNumber
          End If
        
        'Determine Minimum value in Series (exclude zeroes)
          MinNumber = Application.WorksheetFunction.Min(srs.Values)
          
        'Store value if currently the overall Minimum Value
          If FirstTime = True Then
            MinChartNumber = MinNumber
          ElseIf MinNumber             MinChartNumber = MinNumber
          End If
        
        'First Time Looking at This Chart?
          FirstTime = False
      Next srs
      
    'Rescale Y-Axis
      cht.Chart.Axes(xlValue).MinimumScale = MinChartNumber * (1 - Padding)
      cht.Chart.Axes(xlValue).MaximumScale = MaxChartNumber * (1 + Padding)
  
  Next cht

'Optimize Code
  Application.ScreenUpdating = True

End Sub

I will typically link this macro up to a button on the spreadsheet or assign it to a spreadsheet event (such as "Before Double-Click" or "Change"). That way you can get all your charts adjusted quickly without having to manually go in and execute the macro every time.

Here is a simple example of how you might trigger the AdjustVerticalAxis macro above through the Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
'PURPOSE: Run AdjustVerticalAxis macro when Cell C2 is changed

If Target = Range("C2") Then Call AdjustVerticalAxis

End Sub

Get The VBA Example File

If you need a little head start or are not comfortable with VBA quite yet, I have put together a great little example Excel file with the VBA code covered in this article. The example workbook covers both a button triggered macro and a Event Triggered macro.

As always, in order to download this or any example file from this website, you will need to be a subscriber of my free tips newsletter.  If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

 

 Already Subscribed? Click HERE to log-in to the "Example Files" section

 How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! 

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris :)

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview