Loading...

Follow The Spreadsheet Guru on Feedspot

Continue with Google
Continue with Facebook
or

Valid

A couple months ago I received an email from Titus who was looking for a way to easily populate and update a PowerPoint quiz from Excel. This article will outline my suggested solution which does the following:

  1. Allows the user to store the Slide Number, Question, 4 Choices, and the correct Answer within an Excel table
  2. User can execute a VBA macro to populate slides based off a pre-created PowerPoint slide template
  3. The VBA macro will add a Change Color animation to highlight the correct answer during the presentation

To demo how to automate this process, I have gone ahead and created a quiz slide that looks like the below screenshot:

The following sections in this article will outline what needs to be done in Excel, PowerPoint, and VBA to get this quiz-generating process up and running. Enjoy!

Download The Example Files

If you need a little head start or are not comfortable with VBA quite yet, I have put together a sample Excel and PowerPoint file to show you how this process can be automated with VBA.

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

 Excel Setup

The Microsoft Excel interface I set up is very simple. The data is stored in an Excel Table Object named QuizTable. This table is where you can store the following:

  • Slide #: Which slide the corresponding row's question/choices need to be sent to
  • Question:  Your slide question
  • Choice A - D: The four choices your audience can choose from
  • Answer: Designate the correct answer (Data Validation used to restrict inputs to A, B, C, or D)

There is also a named cell called SlideTemplate (currently located in cell B2) where you can designate a slide that the VBA code will copy in the event there are more questions in the Excel table than the count of slides currently in your PowerPoint presentation.

Another named cell called SlideCount, is just a helper cell to simplify the VBA code and determine how many slides need to be in the presentation.

Finally, there is a great-looking button over to the right called Create Quiz! This is what you'll click to generate/update your PowerPoint presentation. Please note that the VBA is setup to target the Active PowerPoint presentation, so make sure you have the proper presentation currently activated before running.

PowerPoint Setup

In my demo file, I have two slides generated:  (1) a title slide and (2) a quiz template slide.

With the quiz template slide there was a little bit of setup needed before it was ready to interact with VBA. Once I had my desired layout created, I went ahead and named each of my shapes a specific name through the Selection Pane (Home Tab > Editing Group > Select > Selection Pane). As you can see below, each shape has a logical name that will be referenced in the VBA code we'll be going over later on. Having standardized names will allow us to map our data in the Excel table to a specific shape in our PowerPoint slide.

How The Quiz Will Function In PowerPoint?

The end result of this automated process will allow you to create or update the slides designated within the Excel table and also add a Change Color animation that reveals the correct answer to the quiz question during the presentation. The below screenshots demonstrate the below/after views in PowerPoint's Presentation Mode.

Let's Get Into The VBA

Alright, now we get into the fun stuff! I will go ahead and layout the full VBA code and then delve into some of the important sections of the code to provide you with some commentary and reasoning for why I wrote what I wrote.

PLEASE NOTE in order to keep the code moderate in length, I did not add error handling for every situation that could go wrong with this proposed setup. For example, if you decide to delete one of the Excel table columns, that is going to cause an issue in the VBA code. So keep in mind if you're wanting to make your process bullet-proof, you are going to want to add some more error handlers to the following code.

Sub CreateQuiz()
'PURPOSE: Create a Multiple Choice Quiz from an Excel Table To PowerPoint
'SOURCE: www.TheSpreadsheetGuru.com

Dim pwr_App As Object
Dim ppt As Object
Dim ppt_shp As Object
Dim ppt_eff As Object
Dim SlideTemplate As Long
Dim SlideCount As Long
Dim QuizTable As ListObject
Dim Choice_A As String, Choice_B As String
Dim Choice_C As String, Choice_D As String
Dim rw As Range

'INPUTS
  Set QuizTable = ActiveSheet.ListObjects("QuizTable")
  SlideCount = ActiveSheet.Range("SlideCount").Value
  SlideTemplate = ActiveSheet.Range("SlideTemplate").Value

'Set variable equal to active presentation
  On Error GoTo NoPresentationFound
    Set pwr_App = GetObject(class:="PowerPoint.Application")
    Set ppt = pwr_App.ActivePresentation
  On Error GoTo 0
  
'Add Slides to presentation (If needed)
  For x = 1 To SlideCount - ppt.Slides.Count
    ppt.Slides(SlideTemplate).Copy
    DoEvents 'Make sure copy completes before moving on
    ppt.Slides.Paste
    DoEvents 'Make sure paste completes before moving on
  Next x

'Loop through each row in Quiz Tabl
  For Each rw In QuizTable.DataBodyRange.Columns(1).Cells
  
    'Store Row Inputs
      SlideNumber = rw.Value
      Question = rw.Offset(0, 1).Value
      Choice_A = "A. " & rw.Offset(0, 2).Value
      Choice_B = "B. " & rw.Offset(0, 3).Value
      Choice_C = "C. " & rw.Offset(0, 4).Value
      Choice_D = "D. " & rw.Offset(0, 5).Value
      Answer = rw.Offset(0, 6).Value
  
    'Transfer Inputs into PowerPoint Slide
      With ppt.Slides(SlideNumber)
      
        'Change Text on the Slide
          .Shapes("Question").TextFrame.TextRange.Text = Question
          .Shapes("Choice_A").TextFrame.TextRange.Text = Choice_A
          .Shapes("Choice_B").TextFrame.TextRange.Text = Choice_B
          .Shapes("Choice_C").TextFrame.TextRange.Text = Choice_C
          .Shapes("Choice_D").TextFrame.TextRange.Text = Choice_D
      
        'Ensure Any Old Choice Shape Animations are removed
          .Shapes("Choice_A").AnimationSettings.Animate = msoFalse
          .Shapes("Choice_B").AnimationSettings.Animate = msoFalse
          .Shapes("Choice_C").AnimationSettings.Animate = msoFalse
          .Shapes("Choice_D").AnimationSettings.Animate = msoFalse
      
        'Store Slide's "Correct Answer" Shape to a variable
          Set ppt_shp = .Shapes("Choice_" & Answer)
          
        'Add ChangeFillColor Animation to "Correct Answer" Shape (use enumeration value!)
          Set ppt_eff = .TimeLine.MainSequence.AddEffect( _
            Shape:=ppt_shp, effectid:=54) '54 = msoAnimEffectChangeFillColor
            
        'Set Color to Change "Correct Answer" shape to
          ppt_eff.EffectParameters.Color2.RGB = RGB(112, 173, 71)
        
        'Set color fade-in time
          ppt_eff.Timing.Duration = 0.4
          
      End With
  
  Next rw

'Completion Notification
  MsgBox "Your Quiz has successfully been updated!"

Exit Sub

'ERROR HANDLERS
NoPresentationFound:
  MsgBox "Could not find a PowerPoint presentation that was open", _
    16, "No Active Presentation"
  Exit Sub
  
End Sub

Determine Your Target PowerPoint file

Since we are running all the VBA code from Excel, we are going to need to reach out and determine which PowerPoint file we want to manipulate. The following snippet of VBA code creates a variable that points to the PowerPoint application itself (ie pwr_App) and then creates a variable that points to the Active PowerPoint presentation (ie ppt).

If an error is thrown, either your user does not have PowerPoint installed on the computer or your user doesn't currently have the PowerPoint application open.

These variables are extremely important as they will allow us to control both Excel and PowerPoint at the same time!

'Set variable equal to active presentation
  On Error GoTo NoPresentationFound
    Set pwr_App = GetObject(class:="PowerPoint.Application")
    Set ppt = pwr_App.ActivePresentation
  On Error GoTo 0

Do You Need To Add More Slides?

If you add more questions to your Excel table, you don't want to go through the hassle of going into PowerPoint and creating more slides before running your code. This section of code cross-references the amount of slides currently in your PowerPoint presentation with the highest slide number referenced in your Excel table. The loop will go through and copy/paste your designated template slide (slide number input into cell B2 of your Excel file) until enough slides have been added.

You may be wondering why I didn't account for the reverse effect....deleting slides if there are too many. I decided not to include this in the event you have other slides included in your presentation that are not part of your quiz.  For example, maybe you are running a department meeting and various people are speaking on different topics and you are starting the meeting off with a few quiz questions. You definitely wouldn't want your VBA code to go in and delete all the slides after your quiz!

'Add Slides to presentation (If needed)
  For x = 1 To SlideCount - ppt.Slides.Count
    ppt.Slides(SlideTemplate).Copy
    DoEvents 'Make sure copy completes before moving on
    ppt.Slides.Paste
    DoEvents 'Make sure paste completes before moving on
  Next x

Navigating The Excel Table and Sending Text To PowerPoint

In the below snippet, the VBA code will cycle through each row of the Excel table and store all the values within the table row to variables. Once we have this information stored, the VBA code easily sends it to the specific shape names in PowerPoint that were designated towards the beginning of this article.

There is also a section that removes any animation that may have been left over from a prior execution of this code. That way our slides are starting off fresh and there is no risk of having two correct answers animated during your presentation.

'Loop through each row in Quiz Tabl
  For Each rw In QuizTable.DataBodyRange.Columns(1).Cells
  
    'Store Row Inputs
      SlideNumber = rw.Value
      Question = rw.Offset(0, 1).Value
      Choice_A = "A. " & rw.Offset(0, 2).Value
      Choice_B = "B. " & rw.Offset(0, 3).Value
      Choice_C = "C. " & rw.Offset(0, 4).Value
      Choice_D = "D. " & rw.Offset(0, 5).Value
      Answer = rw.Offset(0, 6).Value
  
    'Transfer Inputs into PowerPoint Slide
      With ppt.Slides(SlideNumber)
      
        'Change Text on the Slide
          .Shapes("Question").TextFrame.TextRange.Text = Question
          .Shapes("Choice_A").TextFrame.TextRange.Text = Choice_A
          .Shapes("Choice_B").TextFrame.TextRange.Text = Choice_B
          .Shapes("Choice_C").TextFrame.TextRange.Text = Choice_C
          .Shapes("Choice_D").TextFrame.TextRange.Text = Choice_D
      
        'Ensure Any Old Choice Shape Animations are removed
          .Shapes("Choice_A").AnimationSettings.Animate = msoFalse
          .Shapes("Choice_B").AnimationSettings.Animate = msoFalse
          .Shapes("Choice_C").AnimationSettings.Animate = msoFalse
          .Shapes("Choice_D").AnimationSettings.Animate = msoFalse
      End With
  
  Next rw

Applying the Correct Answer Animation

Finally, we are going to single out the answer to the question and apply an animation that will change the correct choice's color to green.

First, the code will point a variable to the shape housing the answer by reading the answer from the Excel table and placing the phrase "Choice_" in front of it. This combination will match one of our PowerPoint shape names.

Next, the VBA code will add a specific animation to the shape (in this case I chose the ChangeFillColor effect). It is important to use the enumeration (numeric) value instead of the textual name for the effect since our VBA is in Excel and not PowerPoint. For a list of all the possible animations you can add, check out Microsoft's list of msoAnimEffect names.

Finally, I threw in some code to determine the animation color (in my example green) using the RGB color code. I also tweaked the length of the color fading between the current color and my animation color to 0.4 seconds (I didn't like the default transition length).

With ppt.Slides(SlideNumber)

  'Store Slide's "Correct Answer" Shape to a variable
    Set ppt_shp = .Shapes("Choice_" & Answer)
    
  'Add ChangeFillColor Animation to "Correct Answer" Shape (use enumeration value!)
    Set ppt_eff = .TimeLine.MainSequence.AddEffect( _
      Shape:=ppt_shp, effectid:=54) '54 = msoAnimEffectChangeFillColor
      
  'Set Color to Change "Correct Answer" shape to
    ppt_eff.EffectParameters.Color2.RGB = RGB(112, 173, 71)
  
  'Set color fade-in time
    ppt_eff.Timing.Duration = 0.4
    
End With

You're All Set!

That covers all the basic steps to get the process of creating/updating PowerPoint slides for your mulitple-choice quiz. I hope you were able to follow along and understand all the necessary steps of the process. Be sure to download the example files I used in this article to save yourself some time and effort (see the big green button towards the beginning of the article).

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 

I am a HUGE advocate of not merging cells as they can cause all sorts of headaches while copy/pasting and running VBA code. I even went as far as to develop a free add-in to add the better solution (Centering Across) to the Excel Ribbon. However, I did run into a scenario where it made sense to take on the potential risks and merge some cells.

One Problem With Centering Across

While dealing with a workbook that was setup similar to the below screenshot, to save space I decided to collapse my monthly columns and just show my Quarterly/Yearly figures (that's all that matters in Corporate America anyways, am I right?!).

But when I collapsed my columns, my header text disappeared! Now for a while I was extremely confused as to why this was occurring, it just didn't make sense to me....

I later found out that my header text was disappearing due to the fact that I was using the Center Across alignment format instead of the Merge & Center format.

When I went ahead and merged the header, collapsing the columns did not force the text to disappear. 

Here was the final setup for my data and as you can see is much easier to see all the different sections without scrolling to the right.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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 

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