Tag Archives: Tips

How to Remove Duplicates in Excel: An Easy Guide

In this article, I will show you how to remove duplicates in Excel. While having duplicate data can be useful sometimes, it can also make it more difficult to understand your data. I’ll use conditional formatting to find and highlight duplicate portions of data within Microsoft Excel. Review your duplicate content and decide if you want to remove them.

Remember that when you delete duplicate values, the duplicate data is permanently deleted from your records. Before you go forward with deleting the duplicate content, I highly recommend that you copy the original data over to another worksheet. This ensures that you will not accidentally lose any of your vital information and hard work.

I use an iPhone app to record my food intake and exercise output every day, and also record my weight, albeit infrequently. All of this data is synced to a database in the cloud. The database automatically enters my weight each day, even though I don’t, by using the last known data point. All of this data can be downloaded in a CSV file, opened with Excel, and saved as a workbook file.

Charting my weight from this data is a simple matter, but Excel doesn’t need all of the extra data points. Consequently, I have reason to use the Remove Duplicates feature that was introduced in Excel 2007.

Using Dates with the Remove Duplicates Feature

I have two columns of data with Date in column A and Weight in column B. I want to remove all duplicate Weights but have to be careful because it makes a difference how the dates are sorted.

The file downloaded from the database was sorted in descending order by Date. When using Remove Duplicates I got a different result when the Dates were sorted in ascending order. In each case, the same number of unique Weight values were found but associated with different Dates.

How to Delete Duplicates in Excel

Understand how to delete duplicates in Excel: Apparently, the Remove Duplicates works from the top down so sorting dates in ascending order make sense. Keep that in mind when Date values are part of your data set.

Here is an example of the raw data, on the left, and the results from using Remove Duplicates when the data was sorted in descending versus ascending order.

Remove Duplicates in Excel

Steps to Removing Duplicate Data in Excel

Select the data range or make sure the active cell is inside the data range you want to manipulate. Excel is smart enough to pick out the region of data and figure out if there are column headers.

First thing, make sure the data is sorted. I selected cell B2 and sorted the range in ascending order so the first unique Weight value would correspond to the First Date, and not the last.

  • Select Data tab » Remove Duplicates, which will bring up a dialog box.

Remove Duplicates Dialog Box

  • Select the column(s) that have duplicate data
  • Check an see if the My data has headers box is checked (assuming you have column headers)
  • Click OK and you will eliminate duplicates in Excel

A popup box will confirm the number of duplicate and unique values

Remove Duplicates Confirmation Popup

If you’re not satisfied with the result, use the keyboard shortcut Ctrl+Z to undo the Remove Duplicates action.

Image that shows how to freeze cells in Excel.

How to Freeze Cells in Excel So Rows and Columns Stay Visible

Image that shows how to freeze cells in Excel.

Have you ever worked in an unorganized spreadsheet? We have to admit, there is nothing more frustrating. When you scrolled down the endless rows, chances are, you couldn’t see your headers anymore. How are you supposed to keep track of where you are plotting data? This is where knowing how to freeze cells in Excel comes in handy.

If you have spent time working in a large worksheet, you may have wondered if there is a way to keep your rows and columns visible. This way, you can keep specific information visible when scrolling down or across. It is time-consuming and cumbersome to navigate back and forth to compare the top of your worksheet to the bottom. Luckily, Excel has a few built-in features to maximize your workflow efficiency.

Sometimes you may want to keep specific information visible while scrolling through your spreadsheet. In this case, Excel’s “Freeze Panes” feature is useful. In this article, we will show you all the ways to freeze the cells and how to do so where rows and columns stay visible. Here are a few tips to keep in mind when learning how to freeze cells in Excel.

  • Excel can only freeze panes from the top down and left to right.
  • Panes cannot be frozen while in “Edit” mode.
  • Frozen Panes can easily be unfrozen.

When and Why You Should Learn How to Freeze Cells in Excel

As mentioned earlier, unnecessary scrolling in Excel is a waste of time. It takes longer than you might think just to scroll up and peek at your category labels. It’s essential to minimize wasted time so you can get your work done efficiently.

In many cases, your spreadsheet will fill up more than what is visible on your screen. Any given worksheet contains a maximum of 1,048,576 rows and 16,384 columns. Thankfully, most spreadsheets will not meet their maximum capacity.

If you are working with several categories, it is helpful to keep your headers on screen at all times. Hence the need to learn how to freeze cells in Excel.

How to Freeze Cells in Excel

Although it’s not obvious at first glance, learning how to freeze cells in Excel is a simple process. While this feature is quite useful, it does have its limitations and can be a bit finicky at times. Below you will find out how to freeze panes and some common pitfalls to avoid.

Freezing the Top Row

Let’s start with freezing the top row of a worksheet. Freezing the top row is probably the most common use of the “Freeze Panes” feature. Freezing the top row of a spreadsheet allows you to keep your headings in place so that you can see them while scrolling.

To begin, select the “View” tab on the Ribbon at the top of your page. In this tab, you can check to ensure that your worksheet is in the “Normal” view and also select your desired “Freeze Panes” options. The “Freeze Panes” tool will not be available if your worksheet is not in “Normal” mode.

With your worksheet in “Normal” view, click the drop-down arrow on the “Freeze Panes” icon. You can then select “Freeze Top Row” from here. The top visible row of your worksheet will now be locked in place at the top of your spreadsheet.

Please note that the “Freeze Top Row” option freezes the top visible row of the current spreadsheet. If your worksheet was not scrolled all the way up, you might have frozen your spreadsheet farther down from the actual top. This feature is useful if you want to compare a row in the middle of your spreadsheet to one at the bottom.

If you freeze a “top” row that is not the first row of the worksheet, Excel will hide all rows above the frozen row until you unfreeze your “top” row.

Freezing the Leftmost Column

Let’s say that you’re working in a large worksheet with a list of dates on the leftmost side of the page and multiple categories across the sheet. If you want to keep your dates visible while exploring all of your categories, you will need to freeze the first column of your spreadsheet.

Freezing the first column of your worksheet is precisely like freezing the top row. The only difference is that between the “Freeze Panes” drop-down menu you will select “Freeze First Column.”

Keep in mind Excel defines the “first” column as the leftmost visible column on the screen. If you want that to be your very first column, you must have your worksheet scrolled entirely to the left when selecting this option. Otherwise, you can use this feature to freeze a column from the middle of your spreadsheet to compare it to the right side.

Just like when freezing the “top” row of a worksheet, freezing a column from the middle of the spreadsheet will hide all of the columns to the left until you unfreeze your “first” column.

More Options for How to Freeze Cells in Excel

It is possible to freeze more than one row or column at the same time. Additionally, you can freeze both rows and columns simultaneously. These features can be useful if you need to compare more than one row or column to other rows or columns that are too far separated to see.

To freeze more than one row, select the row below the last row you want to freeze. Navigate to the “Freeze Panes” drop-down menu and select “Freeze Panes.” Excel will freeze all of the rows above your selected row.

Freezing multiple columns is very similar. Highlight the column to the right of the last column you want to freeze. Navigate to and select the “Freeze Panes” option. This freezes all the columns to the left of your selected row.

To freeze rows and columns at the same time, select the cell that is under the last row you want to freeze and to the right of the last column you want to freeze. Navigate to the “Freeze Panes” option and select it by clicking.

To unfreeze rows or columns, select “Unfreeze Panes” from the “Freeze Panes” drop-down menu.

Despite its limited functionality, the “Freeze Panes” feature is handy for working with large worksheets. Knowing how to freeze cells in Excel may take a little practice to master. Have patience while learning the valuable skill and you will reap the benefits of a much more efficient workflow. Excel is a robust program with solutions for nearly every problem you will encounter.

Here’s a Shortcut to Freeze Cells in Excel

Did you know that there are handy keyboard shortcuts for Microsoft Excel programs? Using these shortcuts not only saves you work time but also from pesky, repeated actions. The main reason why people use shortcuts for Excel is that they do not have to take their hands off the keyboard to use a computer mouse and visually search for both menus and buttons. For example, let’s say that you want to add a new workbook. Without using a keyboard shortcut, you would have to click the “office button,” select “new,” and then double click the “blank workbook” icon. Did you know that you can just click Cntrl + N (The control key clicked with the button for n)? The following abbreviations will make your work life a whole lot easier. 

  • Shift + Space: This shortcut will select a row. 
  • Ctrl + C: Use this shortcut to perform the copy action.
  • Shift + Ctrl + Arrow Down: With this combination, you can select all the way down to the bottom of a region within Excel. 

How to Freeze Cells: Final Review

That is it. You see, learning how to freeze cells in Excel is not as daunting as it may have seemed. With these tools and handy shortcuts under your belt, you are well on your way to workplace efficiency. 

how to use goal seek in excel

How to Use Goal Seek in Excel

Excel has proven itself to be very useful in various situations over and over again. The list of Excel’s benefits seems to be never-ending.

It even has a tool for answering questions and forecasting information. The Goal Seek function in Excel is a great tool for those asking “What if” type questions.

Use this guide to learn how to use goal seek in excel as well as how to put it into action.

Why Use Goal Seek?

why_use_goal_seek_

 

The Goal Seek feature in Excel is basically used to create formulas. It provides information on cause and effect situations.

It determines what specific data will impact another set of data. This feature comes in handy in lots of scenarios.

In financial and sales situations, it can be used to determine what must be accomplished in order to reach a certain monetary goal.

It can also be used to calculate how many votes a candidate needs to win an election.

Once you have your Goal Seek formula in place, you can change any part of it to see how the formula as a whole change.

For example, a computer sales company has a profit goal of $3 million for the year.

Each of their computer systems cost $200, while additional accessories like keyboards cost $50 extra.

This company could use how to use goal seek in excel to determine how many of each product they’ll need to sell to reach their goal.

Here’s How To Use Goal Seek In Excel

To use Goal Seek, you will have to open a new sheet and enter the current information you have.

To make learning Goal Seek a little easier, we are going to use a simpler example.

Sample Scenario

how to use goal seek in excel

How To Start

To start, enter your information and functions into a spreadsheet in an organized table.

For our data, we’ll have the headings Playground, Movie, and Total in the first column.

Across the top, we’ll have the headings Votes and %. We have set up our table to calculate percentages base on the votes.

The goal will be to determine how many votes will it take to choose the Playground option.

Next Move

The next thing we need to do is select the cell we want to change. We want to make the Playground vote equal 66%, or 2/3, so we will select that cell.

Under the Data tab, locate the “What If Analysis” button. Click it to reveal the drop-down menu.

On this menu, you will see “Goal Seek.” Select this option, and a dialogue box will appear.

The first option you will see will ask you to set your cell. In our case, the percentage of Playground votes cell is D2, so we will enter that into the box.

Next, you will see “To value.” This is where you enter the desired goal. Our goal is 66%, so we will enter that into this box.

Final Steps

Finally, the last box will say “By changing cell.” This will be whichever cell you want to change in order to get your result.

We want to know how many votes will get us to 66%, so we want to change the number of playground votes. In our case, that is cell C2.

After you input all of your information, hit OK. Your information on your table will change to accommodate the goal number you have set.

In our case, we change the playground percentage to 66% based on the number of votes it needs.

When we hit OK, the numbers on our chart revealed that 24 out of the class’s 37 students would have to vote for the playground.

That is for them to reach 66% and establish a fair vote.

The Goal Seek function also shows us that for this to be true, 13 students will have voted for the movie.

Conclusion

 

how to use goal seek in excel

The Goal Seek function can be a very useful tool when trying to forecast sales, finances, votes, etc.

Numbers can be tricky to determine, and in important situations, it can be crucial to making decisions based off of accurate information.

The Goal Seek serves to perform exactly that.

Upon first use, the function can prove to be a little tricky. An important thing to remember is to make sure all of the functions in your table make sense.

The Goal Seek feature will not work properly if your functions don’t add up.

You may also get some funky numbers that don’t make sense if your functions don’t make sense.

It is a good idea to practice using this method a few times before you start using it for important projects.

Once you have the feature mastered, Goal Seek is an excellent tool for both professional and educational use.

Read through this guide on how to use goal seek in excel and follow the steps a few times to really nail down the skill.

This may come as a surprise to how useful it becomes. As a matter of fact, your business or job will be a lot easier.

Soon you’ll be forecasting information for all sorts of projects!

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

How to Add Cells in Excel to Sum Up Totals Automatically

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=SUM(B2:H2)

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

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

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

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

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

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

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

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

Conclusion

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

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

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

The 24-Hour Rule

A long time ago I was doing some reading on how you write and publish blog posts. One suggestion that helps me is the 24-hour rule. And that is to finish your post and then wait 24 hours before reading it one-last-time. After all, a blog post is going to be public, out there on the internet with your name on it, for a very long time.

The problem with this 24-hour rule is that I usually find one or more things that need to be changed. And then I have to wait another 24-hours.

When you are on a deadline, like, for instance, having a self-imposed quota of 2 or 3 blog posts a week, the 24-hour rule is inconvenient. It drives you to compromise on the level of quality.

By that I mean that there is a hierarchy of quality levels starting with spelling and grammar and ending with saying what you intended in the most concise, articulate manner possible. A deadline drives you to ensure the spelling and grammar are correct and then you compromise on just how well you get your point across.

Tech Man

For me the deadline and quality levels force a certain tension, all of which drove me to abandon my blogging of all things Excel. Yet since a deadline and quality levels are self-imposed, they are also controllable.

I’ve decided to stick with the 24-hour rule and forget about the deadline, or any deadline for that matter. Consequently my blogging will continue, at my own pace.

Right now I’m working on a post about the Excel iPad app.

So the 24-hour rule stays and the journey continues.

Where is Control+Home for Excel on a Mac

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

Finding My Way Home

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

Excel home key on mac

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

Keyboard Viewer Mac

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

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

Excel Control+Home Key on Mac

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

fn+Command+Left Arrow

fn+Control+Left Arrow

Another mystery solved.

Keyboard Shortcuts

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

Copy Symbol Image

Add Macro Button to the Toolbar in Excel 2011

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

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

Next you:

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

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

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

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

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

  • Copy Symbol Image

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

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

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

    Control + Home in Excel for Mac

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

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

    My Journey

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

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

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

    The Control+Home Macro

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


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

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

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

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

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

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

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

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

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

    Reserved Shortcut

    So I chose g instead and clicked OK.

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

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

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

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

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

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

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

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

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

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

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

    Break Even Calculation International Phone

    Break Even Calculation with an Unlocked iPhone and International Rates

    iPhone 4 PhotoI just upgraded my wife to a new iPhone 4S and since she’s finished with her contract, AT&T will now unlock her old iPhone 4.

    Having an unlocked phone is advantageous when traveling overseas because you can pick up a Sim card with a phone plan and save some money. The question I want to answer here is, “Is it worth it?”

    Phone Plans

    I’ve spent time in the UK and the best place to get a Sim card or even buy an inexpensive mobile phone is with O2. Great coverage, products, service, and you can find them practically everywhere. Just what you need when “on Holiday” and are looking for a mobile phone plan.

    With and unlocked iPhone you can pick up a Sim at O2 for £13.50 that gives you 100 minutes of talk, unlimited text, and 100MB of data. My phone plan with AT&T includes international roaming, which is free, but the international roaming rate in the UK is $1.39 per minute. Ouch!

    The Conversion

    The problem is that I need to convert British Pounds to American Dollars so I can make the comparison. You can find this information online with a search engine (Google, Bing, Yahoo). It would be nice if Microsoft would include currency conversion in the CONVERT function, but I digress.

    The Calculation

    Break Even Calculation International PhoneGiven a Sim only plan of £13.50, and a conversion rate of 1.6123 dollars per pound, my cost is $21.77 USD. That works out to $0.22 per minute, verses the $1.39 that AT&T will charge me while in the UK.

    If I divide my cost in dollars, by the AT&T international rate in the UK, I can talk roughly 15 minutes on my AT&T plan before it becomes cost effective to purchase a Sim card.

    The Smart Move

    With a smart phone, data is king. So while it’s nice to consider the break even point for talk time on an international plan, the bottom line is that with 100MB of data in the aforementioned Sim plan on O2, there is no comparison.

    When going on Holiday or spending time in the UK, pick up a Sim card and stick it in your unlocked iPhone. It’ll be the best spent money on the trip.

    vlookup shark

    The VLOOKUP Function – Inside Out

    vlookup sharkAs part of Shark Week I’ve committed to write something for VLOOKUP week. (It’s what I get for using twitter.) So without further ado.

    I love the VLOOKUP Function in Excel. As the name implies, it’s a vertical lookup. Meaning the function will lookup data in columns.

    The VLOOKUP Function Arguments

    The VLOOKUP function has four arguments and in my opinion the fourth argument always gets overlooked, yet it’s the first thing you need to know. So, like reverse polish notation, we’ll start from the inside and work out to explain each argument.

    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    range_lookup

    The range lookup argument it either TRUE or FALSE. I use FALSE 98% of the time, because using FALSE means the VLOOKUP function will find an exact match. If no match is found then it returns the #N/A error value. Note: And by exact, they mean EXACT. An extra space character, which is not visible to the naked eye, will cause and error.

    The strange thing is that you don’t even need the fourth argument, but if it’s missing the default value is TRUE. Bad choice by my estimation, but that because I rarely use TRUE. If the range lookup value is TRUE then the VLOOKP function will produce an approximate match.

    This can be quite handy if you want to return something like grades, you know: A, B, C, D, F, when given a numerical value between 0 and 100. Teachers must love TRUE. The only catch here is that the first column of the lookup table has to be sorted in ascending order. You can find out more here.

    col_index_num

    This argument is just the column number from the table you are looking up. However, this column will contain the data you want the VLOOKUP function to return. For example, I have a table with Names in the first column and Cites in the second column. I want the VLOOKUP function to return the City value so the index number is 2, for the second column in the table.

    table_array

    The table is where VLOOKUP gets its information. This is where the data is looked up. The reference to the table may take several forms. You normally use an absolute range reference, like $A$1:$B$5. In Excel 2003 I like to use a defined Range Name. In newer versions of Excel I use a TABLE to store the information, and hence the Table Name is what I use for the second argument.

    lookup_value

    We finally come to the lookup_value. This is a single reference the VLOOKUP function uses to find a match in the first row of the Table. For example, if I want to lookup a Name and find the corresponding City, the lookup_value should reference a name and the first column of the table should be a column of names.

    VLOOKUP Function in Action

    In the screen shot below you can see the VLOOKUP function shown in the formula bar, which is for cell B2.

    =VLOOKUP(A2,myTable,2,FALSE)

    I’m using FALSE in the fourth argument for an exact match. For the third argument, I want to return data from column 2 of the Table, which is for the City. For the second argument, the reference is the Table name myTable, which is the range D1:E5. Finally, the the first argument, A2 is a reference to a name.

    VLOOKUP Function Inside Out

    So the VLOOKUP function in cell B2, looks in cell A2 and finds a name (Ted), then goes to the table, myTable, and locates an exact match in the first column (row 3), then goes over to the second column and returns that value (Bryan) to cell B2.

    Note: The myTable reference refers to the range D2:E5, and doesn’t include the header row.

    Analysis ToolPak AddIn

    Check the Analysis ToolPak Add-In in Excel 2003 [VBA Code]

    Analysis ToolPak AddInI recently began using the NETWORKDAYS function in Excel 2007, yet my file was going to be widely used by people with Excel 2003 (both are Windows versions). The NETWORKDAYS function is a standard function in Excel 2007, but with 2003 you have to install the Analysis ToolPak, otherwise you get an error.

    The only way for me to avert an error with users who don’t have the Analysis TookPak installed in Excel 2003 is to use some VBA code that runs when the file is opened. I want to see if they are using a version of Excel older than 2007, check if they have the Analysis ToolPak installed, and then tell them, with a pop-up message, to install the Analysis ToolPak, so all the formulas will work correctly.

    Create a Routine to Check for the Analysis ToolPak

    Open the Visual Basic Editor (Alt+F11) then choose Insert > Module and enter the following VBA code.


    Sub CheckToolPak()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Checks to see if the Excel version comes before Excel 2007,
    ' if so then checks to see if the Analysis ToolPak is installed.
    ' If not, then notify the user and end the program.
    '
    ' This is required because of the NETWORKDAYS formula
    ' that is used in the charts.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If CInt(Application.Version) < 12 Then If AddIns("Analysis ToolPak").Installed <> True Then
    MsgBox "Please install the Analysis ToolPak." & vbCr & vbCr & _
    "Choose Tools > Add-Ins... " & vbCr & _
    "then check the box for Analyiss ToolPak, and click OK."
    End
    End If
    End If
    End Sub

    The code is somewhat self-explainatory except that Application.Version returns a text value, hence you see the CInt function that converts that text value to an integer so that we can tell if the Excel version is less than 12, which is the version number of Excel 2007.

    Create a Workbook Open Event

    This will do the trick, but needs a trigger to call the routine. So double-click ThisWorkbook in the Project Explorer of the VBA Editor, then select the drop-down at the top-left of the window – where you see (General) – and select Workbook. The Private Sub Workbook_Open() subroutine will appear with no code.

    Type in Call CheckToolPak and then save the file.


    Private Sub Workbook_Open()
    Call CheckToolPak
    End Sub

    Now when you open the file with Excel 2003, and don’t happen to have the Analysis TookPak installed, you get the following message.

    Analysis ToolPak PopUp

    If users have the Analysis ToolPak Add-In installed in Excel 2003 there is no message, nor if the user is using Excel 2007, 2010 or 2011.

    Select a Column of Non-Sequential Data

    Non-Sequential Data

    In one of my posts on using VBA to select a column of data, I received a comment asking if the macro could be modified to select data like the screen shot you see to the left.

    This data is non-sequential and consequently the CurrentRegion property won’t work. The way around this is to select the very last row in the same column, then shoot up (Ctrl + Up Arrow) to find the last data cell.

    Once you know the where the column heading and last data cell is in the current column, the range can then be selected.

    The following macro will select the column of data if you start with the active cell at the column heading.


    Sub SelectOneColumnData()
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' This routine will select a non-continuous column of data
    ' when active cell is located in the column heading.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim ac As Range
    Dim lRow As Long
    Dim lc As Range
    Dim col As Integer
    Dim cr As Range

    Set ac = ActiveCell
    col = ac.Column
    lRow = ActiveSheet.Rows.Count
    Set lc = Cells(lRow, col)

    ' Find the bottom of the range then re-set the last cell range
    Set lc = lc.End(xlUp)
    lRow = lc.Row

    ' Set the current range from the active cell to the last row
    ' in the column with data
    Set cr = ac.Offset(1, 0).Resize(lc.Row - ac.Row, 1)
    cr.Select
    End Sub

    Zoom to 125 Percent – Excel 2011 Default Workbook

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

    The buried location is:

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

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

    Excel 2011 Preferences General

    Change the Default Workbook in Excel 2011

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

    Excel 2011 Save As Template

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

    The Default Workbook Secret

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

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

    Remove .XLTX Extension

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

    One More Thing – New Worksheets

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

    My Summary

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

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

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

    Settings and Shortcuts for Excel 2003

    I recently found myself with a new, loaner laptop and a brand new copy of Excel 2003 as my default spreadsheet program. This was fine until I realized there were a number of things “missing.” Like shortcuts and settings that I’ve changed over the years to make Excel serve me, instead of the other way around.

    So here’s my list of things I do to “normalize” Excel 2003.

    Full Menu’s

    My number one pet peeve with Excel is they “automatically customize menus and toolbars based on how often you use the commands.” But my question is, “How do new users know what menu commands are available if they’re hidden?”

    I like to use “full menus” so you see ALL menu commands each time each time you click a menu. This is a standardized approach; you see the same thing each time you click a menu.

    Here’s how it’s done:

      Right click the toolbar
      Click Customize…
      Click the Options tab on the Customize dialog box
      Check Always show full menus
      Click Close

    Customize Dialog Box

    General Options

    I have only two recommendations here: maximizing the recently used file list and minimizing the number of worksheets in a new file.

    General Options Settings

    Recently used file list (9)

    I like to set the recently used file list to the maximum number. In Excel 2003 that number is nine. This saves you time when searching for a recently used file.

    Here’s how it’s done:

      Choose Tools > Options then click the General tab on the Options dialog box. Change the Recently used file list to 9 and make sure there’s a check in the check-box.

    Sheets in new workbook (1)

    When creating a new spreadsheet file, how many sheets do you actually use? How many times have you looked over a spreadsheet file from someone else and clicked on those bank sheets to see if they contained anything?

    Remember the days when a new file had 16 worksheets as the default? Excel 2003 has just three. That’s two to many. I like to set the number of worksheets in a new file to one. If I need another, they’re easily created.

    Here’s how it’s done:

      Choose Tools > Options then click the General tab on the Options dialog box. Change the Sheets in new workbookto 1.

    Shortcuts on the Toolbar

    These custom toolbar buttons are necessary when using Excel 2003: Paste Values, Freeze Panes, Current Region, Auto Filter, and Pivot Table. You can easily customize the toolbar to add these and more.

    My Custom Toolbar Icons

    For each addition to the toolbar you’ll need to access the Customize dialog box. The long way is to choose View > Toolbars > Customize… or the short way is to right-click a toolbar and select Customize… from the pop-up menu.

    Once the Customize dialog box is open, select the Commands tab. Now your ready. Here’s my favorite custom toolbar commands.

    Paste Values

    The very first custom toolbar command icon I put up. Indispensable. Well, almost.

    Here’s how it’s done:

      In the Categories pane select Edit. In the Commands pane scroll down and find Paste Values. Click and hold the left-mouse button down while dragging the icon to a toolbar.

    Paste Values Command

    Freeze Panes

    Another indispensable toolbar command button shortcut icon for anyone who works with lots of data in proper tables.

    Here’s how it’s done:

      In the Categories pane select Window and Help. Scroll down until you find Freeze Panes. Click with left mouse button, hold and drag to the toolbar.

    Select Current Region

    Here’s a neat button that allows you to find the shape of a data region by selecting it for you automatically. Better than the keyboard shortcut Ctrl+Shift+asterisk (*). Reminds me of the CurrentRegion property in VBA.

    Here’s how it’s done:

      In the Categories pane select Edit. In the Commands pane scroll down to the bottom and find Select Current Region. Click and hold with the right-mouse button while dragging to a toolbar of your choice.

    AutoFilter

    A great button to save you some time when filtering tables. If the active cell is on the header row it simply turns on the filter. However, if you select a cell in the data that contains something you want to filter, clicking the AutoFilter will turn on AND filter that selection for you. A one step process that saves time.

    Here’s how it’s done:

      Select Data from the Categories pane. Click the AutoFilter command with the left mouse button, hold and drag to a toolbar.

    PivotTable

    This command serves to initiate a PivotTable from a data table. It also brings up the PivotTable and PiotChart Wizard when you are working on an active PivotTable.

    Here’s how it’s done:

    Select Data from the Categories pane. Scroll down to the bottom of the Commands pane and find PivotTable and PivotChart Report icon. Left-click, hold and drag to a toolbar.

    More Stuff

    Find your favorite command not shown on a Toolbar and load it up. Pronto!

    International Short Date Formatting for the TEXT Function

    I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range.

    Here’s what a US user sees:

      From: 6/6/2011 to 6/10/2011

    Here is the formula:

      =”From: ” & TEXT(MIN(ExtractData!A:A),”m/d/yyyy”) & ” to ” & TEXT(MAX(ExtractData!A:A),”m/d/yyyy”)

    The Min and Max dates are in column A on a worksheet named ExtractData.

    The problem is trying to automatically change the date format in the second argument of the TEXT Function – “m/d/yyyy” – which is a string argument. We can use VBA to accomplish this, but first a refresher on the TEXT function syntax.

    TEXT Function Syntax and Argument

    The Solution

    The Application.International Property solves this problem with the xlMDY argument, which is TRUE if the date order is month-day-year, and FALSE if the date order is day-month-year. This property is put into the Workbook_Open event and modifies a defined name constant that’s used for the second argument of the TEXT function.

    Here’s how it’s done.

    Create a Defined Name Constant for the Date Format

    I created a Defined Name Constant to store a Short Date format, and gave it the name sd_format.

    In Excel 2007 and 2010, choose Formulas > Define Name to bring up the New Name dialog box where you type in sd_format in the Name box, and type =”m/d/yyyy” in the Refers to box. Remember the equals sign.

    New Name dialog box

    In Excel 2003, 2008, and 2011 choose Insert > Name > Define to bring up the Define Name dialog box. Type sd_format in the Names in workbook box, then type =”m/d/yyyy” in the Refers to box. Be sure to use the equals sign.

    Substitute the Named Constant in the TEXT Function

    The sd_format defined name can now be substituted for “m/d/yyy” in the second argument of the TEXT function. Here’s the new formula:

      =”From: ” & TEXT(MIN(ExtractData!A:A),sd_format) & ” to ” & TEXT(MAX(ExtractData!A:A),sd_format)

    Since sd_format is already a text string, enclosed quotes are not needed.

    Create a Workbook Open Routine

    In the VBA Editor, I created a Workbook_Open subroutine, which looks at the computers international setting for the US, and if TRUE changes the sd_format value to “m/d/yyy”, and otherwise changes it to “d/m/yyyy” for the UK short date format.

    Private Sub Workbook_Open()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' This routing updates the regional date setting format for the
    ' defined name sd_format.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If Application.International(xlMDY) = True Then
    ThisWorkbook.Names("sd_format").Value = "m/d/yyyy"
    Else
    ThisWorkbook.Names("sd_format").Value = "d/m/yyyy"
    End If
    End Sub

    How it All Works

    Each time the workbook is opened the Workbook_Open routine executes the IF-THEN-ELSE statement, which simply looks to see if the computers region setting is month-day-year, then sets the defined name sd_format to “m/d/yyyy” which is a US format. If the computer’s region setting for the long date format is NOT month-day-year (and presumably day-month-year) then sd_format is set to “d/m/yyyy” for the UK.

    Every TEXT function using sd_format for the second argument will then have the proper short date format for that computer.

    Note: Obviously if the region settings are changed on the computer while the file is open the file will have to be closed and reopened, but this would most likely be a rare occurrence.

    Related Posts Plugin for WordPress, Blogger...