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!!!

Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!

2013 Holiday Gift Ideas for Excel Users

Looking for holiday gift ideas for Excel users? The holiday season is right around the corner and it’s never too soon to start thinking about what presents you are going to get your friends, family, or coworkers. To help you out, I’ve made a list of items I think would be very useful or exciting for Excel spreadsheet users. Some of these items I use on a daily basis and others are things that are on my own personal wish list. Enjoy and feel free to suggest items by leaving a comment below!

Excel Books

Excel 2010 Bible by John Walkenbach: This is by far the best Excel book I have ever found for anyone who uses Microsoft Excel spreadsheets on a daily basis.

Excel for Scientists and Engineers: Numerical Methods: With this text as your guide, you can significantly enhance Microsoft Excel's capabilities to execute the calculations needed to solve a variety of chemical, biochemical, physical, engineering, biological, and medicinal problems. If only Excel could solve world peace too!

Microsoft Excel 2010 In Depth by Bill Jelen. Known as Mr. Excel, Bill Jelen is a Microsoft Excel MVP. This book literally covers everything you’d want to know about Excel 2010.

101 Secrets Of AMicrosoft Excel Addict - Discover little-known Excel secrets that have been hiding right under your nose that will help boost your Excel skills and productivity far ahead of everyone around you.

VBA and Other Books
Microsoft Visual Basic 2010 Step by Step by Michael Halvorson: A lot of hands-on work-by-examples will further the development of your Visual Basic programming skills.

　

Best Excel Apps

Kingsoft Android Spreadsheets: A powerful spreadsheet application for quickly calculating data, view graphs and filter data, and also a great tool for editing Excel files and save them to xls, xlsx, csv, pdf and etc.

Simple Spreadsheet: Great little program for putting together some quick calculations on the go. Much easier to use than Google Docs spreadsheets.


Best Tablet for Excel Spreadsheets

Microsoft Surface 2: For my needs, Surface 2 is perfect. This device comes preinstalled with a "touch enabled" Microsoft Office which includes Outlook. Basically, all I was looking for was a tablet to check my email, do some word processing, edit spreadsheets, browse the web, and pay bills online.

Excel Software

Microsoft Office Home & Student 2010: Includes the latest and greatest version of Microsoft Excel, among others.

Other Tech, Gadgets, and Toys

Google ChromeCast HDMI Streaming Media Player. Stream my Excel YouTube videos right to your TV using your smartphone, tablet, or laptop!

R/C Helicopter. This Brand New 3 Channel mini rc helicopter is the world's newest, most durable and stable helicopter. The item comes with the latest gyro system for precision control. The metal frame has proven to withstand dozens of crashes.

HDMI Pocket Projector. Projects up to 1080p HD images up to 60" diagonal. Rechargeable and portable. Connects via HDMI to most smartphones, tablets, computers, video players, cameras and more.

Breaking Bad: Complete Series. All seasons plus tons of amazing extras for the greatest show on television. The episode titled "Ozymandias" is my favorite episode of any TV show I've ever seen.

Amazon Prime

I highly recommend joining Amazon Prime. If you order something off of Amazon on average once per month then it pays for itself, or if you're ordering a bunch of gifts this holiday season it probably will too. The FREE two day shipping is awesome and everything I've ordered really has come in two days or less. You can also stream movies, TV shows, and get a Kindle book every month. Great alternative for Netflix or Hulu and well worth the investment in my opinion. Cheers!


What gifts are you getting your fellow percussionist for the holidays? Please let me know by commenting below!

　

Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!

How to Combine Excel Files

There have been countless times during my day job when I’ve had to combine multiple Excel files into one. I used to do it the old fashioned way of copy and paste but after doing this tedious and time consuming task I decided to write a VBA macro to help me out. I’ve now made the template available for you to use for free. This version of the combine Excel workbooks macro allows you to choose the directory of the Excel files to copy without having to change the code in the Visual Basic Editor. The steps to use the Combine Workbooks tool are as follows:

 

1. Place the Excel workbooks you want to combine into a single folder that contains no other files
2. Go to View>Macros
3. Select "Combine Workbooks" then click Run
4. Select the File where the spreadsheets you want to combine are located
5. Save the newly created file

Instructions are provided with the spreadsheet

Please note that this code will not work on protected sheets and it will automatically skip any blank worksheets in any of the workbooks saved in the folder. See how it works for yourself by watching my short demo video below:
 

 

Download the Combine Excel Files spreadsheet that contains the VBA code to copy all the worksheets from all the workbooks in a folder into the active workbook using this link:
 

Combine Files.xlsm download

Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!

Download a Wordpress Website Creation Checklist Spreadsheet

In the past two years I’ve created eight websites for various hobbies and niches I am interested in and that I felt were underserved in the online world. I use Wordpress as my content management system because it allows you to quickly create a website with no HTML or other coding experience. The steps to setup a new website are pretty much the same so I put together a Wordpress website creation checklist to help me remember to do everything. I’ve made this checklist into an Excel template which you can download for free by clicking the link below.

 

 

Two skills I encourage everyone to learn that I’ve found invaluable in my career is how to program VBA macros and how to build a simple website. In fact, in one of my most recent projects these skills were used hand-in-hand. I built the majority of the Observation Wheel database with an Excel spreadsheet and a few custom macros. Every row in my sheet contained all the information for a single observation wheel. I then had the macro export the data to Microsoft Word in the HTML format I needed for the site. I then simply copied and pasted that text into Wordpress and published it. I created pages for over eighty dfferent observation wheels almost instantly!

 

There are countless positive benefits for learning how to make a website. I’ve included a link to all the resources I use within the template and I've tried to include as many tips as I could think of. I begin with registering a domain name and take you all the way through setting up an email list. Download my Wordpress website creation checklist below and please feel free to ask me any questions you may have!

 

Wordpress Website Creation Checklist.xls download

Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!

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?

Visit me at Excel Spreadsheets Help and leave a comment. Would love to see you there!