101 Free Excel Templates to Organize Your Life and Business

101 Free Excel Templates to Organize Your Life and Business

ms-excel-guide

A well-made spreadsheet can help in almost any part of life and business. From budgeting and workout planning, to debt tracking and project management. But unless you’re a pro with Excel, creating a spreadsheet that does exactly what you want, isn’t easy.

That’s where templates come in. Using a template takes the hard work out of creating a spreadsheet that works perfectly. This allows you to get straight to tracking your data, and making better decisions based on those insights.

And as luck should have it, for a limited time only, you can download 101 high quality Excel templates for free.

Download 101 Excel Templates For Free!

free Excel Templates

The templates included in this free download cover everything including:

  • Event organizing
  • Budgeting (for work and home)
  • Expense tracking
  • Wedding planning
  • Sales reports and balance sheets
  • Inventory lists
  • Calendars
  • Project tracking charts
  • Invoices
  • Checklists
  • Health and workout trackers
  • Job application logs
  • Debt and savings trackers
  • Meal planners

And much more.

Rest assured, these are quality, well-designed templates that we’ve personally checked out, and we really wouldn’t want you to miss out on this offer. So, claim them today, save them to your computer, and they’re yours to use whenever you like!

Want in? Simply click here to download 101 free Excel templates from TradePub. You will be required to complete a short form to access the templates, but it’s well worth the two minutes of effort!

Read the full article: 101 Free Excel Templates to Organize Your Life and Business

MakeUseOf


Be an Expert Number Cruncher With This Best Selling Excel Bootcamp

Data plays a vital role in sales, finance, and countless other sectors. Yet relatively few people even know how to work with a simple spreadsheet. If you want to stand out from the crowd, the 2019 Microsoft Excel Bootcamp Bundle can help. Through five courses and over 78 hours of video tutorials, this bundle can turn anyone into a number-crunching expert. You can get the training now for just $ 39 at MakeUseOf Deals.

Master Microsoft Excel

If you work behind a desk, you have probably met Microsoft Excel before. But without guidance, it’s easy to miss some of the most powerful features.

This bundle helps you learn the software inside out, with hours of hands-on tutorials from expert instructors. You don’t need any prior knowledge to get started, and the courses teach you valuable workplace skills.

The first track covers all the fundamentals. You learn how to navigate Excel, create a workbook, format your data, use formulas, and design eye-catching graphs.

The Advanced course shows you a variety of time-saving techniques, along with powerful functions and macros. You can also take a deep dive into PivotTables, learn about VBA programming, and discover how Excel can provide key business insights.

You can learn at your own pace, and each course comes with a certificate of completion — a great addition to any résumé.

78 Hours of Training for $ 39

They’re worth $ 1,725, but you can grab all five courses now for only $ 39 with lifetime access included.

Read the full article: Be an Expert Number Cruncher With This Best Selling Excel Bootcamp

MakeUseOf


This $11 Course Helps You Master Microsoft Excel

From engineering to finance, there are countless jobs that involve working with spreadsheets, formulae and charts. Yet how many of us have really mastered these number-crunching skills? Microsoft Excel from Beginner to Advanced is a four-part course that brings you up to speed. Through 15 hours of tutorials, you learn the software inside out and earn certification. You can get the course now for $ 10.99 via MakeUseOf Deals.

In-Depth Training

For most people, Microsoft Excel is the spreadsheet software. It’s an app you open when you need to check the company rota or add up a few bills. But Excel is capable of so much more.

This training helps you unlock the full power of Excel and gain useful professional skills. The videos start from scratch, so you don’t need any previous experience — but you will find yourself making quick progress.

Along the way, you discover how to analyze large data sets and create effective visualizations. The training also shows you how to create and format spreadsheets, build dynamic reports using PivotTables, and automate daily tasks using macros and VBA.

These skills are vital if you plan to work in finance, sales, marketing, or science — although they will make any résumé stand out. To prove your knowledge, you can claim a certificate of completion at the end of this course.

15 Hours for $ 10.99

It’s worth $ 149.99, but you can grab this Excel training now for just $ 10.99 — and you can use code MERRY15 for an extra 15% off.

Read the full article: This $ 11 Course Helps You Master Microsoft Excel

MakeUseOf


Become a Certified Excel Expert with this $19 Course

When it comes to organizing and analyzing data, Excel is one of the most powerful tools available. But working with this number-crunching beast can be daunting. The Microsoft Excel Diploma Master Class helps you overcome the learning curve and become a certified expert, with 12 hours of live training. You can grab the course now for $ 19 at MakeUseOf Deals.

Essential Live Instruction

If you want to work in sales, marketing, or finance, it’s pretty essential to know your way around Excel. This course helps you master the software, and prove your knowledge to potential employers.

The training is spread over one month, with three hours of content per week. You learn through live tutorials with experienced instructors, and the lessons start from scratch.

Along the way, you discover how to create workbooks, perform simple calculations, automate your work with macros, and format large data sets. You also learn to use tables, charts, and many other features. Alongside the lessons, you get access to tests, Q&A sessions, and downloadable workbooks.

By the end of the month, you should feel confident to handle anything your boss could throw at you. Just as importantly, you will come away with a CPD certificate — a great addition to your résumé.

12 Hours of Training for $ 19

If you need to learn Excel for work, this course offers great value. It’s worth $ 395, but you can become a student now for just $ 19.

Read the full article: Become a Certified Excel Expert with this $ 19 Course

MakeUseOf


How to Send Emails From an Excel Spreadsheet Using VBA Scripts

To send emails from Microsoft Excel only requires a few simple scripts. Add this functionality to your spreadsheets and you can really enhance how much you can accomplish in Excel.

Unlock the “Essential Microsoft Office Shortcuts” cheat sheet now!

This will sign you up to our newsletter

Enter your Email

We’ve covered a lot of great Excel macros, which can accomplish the same things VBA scripts can, but without the need for programming knowledge. But there are many advanced things you can only do with VBA, like creating a spreadsheet report with all of your PC information.

Why Send Email From Excel?

There are a lot of reasons why you might want to send an email from inside Microsoft Excel.

Maybe you have staff that updates documents or spreadsheets on a weekly basis, and you’d like to receive an email notification of when those updates are done. Or you might have a spreadsheet of contacts and you want to send one email to all of them at once.

You’re probably thinking that scripting an email broadcast from Excel is going to be complicated. That’s not the case at all.

The technique in this article will make use of a feature that’s been available in Excel VBA for a long time, Collaboration Data Objects (CDO).

cdo in excel

CDO is a messaging component used in Windows since very early generations of the OS. It used to be called CDONTS, and then with the advent of Windows 2000 and XP, was replaced with “CDO for Windows 2000”. This component is already included in your VBA installation within Microsoft Word or Excel and is ready for use.

Using the component makes sending emails from within Windows products with VBA extremely easy. In this example, you’ll use the CDO component in Excel to send out an email that will deliver the results from a specific Excel cell.

Step 1: Create a VBA Macro

The first step is to go to the Excel Developer tab.

Inside the Developer tab, click on Insert in the Controls box, and then select a command button.

adding button in excel

Draw it into the sheet and then create a new macro for it by clicking on Macros in the Developer ribbon.

add macro for command button

When you click the Create button, it’ll open the VBA editor.

Add the reference to the CDO library by navigating to Tools > References in the editor.

adding references in excel

Scroll down the list until you find Microsoft CDO for Windows 2000 Library. Mark the checkbox and click OK.

adding cdo for windows reference

When you click OK, make note of the name of the function where you’re pasting the script. You’ll need it later.

Step 2: Set Up the CDO “From” and “To” Fields

To do this, you first need to create the mail objects and set up all of the fields that are necessary to send the email.

Keep in mind that while many of the fields are optional, the From and To fields are required.

Dim CDO_Mail As Object Dim CDO_Config As Object Dim SMTP_Config As Variant Dim strSubject As String Dim strFrom As String Dim strTo As String Dim strCc As String Dim strBcc As String Dim strBody As String  strSubject = "Results from Excel Spreadsheet" strFrom = "rdube02@gmail.com" strTo = "rdube02@gmail.com" strCc = "" strBcc = "" strBody = "The total results for this quarter are: " & Str(Sheet1.Cells(2, 1))

The cool thing about this is that you can create up any string you want to customize a full email message and assign it to the strBody variable.

Piece together components of the message by using the & string to insert data from any of the Microsoft Excel sheets right into the email message, just like shown above.

Step 3: Configure CDO to Use an External SMTP

The next section of code is where you will configure CDO to use any external SMTP server to send the email.

This example is a non-SSL setup through Gmail. CDO is capable of SSL, but that’s outside the scope of this article. If you need to use SSL, this advanced code in Github can help.

Set CDO_Mail = CreateObject("CDO.Message") On Error GoTo Error_Handling  Set CDO_Config = CreateObject("CDO.Configuration") CDO_Config.Load -1  Set SMTP_Config = CDO_Config.Fields  With SMTP_Config  .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"  .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1  .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com"  .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25  .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True  .Update End With  With CDO_Mail  Set .Configuration = CDO_Config End With

Step 4: Finalize the CDO Setup

Now that you’ve configured the connection to the SMTP server for sending the email, all you have to do is fill in the appropriate fields for the CDO_Mail object, and issue the Send command.

Here is how you do that:

CDO_Mail.Subject = strSubject CDO_Mail.From = strFrom CDO_Mail.To = strTo CDO_Mail.TextBody = strBody CDO_Mail.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send  Error_Handling: If Err.Description <> "" Then MsgBox Err.Description

There won’t be any pop-up boxes or security alert messages, which can happen when you resort to using the Outlook mail object.

CDO simply puts together the email and utilizes your SMTP server connection details to fire off the message. It’s the easiest way to incorporate email into Microsoft Word or Excel VBA scripts.

To connect your command button to this script, go into the code editor and click on Sheet1 to view the VBA code for that worksheet.

Type the name of the function where you pasted the script above.

connect command button to script

Here’s what the message looked like that I received in my inbox:

excel email received

Note: If you receive an error that reads The transport failed to connect to the server, make sure you’ve entered the correct username, password, SMTP server, and port number in the lines of code listed underneath With SMTP_Config.

Take It Further and Automate the Whole Process

It’s all well and good to be able to send email from Excel at the touch of a button. However, you might want to use this functionality on a regular basis, in which case it would make sense to automate the process.

To do so, you’ll need to make a change to the macro. Head to the Visual Basic Editor and copy and paste the entirety of the code we put together.

Next, select ThisWorkbook from the Project hierarchy.

From the two dropdown fields at the top of the code window, select Workbook and select Open from the Methods dropdown.

Paste the email script above into Private Sub Workbook_Open().

This will run the macro whenever you open up the Excel file.

Next, open up Task Scheduler.

You’re going to use this tool to ask Windows to open up the spreadsheet automatically at regular intervals, at which point your macro will be initiated, sending the email.

create basic task in task scheduler

Select Create Basic Task… from the Action menu and work your way through the wizard until you reach the Action screen.

Select Start a program and click Next.

select program task scheduler

Use the Browse button to find Microsoft Excel’s location on your computer, or copy and paste the path into the Program/script field.

Then, enter the path to your Microsoft Excel document into the Add arguments field.

Complete the wizard, and your scheduling will be in place.

It’s worth running a test by scheduling the action for a couple of minutes in the future, then amending the task once you can confirm that it’s working.

Note: You may have to adjust your Trust Center settings to ensure that the macro runs properly.

To do so, open the spreadsheet and navigate to File > Options > Trust Center.

From here, click Trust Center Settings, and on the next screen set the radio dial to Never show information about blocked content.

Make Microsoft Excel Work for You

Microsoft Excel is an incredibly powerful tool, but learning how to get the most out of it can be a little intimidating. If you want to truly master the software, you’ll need to be comfortable with VBA, and that’s no small task.

However, the results speak for themselves. With a little VBA experience under your belt, you’ll soon be able to make Microsoft Excel perform basic tasks automatically, giving you more time to concentrate on more pressing matters.

It takes time to build expertise with VBA, but you’ll soon see the fruits of your labors if you can stick with it.

One great place to start is our authoritative tutorial on using VBA in Excel. Once you’re done with that, this simple script to send emails from Excel will feel like child’s play.

Read the full article: How to Send Emails From an Excel Spreadsheet Using VBA Scripts

MakeUseOf


3 Crazy Excel Formulas That Do Amazing Things

Microsoft Excel is one of the most powerful spreadsheet tools, with an impressive collection of built-in tools and features. In this article, you’ll learn how powerful Excel formulas and conditional formatting can be, with three useful examples.

Unlock the “Essential Microsoft Office Shortcuts” cheat sheet now!

This will sign you up to our newsletter

Enter your Email

Digging Into Microsoft Excel

We’ve covered a number of different ways to make better use of Excel, such as using it to create your own calendar template or using it as a project management tool.

Much of the power lies behind the Excel formulas and rules that you can write to manipulate data and information automatically, regardless of what data you insert into the spreadsheet.

Let’s dig into how you can use formulas and other tools to make better use of Microsoft Excel.

Conditional Formatting With Formulas

One of the tools that people don’t use often enough is Conditional Formatting. If you’re looking for more advanced information on conditional formatting in Microsoft Excel, make sure to check out Sandy’s article on formatting data in Microsoft Excel with conditional formatting.

With the use of Excel formulas, rules, or just a few really simple settings, you can transform a spreadsheet into an automated dashboard.

To get to Conditional Formatting, you just click on the Home tab, and click on the Conditional Formatting toolbar icon.

conditional formatting in excel

Under Conditional Formatting, there are a lot of options. Most of these are beyond the scope of this particular article, but the majority of them are about highlighting, coloring or shading cells based on the data within that cell.

This is probably the most common use of conditional formatting—things like turning a cell red using less-than or greater-than formulas. Learn more about how to use IF statements in Excel.

One of the lesser used conditional formatting tools is the Icon Sets option, which offers a great set of icons you can use to turn an Excel data cell into a dashboard display icon.

conditional formatting icons

When you click on Manage Rules, it’ll take you to the Conditional Formatting Rules Manager.

Depending on the data you selected before choosing the icon set, you’ll see the cell indicated in the Manager window, with the icon set you just chose.

conditional formatting rules manager

When you click on Edit Rule, you’ll see the dialog where the magic happens.

This is where you can create the logical formula and equations that will display the dashboard icon you want.

This example dashboard will show time spent on different tasks versus budgeted time. If you go over half the budget, a yellow light will display. If you’re completely over budget, it’ll go red.

setting conditional formatting rules

As you can see, this dashboard shows that time budgeting isn’t successful.

Almost half of the time is spent way over the budgeted amounts.

time budgeting dashboard

Time to refocus and better manage your time!

1. Using the VLookup Function

If you’d like to use more advanced Microsoft Excel functions, then here’s another one for you.

You’re probably familiar with the VLookup function, which lets you search through a list for a particular item in one column, and return the data from a different column in the same row as that item.

Unfortunately, the function requires that the item you’re searching for in the list is in the left column, and the data that you’re looking for is on the right, but what if they’re switched?

In the example below, what if I want to find the Task that I performed on 6/25/2018 from the following data?

using vlookup in excel

In this case, you’re searching through values on the right, and you want to return the corresponding value on the left – opposite the way VLookup normally works.

If you read Microsoft Excel pro-user forums you’ll find a lot of people saying this isn’t possible with VLookup, and that you have to use a combination of Index and Match functions to do this. That’s not entirely true.

You can get VLookup to work this way by nesting a CHOOSE function into it. In this case, the Excel formula would look like this:

"=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)"

What this function means is that you want to find the date 6/25/2013 in the lookup list, and then return the corresponding value from the column index.

In this case, you’ll notice that the column index is “2”, but as you can see the column in the table above is actually 1, right?

vlookup excel formula

That’s true, but what you’re doing with the “CHOOSE” function is manipulating the two fields.

You’re assigning reference “index” numbers to ranges of data – assigning the dates to index number 1 and the tasks to index number 2.

So, when you type “2” in the VLookup function, you’re actually referring to Index number 2 in the CHOOSE function. Cool, right?

vlookup results

So, now the VLookup uses the Date column and returns the data from the Task column, even though Task is on the left.

Now that you know this little tidbit, just imagine what else you can do!

If you’re trying to do other advanced data lookup tasks, be sure to check out Dann’s full article on finding data in Excel using lookup functions.

2. Nested Formula to Parse Strings

Here’s one more crazy Excel formula for you.

There may be cases where you either import data into Microsoft Excel from an outside source consisting of a string of delimited data.

Once you bring in the data, you want to parse that data out into the individual components. Here’s an example of name, address and phone number information delimited by the “;” character.

delimited data

Here’s how you can parse this information using an Excel formula (see if you can mentally follow along with this insanity):

For the first field, to extract the leftmost item (the person’s name), you would simply use a LEFT function in the formula.

"=LEFT(A2,FIND(";",A2,1)-1)"

Here’s how this logic works:

  • Searches the text string from A2
  • Finds the “;” delimiter symbol
  • Subtracts one for the proper location of the end of that string section
  • Grabs the leftmost text to that point

In this case, the leftmost text is “Ryan”. Mission accomplished.

3. Nested Formula in Excel

But what about the other sections?

There may be easier ways to do this, but since we want to try and create the craziest Nested Excel formula possible (that actually works), we’re going to use a unique approach.

To extract the parts on the right, you need to nest multiple RIGHT functions to grab the section of text up until that first “;” symbol, and perform the LEFT function on it again. Here’s what that looks like for extracting the street number part of the address.

"=LEFT((RIGHT(A2,LEN(A2)-FIND(";",A2))),FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))),1)-1)"

It looks crazy, but it’s not hard to piece together. All I did is took this function:

RIGHT(A2,LEN(A2)-FIND(";",A2))

And inserted it into every place in the LEFT function above where there’s an “A2”.

This correctly extracts the second section of the string.

Each subsequent section of the string needs another nest created. So now you just take the crazy “RIGHT” equation you had created for the last section, and then pass that into a new RIGHT formula with the previous RIGHT formula pasted into itself wherever you see “A2”. Here’s what that looks like.

(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))))))

Then you take THAT formula, and place it into the original LEFT formula wherever there’s an “A2”.

The final mind-bending formula looks like this:

"=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),FIND(";",(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),1)-1)"

That formula correctly extracts “Portland, ME 04076” out of the original string.

parsed string

To extract the next section, repeat the above process all over again.

Your Excel formulas can get really loopy, but all you’re doing is cutting and pasting long formulas into itself, make long nests that actually work.

Yes, this meets the requirement for “crazy”. But let’s be honest, there is a much simpler way to accomplish the same thing with one function.

Just select the column with the delimited data, and then under the Data menu item, select Text to Columns.

This will bring up a window where you can split the string by any delimiter you want.

splitting text

In a couple of clicks you can do the same thing as that crazy formula above… but where’s the fun in that?

Getting Crazy With Microsoft Excel

So there you have it. The above formulas prove just how over-the-top a person can get when creating Microsoft Excel formulas to accomplish certain tasks.

Sometimes those Excel formulas aren’t actually the easiest (or best) way to accomplish things. Most programmers will tell you to keep it simple, and that’s as true with Excel formulas as anything else.

If you really want to get serious with using Excel, you’ll want to read through our beginner’s guide to using Microsoft Excel. It has everything you need to start boosting your productivity with Excel.

Read the full article: 3 Crazy Excel Formulas That Do Amazing Things

MakeUseOf