How to change relative hyperlink to exact link in Excel

You may have used the HYPERLINK function in Excel before but what you may not have released is your links can be either relative or absolute (or sometimes called exact or specific). What does this even mean? And how do you change from relative to exact?

Excel Tips from the Best 2021 March Madness Brackets

After a year hiatus it’s finally here, the college basketball March Madness brackets are back! Last year, I made a NFL Draft Game spreadsheet for the first time to try to compensate for the loss of the basketball brackets but it just wasn’t the same (but I did still update it for 2021). This year, the 2021 NCAA men's basketball tournament will be unlike any March Madness that has come before. All games will be played in Indiana, with most in Indianapolis. The schedule has also been change. The First Four would typically be played on Tuesday and Wednesday night with the first round being played on Thursday and Friday. 

Here's the 2021 March Madness schedule:

• First Four — 4 p.m. start on Thursday, March 18
• First round — 12 p.m. start on Friday, March 19, and Saturday, March 20
• Second round — 12 p.m. start on Sunday, March 21, and Monday, March 22
• Sweet 16 — 2 p.m. start on Saturday, March 27, and 1 p.m. start on Sunday, March 28
• Elite Eight — 7 p.m. start on Monday, March 29, and 6 p.m. start on Tuesday, March 30
• Final Four — 5 p.m. start on Saturday, April 3
• NCAA championship game — 9 p.m. Monday, April 5

Once again, I will be using the best March Madness brackets in Excel, created by David Tyler (and I will continue to use his until he decides to no longer update them). They’re very polished and easy to use. There are only 68 teams in the field but the spreadsheet is already setup to handle up to 128 teams, if they expand in the future. There are two sheets: the bracket and the pool manager. Instructions are included but its very intuitive. 

The First 5 Things I Do When Examining Someone Else's Spreadsheet

As I’ve said countless times before, you can learn a lot by looking at templates made by others. Here are 5 things I do when examining a new spreadsheet:

1. Unhide hidden sheets, columns, and rows: When you make a template others are going to be using, you want to make it look nice and clean and hide anything that could cause confusion to a first time user, which leads to hiding rows, columns, or even entire sheets in a workbook. So, the first thing I do when examining someone else’s template is look for the hidden data. Right click on the sheets tab and click “unhide”. I unhide all the hidden sheets if there are any to see what data is present. Look for any hidden columns or rows as well by seeing if any letters or numbers are skipped.

2. Understand the NamedRanges: Go To Formulas > Name Manager and examine what the named ranges are, what sheets and cells they refer to. Hopefully they're all named well, like in David's brackets.

3. Look at conditional formatting rules: On the Home tab, go to Conditional Formatting, click Manage Rules, then Show formatting rules for This Worksheet to view them all.

4. Look through the formulas: On the Formulas tab, click “show Formulas” to show if they were manually typed in or if there is a formula calculating the values

5. Look through the macros: Hopefully, the person writing the code left lots of good comments so it’s easier to follow along with what each piece of code does

Watch me quickly walk through David's 2021 March Madness brackets going through the five points listed above:

How to find the values of diagonal cells in a table in Excel

Today I'm going to show you how to use the OFFSET function combined with Index and Match to find the values of diagonal cells in a table using my Super Bowl squares template as example.

Use the OFFSET function to find the value of a cell to the upper left, lower left, upper right, or bottom right of another cell. Let's say my name is in cell B2, the reference cell. To go up a row use -1, to go down a row use 1. Use -1 to go to a column to the left, +1 to go to a column to the right.

Upper left: =OFFSET(B2,-1,-1)
Upper right: =OFFSET(B2,-1,1)
Lower left: =OFFSET(B2, 1,-1)
Lower right: =OFFSET(B2, 1,1)

Now this is useful if you know the exact location of the reference cell, but what if that reference cell is always moving? That's where INDEX and MATCH come into play. A perfect example is taken from my Super Bowl Squares spreadsheet template. Watch the video below to see how I can get the values of the diagonal cells when the reference cell can change once I hit the randomize numbers button:

What do you think of that? Would you like to see more examples like this, taken straight out of a template? Let me know in the comments below.

Ask Excel Help: How to add or count across multiple sheets in Excel

I recently received a question from a reader on our Excel Help Facebook page asking the following:

=SUM(First:Last!A1) adds up numbers across a series of sheets in the same workbook only if A1 contains a number. How do I add up the total number of times an A1 cell contains a text value x?

First of all, if you’re not familiar you are able to add values across different sheets using SUM function. Say you have 3 worksheet named Sheet1, Sheet 2, and Sheet3. You can use this formula to add up all the values in cell A1 in all three sheets:

=SUM(Sheet1:Sheet3!A1)

This special syntax is referred to as a 3D Reference.

Remember, SUM adds all the numbers in a range. You can type in the formula manually, or, after typing “=SUM(“ hold down the shift key and select the sheets, then select the cells to sum. To sum a range of numbers across sheets rather than an individual cell, the formula would look like this:

=SUM(Sheet1:Sheet3!A1:B16)

If your sheet names are numbers, add apostrophes to the formula:

=SUM(‘1:3’!A1:B16)

So our reader was asking how to count cells with text rather than adding numerical values. COUNT function will count the cells containing numbers, while COUNTA will count the number that are not empty. Same formula as before but replace SUM with COUNTA:

=COUNTA(Sheet1:Sheet3!A1)

This method works for almost any other function, too, such as AVERAGE. However, if you try using SUMIF or COUNTIF you’ll probably get a #VALUE error. Using those functions requires a bit more work using named ranges and INDIRECT, a topic for another day.

If you ever have any Excel questions or need any Excel help please feel free to send them my way! I read each and every message or comment I receive.

Quick Excel Help: Date and Time Formats

I received an email from one of my readers asking a question about date and time formats in Excel and I figured I would share my answer here in case anyone else has the same question.

Question: Hi Nick, I am having trouble with Excel time date formats and was hoping you can help. I have a data field with a date time format and I want to separate this out with date in one column, time in another, & hour of the day  in another. Can you please tell me how to do this?

Answer: There are a number of date and time functions in Excel that should be helpful to you. I’m not sure if this is exactly what you’re looking for but try these formulas out:

In column a: =now()

Column b: =month(a2)&”/”&DAY(A2)&”/”&YEAR(A2)

Column c: =HOUR(A2)

Column d: =minute(A2)

Column e: =Second(A2)

Column F: =TIME(C2,D2,E2)

Do you have any other suggestions for this reader’s question?

Quick Excel Tips: Dynamic Lookup

Today, I’m going to show you one of my favorite Excel tips: Dynamic Lookup or Dynamic Searching by using a combination of VLOOKUP and MATCH functions. Bascially, this function combo makes it so that the column that you pull the data from is dynamic based on the header making it more flexible than VLOOKUP by itself because you don’t have to rely on knowing the index column number.

In my example (that you can download below), in cell B2 I have this formula that combines VLOOKUP and MATCH:

=VLOOKUP($A2, $D$2:$G$14, MATCH($B$1,$D$1:$G$1,0),FALSE)

The column header in B matches one of the column headers in D, E, F, G - it doesn’t matter which one, you can change it and the values update automatically, which is the beauty of this formula. This makes it easy to add or remove columns without having to update your formulas. Not only is it more dynamic, the index column need not be on the left. Try it yourself!

Download my Dynamic Lookup formula example spreadsheet here.

Top Excel Help Posts in 2013 and 2014 Goals

2013 is coming to a close so it’s that time of year to sit back and reflect about what transpired this past year and what is to come. I’m going to share my most popular Excel tips from the past year, my most popular Excel templates, and finally share what my goals and vision for next year is.

Top 5 Excel Tips from 2013

 

Listed below are the five most popular Excel tips I published on the Excel Help blog throughout 2013:

 

1. How to combine Excel files with a VBA macro
2. How to filter Data in Excel
3. How to create collapsible rows in Excel
4. How to embed Excel in a website
5. Excel pranks and practical jokes

 

Top 5 Excel Templates from 2013

 

Listed below are the top 5 most popular free Excel templates and spreadsheets I created and made available to download in 2013:

 

1. Printable Super Bowl Squares
2. Website Creation Checklist
3. Newborn Checklist for New Parents
4. Apartment Comparison Spreadsheet LITE version
5. NFL Helmet Schedule

 

Goals for 2014

I recently took a poll of my loyal followers by asking them what they would rather see more of on the blog: spreadsheet templates or tips/tricks/macros to improve spreadsheet creation efficiency? My most recent posts have primarily been spreadsheet templates I’ve made available to download for free, such as my newborn feeding schedule, college bowl prediction pool, etc. Something I’ve gotten away from lately is posting tips and macros to improve your Excel efficiency, like my earlier posts on how to create folders automatically, how to create hatching in Excel, etc.

The majority of my reader’s responses asked for more tips, tricks, and macros to improve Excel efficiency. I will still be creating and posting templates as I have been because I think a lot of people find them to be quite useful and you may pick up a few tips just by looking at them and examining the formulas I use. However, I promise to make a strong effort to post more tips and macros, especially for my email subscribers. In fact, I already have several rough drafts already written and am working on some new video tutorials. What would you like to see more of in 2014? Anything specific?

Have a great holiday season, thanks for your continued support, and see you in 2014!!!

My Excel 2016 Wishlist

Today, I started thinking about my Excel 2016 wishlist. Microsoft Excel has been updated every three years beginning in 2007. Knowing this, we can only assume the next edition of Excel will come out in three years from now in 2016. The latest update, Excel 2013, includes new tools like FlashFill, PowerView, Timeline Slicer, and over 50 new functions. Don’t get me wrong, Excel is a very powerful tool but it’s not completely perfect yet.

Thus, it’s never too early to start thinking about the next edition of Excel and what improvements can be made. There are a number of features that I think would make creating spreadsheets even easier. A couple of simple improvements could help save users even more time. Here are some of the things I’d like to see in Excel 2016:

• A FIXTODAY() function that holds the date of the day the formula was first created or entered into the spreadsheet.
• I don’t know if this will ever be possible outside of Google Docs but allowing multiple users to edit the same document at the same time would be wonderful.This would eliminate all those extra copies of spreadsheets floating around.  This option is already available!
• I don’t know why you can’t do this already but it would be great to be able to unhide multiple worksheets at the same time. Currently, you can only unhide one sheet at a time which can be a major headache and quite inconvenient.
• Along this same line, I would like the ability to unprotect multiple sheets at once. Something like a global password may be the solution.
• An easy way to be able to see exact revision history.
• More options when protecting a sheet, like being able to use CustomViews.
• More text manipulation functions and options.

That’s what I think. What about you? What would you like to see in future versions of Excel? What’s on your Excel 2016 wishlist? Please let me know in the comments below. Who knows, maybe Microsoft will even be taking notes!

NCAA Football Bowl Schedule and Pool 2012

The NCAA college football bowl season is finally here which means it’s time to make your picks and predictions about who you think will win each game. One of the best times of the holiday season (other than giving and receiving gifts) is gathering around the TV and rooting for your favorite football team in a BCS bowl game. Make the occasion even more fun and competitive by trying to predict the NCAA football bowl games.

Use my spreadsheet template to create a college bowl pool with your friends or coworkers. Simply download the Excel file linked to below and follow the instructions included within the spreadsheet. The NCAA bowl pool also doubles as a printable college football bowl schedule. The spreadsheet is all set up and ready to go - simply make your picks and then watch the game to see who the winner is. It automatically keeps track of several different stats like how many games each person gets correct, what percentage of games you’ve picker correctly, and more!

How it Works

Altogether you do not have to enter or change any formulas or complicated functions to use the spreadsheet, I will briefly explain how it works in case you want to create your own from scratch or modify mine:

The college bowl spreadsheet uses drop down lists to allow the user to select a winning team. The number of games picked correctly is added using Excel’s SUMProduct function. Conditional formatting is used to color code correct picks from incorrect picks.

Download the NCAA College Football Bowl Schedule and Pool

To download the spreadsheet simply click the link below which will take you to the box.com, where the file is hosted. Next, click on the “Download” link in the upper right hand corner. That’s it!

Download: NCAA Football Bowl Schedule 2012.xls (hosted on www.box.com)

Update 12/12/12: I uploaded a new version of the spreadsheet with a macro that automatically adds the correct number of columns and stats for the total number of players. To run the macro go to the Master Pool sheet and run the "AddPlayers" macro (by going to View>Macros>View Macros>AddPlayers>Run). After hitting run, enter total number of players into pop-up input box. Click OK then complete the rest of the template as usual.

Also, I’ve created a Google doc to start an Excel Spreadsheets Help NCAA bowl pool. To join, download the spreadsheet, make your predictions, then copy and paste your picks into my Google doc here:

https://docs.google.com/spreadsheet/ccc?key=0Av7RTFdlK3AmdFBwcHBINi1jUi1jdm1ZWTFtbWNuQkE

Final Thoughts

I’m sad to see my Buckeyes sitting on the sidelines this year (should have taken a bowl ban last year) although I’m hoping my Toledo Rockets can cap off the year with a good win. I’m a Big Ten and MAC guy so I’ll be pulling for each conference team but I have to admit I have almost no hope or confidence for any of them winning a single match up. What are your NCAA bowl predictions? What are your bowl picks? Leave a comment below and let me know! Like our Facebook page to receive notification when we post our NFL playoffs bracket and other sport templates.

-Nick

Go Rockets!

Unique Excel Uses: Designing Roller Coasters

We’re always looking for new and unique uses for Excel and I recently stumbled upon a very thrilling example of what Microsoft Excel is used for. Travis Rothbloom is a mechanical engineer and aspiring roller coaster designer. He decided to design a roller coaster for a school project using a  combination of Excel and MatLAB. This massive spreadsheet with roller coaster physics formulas contains 8500 rows by 50 columns of data! Travis explains how he compiled his engineering spreadsheet:

The first thing that I established in my spreadsheet were the constant values that I used, namely gravitational acceleration, friction coefficients, and finite step size along with other parameters that helped define the physical geometry of the track. Excel's functionality of maintaining a reference's cell index with the "$" symbol really came in useful when I needed to change friction values - all I had to do was change the one cell storing the coefficient and the entire spreadsheet (thousands of lines of data) would update automatically.

Formatting, constant values, a table containing statistics, and an embedded equation using Excel's native trig functions.

Then it was time to take the physics equations that I derived and embed them into the spreadsheet. This was pretty easy given that Excel has built-in methods for calculating trigonometric functions, powers/roots, and division remainders while maintaining the proper order of operations. Given that many of the calculated rows' values are dependent on their respective column's previous value, I had to set up a row to store initial conditions as to not cause a null reference. When I did have null references or circular dependencies, however, it was easy to spot the source with Excel's error handling mechanisms. Formulas that described the dictating curves of the track, whether they be in g-forces, roll angle, curvature radii, etc., relied on an incrementing time index whose interval was dictated by the finite step size parameter's cell.

With all this, I relied on Excel's formatting to help visualize what was transpiring in my spreadsheet. I highlighted both rows and individual cells to indicate what was a dictating, inputted value vs. what was being calculated by other values; this was not the same for every row because I would sometimes rearrange the equations for nuanced track elements, and using this color coded system made this a whole lot easier to keep track of. I also used blank columns highlighted with a color to separate columns into groups for easier viewing. Along with that, I frequently would hide multiple columns or rows to help navigate the spreadsheet as thousands of lines and up to 50+ columns of data can become unwieldy at times. Lastly, I created a table at the top of the sheet that maintained the maximum or minimum values of particular values such as speed and different g-forces.

2D plot showing an elevation of the ride

Although Excel doesn't have a built-in 3D plotter, I created 2D plots of the track coordinates so I could view the track geometry within the spreadsheet. I stored these in separate tabs for easy navigation. Also stored in a separate tab was any other miscellaneous information that I would reference.

Finally, I made use of the fact that other programs usually have an easy time reading/parsing Excel documents. I frequently imported my spreadsheet into Matlab for further post-processing, including 3D plotting and some other calculations. It's not necessarily the case that Excel wasn't able to do any of these other things (for example, I have found user-created 3D plotting macros online) but rather I'm more comfortable coding in Matlab rather than VBA. People would often ask me why I didn't just work in Matlab for the entire project, to which my response was that I thought (and still do think) that working with mass data sets in spreadsheet format is best done by Excel and since it is found on so many computers and it's so easily read by other programs, it was easy to work on the project wherever I was.

2D plot showing the ride's plan

Thanks again to Travis for sharing his awesome “Excel uses” example and good luck towards your goal of becoming a coaster creator - and let us know when you do so we can go ride your  breathtaking creation! Read more details about Project Soar at his website.

Subscribe to our newsletter for more updates and drop us a line (or a comment) if you know of someone with a unique or interesting  “Excel Used For” example.

An Excel Macro to Delete Pictures

How to delete pictures and textboxes based on a range

I recently created an Excelpicture  macro where I deleted a range of rows from my spreadsheet. However, I had a few pictures and textboxes residing inside the area that I wanted to delete. These objects aren’t tied to any individual cell or row so they were originally not being deleted like I wanted. What to do? Time to edit our Excel vba picture macro.

I added a few lines of code which essentially check if the top left corner of the object intersected with any of the cells contained within the range I wanted to delete. If so, and the object was a picture, then it was deleted. Same for the textboxes.

‘Delete pictures

Dim Sh As Shape

   For Each Sh In ActiveWorkbook.Worksheets(I).Shapes

       If Not Application.Intersect(Sh.TopLeftCell, Range("A40:AA120")) Is Nothing Then

         If Sh.Type = msoPicture Then Sh.Delete

             End If

    Next Sh

'delete text boxes

 For Each Sh In ActiveWorkbook.Worksheets(I).Shapes

       If Not Application.Intersect(Sh.TopLeftCell, Range("A40:AA120")) Is Nothing Then

                  If Sh.Type = msoTextBox Then Sh.Delete

               End If

    Next Sh

By using macros in Excel, this process was repeated inside code which looped through all the sheets in my open workbook. To loop through all worksheets:

Dim WS_CountAs Integer

 Dim I As Integer

' SetWS_Count equal to the number of worksheets in the active workbook.

WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop through all worksheets.

   For I = 1 ToWS_Count

'active sheet i

ActiveWorkbook.Worksheets(I).Activate

‘insert delete pictures and textboxes here

Next I

As with most computer programs, there are multiple ways to go about the same task and it’s up to you to pick the best one. Another option is this:

Dim shpAs Shape

    For Each shpInActiveSheet.Shapes

        'Debug.Printshp.Name, shp.TopLeftCell.Row, shp.BottomRightCell.Row

        If shp.TopLeftCell.Row>= 5 Andshp.BottomRightCell.Row<= 39 Then

shp.Delete

        End If

    Next

Take Advanced Excel Training to learn how to write VBA macros in Excel.

How to find the best score based on condition

In this example, I have a workbook with two worksheets. On Sheet1, in column A, there is a list of more than 2000 individual’s names (some of them repeated) and column B lists each person's average test score percentage. In the second worksheet, called Sheet2, column A contains a list of each person's name exactly once and in columns b, c, d I want to list their first, second, and third best score or percentage.

How to use the LARGE function in Excel

The first, second, or third best score can be obtained using the Excel large function. The LARGE function allows you to return the nth largest value in a unique data set, like the second best score. The syntax for the large function excel is: =LARGE(array,k) where an array is a range of data and k is the position from the largest value in the array. So, for the first best score k=1, second best k=2, etc.

The LARGE function excel will get the best score from the list but we need to make it so the function only applies to the individual listed in sheet 2 column A. This is where the excel array function is used.

Using arrays in Excel

An array is a range of data, like items in a table. Often times you may see array formulas referred to as "CSE formulas," because you press CTRL+SHIFT+ENTER to transform the function into an excel array formula. We need to use our LARGE function in an array in order to return the best score based on the condition of the person’s name. So we’ll use the Excel large if.

To return the 2nd largest score for Joe, the name listed in Sheet2 column A, use this excel large formula:

{=LARGE(IF(Sheet1!$A$1:$A$2000=$A2,Sheet1!$B$1:$B$2000,-1),2)}

Remember, to enter this array formula into the cell, then instead of hitting the Enter key, hit Ctrl-Shift-Ente

Download the large and array examplespreadsheet from Google docs or join our email newsletter to receive it as an .xls attachment.

Personal Business Management Spreadsheet Template

We’re in the middle of tax season here in America so it comes as no surprise that some of the most requested Excel spreadsheets this time of year are personal and business finance and accounting templates. In my everyday life I use three primary spreadsheets to help track my finances and will be writing a post about each one.

First up is what I like to call my business accounting spreadsheet. I call this a “business” but it’s pretty basic seeing as how I’m the only employee. Maybe a better name would be web site management spreadsheet or even personal project management (or project tracking) – because that’s what this is, a personal project, after all!  Also please note, this template is relatively new and is continuously evolving as I add new features to my web site and want to analyze the data in new ways.

The Concept

So what is this so called business or project? This blog uses the Blogger platform, which is free to use but requires .blogspot to be added at the end of the domain name. I’ve always wanted to create my very own web site and finally did so - how to learn to write CATIA macros. The site contains several free articles with tips and advice about VB scripting in CATIA, a 3D CAD program.

However, creating and maintaining my own web site costs money. I decided to treat my site like a business. I keep track of all expenses and revenue because the goal is to have the site pay for itself through the sale of an eBook I wrote on the same topic. If the site is not profitable over time I will abandon it. I guess you could classify this type of web site as a “niche profit site.”

Total Expenses

The first sheet I have in my template is labeled Total Expenses. This where I keep track of any products or services I have to buy to keep the web site up and running, as well as the initial start up fees. For example, I purchased the domain name www.scripting4v5.com through NameCheap at $10.87 for an entire year. In the month column I use the MONTH function to return the month of a date as a number, which will be used later on in my monthly report worksheet. I use HostGator (exceptional customer service – I speak from experience!)  to host my web site, a monthly expense.

For my CMS (content management system) I decided to go with Wordpress because it’s user friendly and free (thus not included as an expense). In addition, I purchased a new theme called Socrates due to its number of built in features which again are very easy to use. The onetime fee is added to the expense sheet.

In order for customers to purchase and download my eBook, I needed a way to protect the download link so it couldn’t be copied and shared with other users. I bought a program called WP File Lock one another onetime fee of $47.

Finally, I use an email newsletter service called Aweber to manage my email subscribers. This service is a monthly fee of $16.33. That’s it for my expenses. I know it sounds like a lot but really I only have two only monthly bills (email newsletter, and hosting) and one yearly expense (domain name).

Total Revenue

Now, let’s look at the next tab in my workbook, Total Revenue, where I list all my revenue generated from the web site. At this time I am using Google Adsense to place one banner of ads across the top of the site as well as Kontera ads within the text.

The main revenue stream is from selling VB Scripting for CATIA V5 eBook. I have a referrer column to indicate whether I sold the eBook or if it was sold through one of my affiliates. Yes, if you have a Clickbank account you can earn a 50% commission for selling my book for me!

Once again, I use the MONTH function to return the number of the month, as in cell F2, =IF(E2="","",MONTH(E2)). At the bottom of the sheet I add the totals for each of my site revenue streams, as in cell B20 I have =SUMIF(A2:A15,A20,D2:D16).

Monthly Report

Finally, on the third sheet I can look at my total expenses and revenue by month. This gives me a great snapshot of how the site is doing. I use the SUMIF formula on my Monthly worksheets where I can view total expenses, revenue, and if I have made or lost money for the month. For example, in cell B2,

=SUMIF('Total Expenses'!F2:F9,2,'Total Expenses'!C2:C9) I also use conditional formatting to highlight when I've spent more money than I’ve made in red and highlight the text in green when I have made a profit.

Summary

In review, I was able to setup my first web site at an initial cost of $209.11, shown here on my spreadsheet. My expected monthly recurring expenses are $26.28. So now what? The purpose of the site is to sell my eBook. When a sale is made I add it to my revenue column. It will take a few sales to cover my initial expenses but then I should only need to sell one eBook a month in order to pay for the site every month.

Sorry for the long article, but that was my “business” spreadsheet in full. Next, I’ll cover the Excel spreadsheet template I use to track all of my real-world and online income, then we’ll look at how I keep track of bills and other living expenses.

DOWNLOAD PERSONAL BUSINESS PORJECT TRACKING TEMPLATE.xls

*Full disclosure: Some of the links in this article are for affiliates. I earn a commission if you purchase the product having following the link. I only name products that I actually use and fully endorse.

·         Tags: personal finance excel spreadsheet, monthly finance spreadsheet, Free excel project sheet