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 Hide Gridlines in Excel - Three Ways

Gridlines in Excel are the faint, grey colored lines that appear around cells in a spreadsheet to distinguish them from one another. By default, the gridlines are displayed in worksheets using a color that is set by Excel. You can essentially turn off the gridlines, cover up the gridlines, or change their color in order to hide them.

There are at least three methods you can use to hide the gridlines in Excel.

1. The proper method is to go to View then uncheck the Gridlines box. You can hide gridlines on multiple sheets by selecting all the sheets first, then unchecking the box.

2. Change a cell’s fill color to white or another color. If you apply a fill color to cells on your worksheet, you won't be able to see or print the cell gridlines for those cells.

3. Change the gridline’s color to white. You can change the default Gridline color by going to File > Options > Advanced then scroll down to Display options for this worksheet.

To see how to do each one of these check out the video below.

Borders are not the same as gridlines in Excel

People often confuse borders and gridlines in Excel. Gridlines cannot be customized in the same manner that borders can. If you want to change the width or other attributes of the lines then you need to use a border. Gridlines are always applied to the whole worksheet or workbook, and can't be applied to specific cells or ranges. If you want to apply lines selectively around specific cells or ranges of cells, you should use borders instead of, or in addition to, gridlines.

Let me know if you found this Excel tip helpful!

How to Write a Book Fast by Using Excel - Video Tutorial

Where most authors are writing their books in Microsoft Word, Scrivener, or Pages for Mac, I’ve actually been using Microsoft Excel to write books fast and today I’m going to show you my exact technique. You can see an example of one of my books created in Excel by checking out The 50 Most Terrifying Roller Coasters Ever Built on Amazon.com. As you can see, it has a 4.5 star rating from 14 reviews - none of which are from people I know, all real readers and customer - and has sold more than 2,000 copies. I’ve written four books using this technique and I chose this niche because I have a real passion for roller coasters and amusement parks.

Why write a book in Excel?

List type blog posts and articles are very popular these days, so my idea was to turn one of these type of list posts into a short book. This method is probably not a good idea for every type of book. You wouldn’t want to write a novel or long work of fiction in Excel. But if you're trying to relay stats or facts or have some form of repetition then this technique could help save you a lot of time. Anytime you find yourself doing something over and over or thinking to yourself "there has to be a better way" then, guess what, there probably is a better way and macros could be the answer.

How do you write a book in Excel?

Each row in my spreadsheet template starts a different page in the book. So 50 coasters equals at least 50 pages. I’ve colored coded the spreadsheet: every column in green is my initial input. Orange columns are formulas that use information from two other columns. All columns without color contain data that was entered by a virtual assistant whom I hired specifically for this job from Upwork.

Basically, I came up with the list of 50 coasters I wanted to feature in the book, then I outsourced the data collection process using Upwork. While my virtual assistant was contacting theme parks and researching data, I wrote the code to the macro that would automatically export all the data from Excel into the format in Word that I was looking for. The macro runs through a simple For..Next loop, looping through each row of the spreadsheet and exporting the data into the Word document.

After the information is exported to Word, just add your typical front and back matter, pictures, edit, and you’re done. Uploading to CreateSpace and Kindle Direct Publishing is a breeze. And you can outsource most of these tasks as well, if you have the budget for it. Or do it yourself. Most people don't realize that if you have something written, it could be on sale on Amazon.com in as little as two days. Heck, some guy got a picture of his foot to be a best seller.

Watch My Book Magically Appear

Watch the video tutorial below where I show you how to write a book fast by using Excel and see the book pages magically appear before your very eyes.

Steps to Write a Book Using Excel

Here's the basic steps I use to start writting my book with a spreadsheet:

1. Setup Excel sheet
2. Outsource data entry using Upwork
3. Create macro to automatically export data to Word
4. Run Macro
5. Add front and back matter, pictures, etc. to book in Word document
6. Edit and create front cover
7. Publish on CreateSpace and KDP
8. Market and promotion

Feel free to contact me if you'd like to know more or have questions about how the macro works to export the data from Excel into Word.

How to change the default arrow size in Excel

As an engineer at my day job, I’m constantly making diagrams in Excel. I have to point at and label different components using lines and arrows in Excel, but it’s rather annoying when I have to change the line weight and arrow color every single time. But this is Excel we’re talking about here, where there is an option for almost everything. So yes, you can actually change the formatting defaults of a shape, text box, or other object and then make your changes the new defaults for anything you add later. For example, if I want all of my arrows in Excel to be colored red, I can change the default to red so I don’t have to change it every single time – a great time saving tip!

To change the default arrow size and color in Excel (or any other shape) follow these steps:

1. Add the arrow (or other shape) that you want to change the formatting defaults for (insert>shapes>arrow)
2. Select the arrow
3. Change the arrow’s color and weight or any other changes that you want
4. Right-click the arrow, and then click Set as Default Line.

Now you can create new arrows with the same formatting and not have to create it or copy and paste it over and over again.

The new default only applies to the open workbook. I have not yet discovered a way to permanently change the arrow size, from session to session or workbook to workbook, other than possibly saving it as a template. Do you have any other suggestions how to do so?

Schedule Meeting Time Template

Having trouble finding the best time to meet with your team? Use this Excel template to find the perfect date or time for your meeting. Here’s how to use the meeting scheduler template: Enter your name in the input field, then use the drop down menu to add a check mark into the time slots you are available. If you’re not available, then leave it blank. When all the required meeting attendees enter their available times, the spreadsheet shows you the first and second best meeting times.

One thing this template will demonstrate is how to insert a check mark in Excel. Go to the top tab "Insert" then in click the "Symbol" button on the far right. A dialog box will appear and you need to select "Wingdings" from the drop down list at the top left. Scroll through the symbols until you find the check mark (wingdings: 252). If you copy and paste the check into a new sheet you may see a ü symbol instead. Simply change the font of that cell to wingdings to get the check mark back.

The next thing you’ll see by dissecting this template will show you how to use a check mark in a drop down list. Another function you can examine is how to use conditional formatting to color a cell based on the cell’s value. In this case, we want to color our cell green if it contains a check mark and red if left blank. As you can see, you will use the "ü" symbol in the formula.

Download the Easy Meeting Schedule Template.xls 

There are similar online tools to help you schedule meetings but most require a fee to unlock all the features. Instead you can use and modify this free Excel template. You could add more functionality, like adding a formula to automatically send an email once you’ve picked the meeting time.

Do you think this template will be useful to you?

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?

How to input military time into Excel

I have to admit, the title of this post is a little bit misleading because there is no direct way to input military time into Excel. If you try to input "1300" as 1:00PM, Excel has no way to know that you're entering a time as opposed to a number. In order for Excel to recognize your input as a time and not 1,300 you must enter a colon and enter as "13:00". Basically, formatting the cells for dates and times only affects how the contents of the cell is displayed and not how the information is entered. So if you enter the military time with a colon and format the cell as Time then 1300 will be converted to 1:00PM.

Here's why: Excel stores dates and times as days and fractions of a day, where the number 1 equals January 1st, 1990. Entering 1300 into a cell leads Excel to interpret that as the 1300th day since January 1st, 1900 at 12 midnight.  Enter 1300 into a cell and format it as Time. Notice the value displays this: 7/23/1903  12:00:00 AM. Interesting, no?

Going back to the original question, how to input military time into Excel, my suggestion is this: entering a colon while inputting the military time is extra work. We always want to make data entry fast and painless as possible. So let's use a formula that will enable us to enter military time as 1300 but then display the regular time.  If we input the value of 1300 in cell A1, enter the following formula into cell B1: 

=time(int(a1/100),mod(a1,100),0)

In the picture above, you can see the how the military time is entered and the formula converts it to standard time. This post was inspired by a question from a reader on our Excel Spreadsheets Help Facebook page.

8 Simple Rules to Make Your Spreadsheets Look Better

A little bit of formatting can turn an average looking spreadsheet into a great looking spreadsheet. Here are 8 simple rules to make your spreadsheets look better:

1. Use no more than two different font types.
2. Name your spreadsheet. Give your spreadsheet a descriptive name as well as naming all the individual sheets, tables, charts, etc.
3. Use no more than three to five different fill colors. I typically use white or a light grey for my background colors and a few lighter colors for accents.
4. Don't use 3D charts. Charts are a great addition to any spreadsheet but make sure they are of the 2D variety rather than the 3D option.
5. Turn off gridlines. To turn off the gridline, go to the View tab and uncheck the box next to gridlines.
   
6. Bold your headers (but don't get too crazy).
7. Create space. Let your spreadsheet breathe. Don’t be afraid to leave a completely empty column or row in between your data sets.
8.  Less is more. A general rule when it comes to formatting is less more. It's super easy to get carried away so remember to use some restraint.

There you have it! You can see examples by downloading my free spreadsheet templates. I hope these tips help improve the look of your spreadsheets.

Newborn Checklist for New Parents

Sorry for the lack of posts recently (besides sports spreadsheets) but the reason is because I’ve been getting ready for something big, something exciting, something life-altering: my wife and I are expecting our first child next month! We’re super stoked to be having a son but anxious at the same time. We’ve been working hard at getting everything ready but I can’t help thinking, “what if we’ve forgotten something important?” There have been so many things to do and there are so many things yet to do. How does a new parent keep it all organized? My solution is to use an Excel spreadsheet of course!

 

 

In order to keep myself organized and to help other future parents I’ve put together what I hope is the ultimate newborn checklist in Excel. This master file is composed of four separate sheets (I imagine I'll probably add more in the future too):

Things to do BEFORE the Baby is Born

The first sheet is a getting ready for baby checklist. It includes a list of things you should do before your baby is born and how soon before the due date they should be completed by.

Baby Registry Checklist

The next sheet in the file is the baby registry checklist. You can use this for a baby shower checklist or for a list of things you need before the baby arrives.

Hospital Bag Checklist

A list of what to pack for the hospital that you can easily print off if you would like.

 

Things to Do After the Baby is Born checklist

Another checklist of things that need to be done after the baby is born.

Behind the scenes so to speak, the newborn checklist spreadsheet uses named ranges, data validation to create lists, conditional formatting to color cells based on text, IF formulas, concatenate formulas, data filtering, and more. You’re free to poke around the spreadsheet if you want to see how it works or you don’t have to deal with any of that if you don’t want to and simply fill in the information or print it off.

My goal is to make this the ultimate resource for new parents. I’ve tried to include everything such as what to pack in hospital bag for mom. Tasks can be assigned to husband or wife. I also included some links to some services that can help you save money, like Amazon Mom. Believe me guys, your wives will be very impressed and happy if you use this list and actually help her prepare (yay brownies points). Download the spreadsheet for free using the link below.

Newborn checklist.xls download

Please let me know in the comments if you enjoy this newborn checklist and if you would like to see more parenting Excel templates like it.

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!

Macro to Export Hyperlinks from Excel to Word

In this tutorial I am going to show you how to write a VBA macro to export hyperlinks from Excel to Word. I’m all about automation and efficiency and this is another real world example. I used this macro to quickly create a table of the world’s observation wheels for my latest website.

What you will learn by reading through this tutorial:

• How to create a VBA macro to send data from Excel to Word
• How to export hyperlinks from Excel
• How to find the last row of data in an Excel sheet using a macro
• How to paste hyperlink into Word using a macro
• How to make a webpage from an Excel file

To begin, we have an Excel sheet with names in column A, some of them are hyperlinks to webpages and some of them are not. Hit Alt + F8 and create a new macro, I named mined Tables. It’s time to begin coding.

 

 

The first thing we need to do is create a new instance of Microsoft Word and make it visible:

 

Dim appWD As Word.Application

Set appWD = CreateObject("Word.Application")

appWD.Visible = True

 

Next, let’s find the last row that contains data within our active Excel spreadsheet (my sheet is called “Data”):

 

Sheets("Data").Select

            Dim FinalRow As Integer

 

            FinalRow = Range("A9999").End(xlUp).Row

 

Now we add some error handling - if there is no data then quit the program, otherwise continue on:

If FinalRow = 0 Then

            Exit Sub

 

            Else

 

As a check, I like to have a message box pop-up displaying the total number of rows with data:

 

            MsgBox "Number of rows is " & FinalRow

 

Now it’s time to tell Word to create a new document. We’ll also add our header text by using TypeText. TypeParagraph inserts a new paragraph by going to the next line:

 

            appWD.Documents.Add

            appWD.Selection.TypeParagraph

 

            appWD.Selection.TypeText Text:="[table caption= List of Observation Wheels]"

 

            appWD.Selection.TypeParagraph

 

            appWD.Selection.TypeText Text:="Name,Height(m)"

Next, we’ll create a For...Next loop to cycle through every row and look to see if there is a hyperlink in column A. If there is a hyperlink, we want to copy it, if not then we still want to copy any text in the cell.

 

Dim hyperlink1 As String

Dim i As Integer

 

            For i = 2 To FinalRow

 

            If Range("A" & i).Hyperlinks.Count > 0 Then

 

 

            'if there is a hyperlink

 

            appWD.Selection.TypeParagraph

 

            hyperlink1 = Range("A" & i).Hyperlinks(1).Address

 

            appWD.ActiveDocument.Hyperlinks.Add Anchor:=appWD.Selection.Range,          Address:=hyperlink1, SubAddress:="", ScreenTip:="", TextToDisplay:=Range("A" & i)

 

            appWD.Selection.TypeText Text:="," & Range("B" & i)

 

 

            Else

 

 

            'If no hyperlink

 

            appWD.Selection.TypeParagraph

 

            appWD.Selection.TypeText Text:=Range("A" & i) & "," & Range("B" & i)

 

 

            End If

 

            Next 'i

 

Finally, we can add any text at the bottom of the document and close the if and sub statements.

 

            'end the table

            appWD.Selection.TypeParagraph

            appWD.Selection.TypeText Text:="[/table]"

            End If

            End Sub

 

You may have noticed I began and ended with [table] and [/table]. I installed a Wordpress plugin on my website that enables me to easily insert sortable tables into my webpage without any major html coding involved. So now I can run my macro on my spreadsheet, copy the result it spits out into Word, and paste the text into my website. Here is the end result, a sortable table of all large observation wheels found throughout the world! Pretty cool huh?

How do you create hatching in Excel?

The pattern fill is a great tool to create hatching in Excel 2010 (hatching is when you add fine lines to graphics to represent shading or other factors). However, for some reason the option of filling a series with a pattern was taken out of the user interface in Excel 2007.  The good news is the Excel pattern fill was not taken out of the 2007 object model so you can use VBA to apply a pattern to a series. Andy Pope has done just that by creating an add-on to return the tools for hatching in Excel 2007. We recently had the opportunity to talk to Andy about creating the Excel fill tool. Thanks to Andy for taking the time to answer a few of our questions.

ESH: How long have you been using Microsoft Excel?
A: I started using Excel 5.0 around 1998.

ESH: Could you please explain why the hatching/pattern fill feature can be found in Excel 2010 but was left out of 2007?
A: That is really a question for the people at Microsoft. For Excel 2007 the functionality was there, for backward compatibility, but they did not include any UI for users to employ it. This was either an over-site or a deliberate move to depreciate the feature. The negative feedback to the removal of this feature made Microsoft reinstate the UI for Office 2010.

Excel 2007

ESH: What is an Excel add-on? Why are they needed?
A: Add-ins are hidden workbooks that contain code to extended the functionality of Excel. Normally the functionality they provide is not specific to any one workbook or data set. As with the Pattern Fill add-in it provides the functionality to fill any shape or chart element.

ESH: Could you describe the process of creating the fill add-on? What were the steps involved?
A: First identify a problem or task that can be made easier and or quicker by using VBA code. Create the code required to perform the task. You need to bear in mind the following,

• References should be to the active workbook
• You need to provide UI elements so the user can interact with your code
• You need to handle errors that your code may encounter as you cannot
• control how the user will attempt to use your add-in

Excel 2010

ESH: Great information! Thanks again to Andy for taking the time to answer our questions and for creating such a useful plugin. To download the pattern fill plugin visit Andy’s page here.