Excel Spreadsheet Icon

How to Convert Excel to Google Sheets

Microsoft Excel used to be your only option for spreadsheet software, but not anymore. You can move all of your Excel files to a digital format that is easy to use and updates in real-time, as well as being free to use. We’re talking about how to convert Excel to Google Sheets, and we have everything you need to know.

Google offers a number of great applications for free, including Google Sheets. While the applications are not as powerful as what you receive with the Microsoft options, when you don’t want to spend money on the program and only need basic features Google is the way to go.

Related Post: Google Sheets Vs. Excel: How They Differ, Strengths and Weaknesses

Google Sheets gives you several excellent features, so you need to know how to convert Excel to Google Sheets. Here is the answer.

How to Convert Excel to Google Sheets

Screen capture from Google Sheets

When looking at how to convert Excel to Google Sheets you thankfully don’t need a workaround or any special software. If you already have Google Sheets that’s all you’ll need to convert the Microsoft file to the Google file.

If you just received the Sheets file and are a native Excel user, it is easy to open up the application and to begin using the information.

Signing Up with Google Sheets

If you are a native Microsoft application user and you haven’t used it before this is the one mandate you have in order to convert Excel to Google Sheets.

Chances are you have a Google account (through Gmail or YouTube or something). If you don’t, go over to Gmail.com and create a free account. Once you have your Google account login head over to Google.com/sheets/about.

On the home page click “Go to Google Sheets.” This will instantly take you over to the Sheets application.

Screen capture of Google.com/sheets/about

If you’ve ever downloaded a Sheets or Excel file in the past to your Gmail account the information will automatically be displayed. You can select the file from the lower portion of your screen, or you can create a new sheet, produce a “To-Do List,” an annual budget, or select one of the other templates.

Converting Excel to Google Sheets with File in Google Drive

If you received the Excel file in your Gmail account, you’ll already have the file saved on your Google Drive (which is the cloud service Google has packaged in with its Google accounts).

With the file in your Google Drive you’ll want to click on the selection of dots in the upper right corner of your Google account window.

Now, choose “Google Drive.”

From here, select the Microsoft Excel file listed inside of your Google Drive account. A pop-up window will appear, asking what you want to open the file with.

Select “Google Sheets.” The spreadsheet will now convert over to the Sheets format.

Convert When You Upload

Now, you may not want to open Google Sheets and open the file just to convert it. Thankfully, Google allows you to instantly convert the file to Google Sheets without ever opening the file in the first place.

Head over to the Google Sheets home page, then click on the “Open File Picker” icon, which looks like a file folder and is located under your profile icon.

When the “Open a File” window opens choose “Upload,” then select the Microsoft Excel file you want to upload and convert.

Click “Open” and the file will now upload to your account and automatically convert over to Google Sheets.

File Formats You Can Convert (And Some You Can’t)

The majority of Microsoft Excel files can be converted without a problem, so when looking over the how to convert Excel to Google Sheets instructions, you’ll find it works with nearly all the files you have. The Excel file you can convert include:

.xls, .xlsx, .xlsm, .xlt, .xltx, .xltm, lods, .csv, .tsv, .txt, .tab.

Now, there are a few files you cannot convert over to Microsoft Excel. First, if the file is password protected you won’t be able to convert it as Google does not have the right to access the Microsoft password-protected file.

Next, you can’t convert a Macros file, although you can upload the macro file into the Apps Script application and then convert it from there.

If you have any Excel charts embedded into PowerPoint or Word you can’t convert the information over to Google Sheets. You also can’t convert any sheets that are linked to your Excel file (such as a website linked within the file that then leads to an Internet saved spreadsheet. If you want to convert that file you must download it.

Exporting Sheets File Back to Excel

Screen capture of Microsoft Excel

After you have followed the how to convert Excel to Google Sheets instructions and have worked within Google Sheets to edit or tweak information, you may find the need to export the file back to Microsoft Excel for later use. It is a simple process that you can do as soon as you are done editing the file in Google Sheets.

Export the File as Excel

When you are in Google Sheets and you’re ready to export the file out for Microsoft use, click on the “File” button at the top of the application window, now choose “Download As” from the list of options appearing in the pull-down menu.

You will now be able to select the desired file format you want to export the file as. Select the .xlsx file format and click “OK.” The file will now export out to your computer in the selected Microsoft Excel format.

If you’d like, you can also export it as several other files, including PDF. The ability to save as Excel and to open Excel files within Google Sheets makes it possible to jump back and forth between the software. This way, no matter who you are working with or what application they are on, you will always be able to run and view the information whenever necessary.

Why Google Sheets?

Before you look into how to convert Excel to Google Sheets you may wonder why someone would use Google Sheets to begin with? Microsoft has long been the king of inner-office applications, so why would someone make the shift now?

Chances are, the person moving from Excel to Sheets didn’t become fed up with Excel. It very much is the most powerful and useful spreadsheet software out there (and it’s not all that close). However, not everyone needs powerful spreadsheet tools. They might not need all the insights and coding features. They may just need cells to help maintain information. When this is the case there’s no reason to purchase Microsoft Office, which is an expensive application suite to keep up to date.

Instead, it is better for the individual to use Google Sheets. Google Sheets is free to use, converts in and out of Excel files, and can do several things Microsoft can do. Plus, it can easily be uploaded to other Google applications and also emailed from Google Drive. So if you’re wondering why someone would use Google Sheets (or why you might want to give it a try), it’s because it’s free, and yet still a quality application.

A final benefit is you don’t need to install anything onto your computer if you don’t want to. It is possible to download the application to work offline, but if you’re looking to save hard drive space on your computer, Google Sheets is an excellent option.

Work with Others in Real Time

One of the best features Google brings to the table (and a major reason why individuals like to use Google Sheets) is because you can work with others in real time through the Google Drive application.

If you send someone the Google Sheets file and you both have it within a shared space, both of you will be able to view the file. Additionally, you’ll be able to see that the other individual is accessing the file at the same time. From there, you can make edits with them and adjust whatever is necessary. There may be times where you need to collaborate with someone through Microsoft Sheets and you don’t want to constantly download and email the attachment. With the combination of Google Drive and Google Sheets, you’ll never again need to do this.

Conclusion

A person working on his laptop
image source: pixabay.com

Microsoft Excel remains the king of all spreadsheet applications. However, Google Sheets does offer a number of useful features, not to mention it is free. Because of this, there are many people who are starting to make the switch from the Office program to the Google program. If you received an Excel file and need to either access it for the first time in Sheets, or you have never used Sheets before and you want to know how to open your own file in the software, these are the instructions you need to follow for how to convert Excel to Google Sheets. With this information, you’ll always be able to open an Excel file in Sheets whenever necessary, and then convert it back out should you want.

image of excel showing the addition function

The Data Adds Up: Using the Addition Formula in Excel

Meta: In this article readers will learn the basic addition formula for Microsoft Excel. Users can find examples and a how-to guide for entering formulas themselves and using the sum feature.

The addition formula is one of the basic functions you can perform in Microsoft Excel and other spreadsheet programs. There are several different ways to use the addition formula in Excel and many different times when the formula will come in handy when you are working with data in your spreadsheet.

In the following article, we will discuss the different ways you can enter an addition formula, and when it is appropriate to use the formula. This will include sections on:

  • What is a formula in Excel
  • Basic addition formula
  • Advanced addition
  • The “SUM” function
  • SUM with rows and columns

What Is a Formula in Excel

In Excel, a formula is a way to perform calculations and other operations on your data. There are basic formulas such as addition or subtraction, which we will discuss in this article, and much more complex formulas that make calculations for advanced statistics or engineering problems. In essence, a formula is performing some simple, or complicated, mathematical function.

Any formula that you make in excel will begin with the “=” symbol and are most often entered into a specific cell of your choosing. For example, if you typed “= 5 + 3 + 8” into the cell A1 and pressed enter, what would you expect to get back? If you said 16, you’re exactly right. Pretty easy, no?

Values are not the only thing you can add using the addition formula. Formulas can also contain constants, individual cells (for example, B22) or a range of cells (for example, F2 to F22), functions, and operators.

In Excel, values are not limited to numbers. They can include dates, text or words, and Boolean values.

The Basic Addition Formula

Image of Excel showing formula bar with =2+2 and cell D4 showing 4
Screen capture from Excel: Cell D4 showing the result of the formula “=2+2” the formula is displayed in the formula bar

As we noted above, the basic addition formula is quite simple. Except for the syntax, you probably recognize it from your elementary school math class. In Excel, instead of writing “2 + 2 =”, you write = 2 + 2 and the program will spit back to you the number 4.

You can also write the names of cells into your formula instead of numbers and get the same result. For example, if you typed = A4+B4 into the C4 cell, the program would then do perform the function and give you the result in C4. In this case 4.

The huge advantage to using cell references rather than typing in the data is that if change the data in a cell, the formula will automatically change. This will cut down on making any errors when working with your data.

More Advanced Addition

As noted above, one way to do this is to type in the range of cell references you want. Above we did “=A4+B4” and got 4 back.

If you’re more of a mouse person, the other easy way to do this is the point and click method. Start by typing a “=” into C4. Now click on cell A4. This should show “=A4” in the cell. Then type the “+” sign and click on the cell you want to add to A4. In this example, we will do B4. Cell C4 will now have the completed formula “=A4+B4”.

addition formula for cells shown
Showing formula in cell C4

When you press enter, you will get the result, 4.

Excel screen capture showing c4
After hitting enter in C4, the sum of the selected cells is displayed, the formula shows in the formula bar above

If you need to change the formula, say you want to multiply the cells instead of adding them, double-click the cell containing the formula and change the “+” to “*” and press enter.

The Sum Function

In Excel, you don’t need to use the operator “+” to add together two or more numbers. You can also use what is called the SUM function. As with all formulas, you still start by typing an “=”, but now you type SUM and whatever numbers or cells you want added up.

For example you can type =SUM(2,2) into a cell.

Showing formula for cell C7 and total displayed in that cell

When you press enter you’ll get 4.

You can also type in cell references, for example, =SUM(A1, B1, C1). If you want to do a range of cells, type =SUM(A1:C1).

Showing range addition formula

The Sum Function for Rows and Columns

All of our examples so far have been pretty easy, and you might be wondering, why do we need such an easy formula when you can do it in your head?

Well, the more data you have in your spreadsheet, the longer it will take for you to do the addition by hand. Imagine you’re working in sales, and you have a spreadsheet that keeps track of units of your product you’ve shipped in the past year as well as the units that remain in your warehouse.

You could sit there and calculate the entire inventory by hand, but then what would the point be of recording everything in a spreadsheet?

To cut down on time without creating an elaborate formula to perform addition, you can use the “SUM” function. The SUM function is incredibly useful if you’re like our salesperson who wants to add up her sales from the previous year.

Annual sales shown for how to use the sum function (addition) in excel

In the example, you can see that we have a lot more data than in the earlier examples. By using the SUM function, we can easily add together all the data in a given column. You can do the same thing for a row, or for both rows and columns simply by typing in or clicking on the range you wish to add up.

You can later use this data to create pivot charts, or pivot tables.

Bringing it all Together

In this article, we introduced the addition formula for Excel spreadsheets. We discussed some of the reasons why you might choose to use the addition formula, types of values and references that can be added using the formula, and some of the shortcuts you can use to tell Excel to add data in your spreadsheet.

man looking at tablet showing pivot tables and pivot charts

Pivot Charts: An All-Encompassing Guide

Pivot Charts can help you to take an unorganized set of data and turn it into a clear and concise representation of the information that you’re trying to convey. You can eliminate all unnecessary information and single out key data points in order to better understand specific subcategories of data.

Here, we’re going to look at the most important aspects of Pivot Charts, including:

  • The Basics of Pivot Charts
  • Reasons to use Pivot Charts
  • How to Create a Pivot Chart

What are Pivot Charts?

image of different types of pivot charts printed out with a phone and hands on a table.

Pivot Charts offer a visual representation of relevant data from a set. Most often, charts get data points from associated Pivot Tables instead of from raw data. Pivot Charts give you more flexibility than tables when it comes to layout and aesthetics, however.

You can display information using categories, markers, axes, and other hallmarks of charts and graphs. Excel’s Pivot Chart tool lets you show off data in just about any format except for an XY (scatter plot), stock graph, or bubble chart.

The Difference Between Pivot Charts and Standard Charts

In most respects, Pivot Charts are similar to standard graphs. They help us to visualize data in a way that’s clear, concise, and easy-to-read using simple shapes and labeled markers.

There are, however, some slight differences when creating Pivot Charts that you should be aware of:

  • The Orientation of Rows and Columns:  With traditional charts, you can easily switch row and column orientation by using the Select Data Source dialog box. With Pivot Charts, however, you can click and drag to “pivot” row and column labels.
  • Stylistic Limitations: As previously mentioned, you can’t make Pivot Charts in certain standard chart formats, including scatter plots, stock graphs, and bubble charts.
  • Linked Data Sources: With traditional charts and graphs in Excel, you typically use data directly from worksheet cells. Pivot Charts, however, use information from Pivot Tables to create a simple and user-friendly graph.
  • Changing Data: You can’t change the chart data range that you use in a Pivot Chart by using the traditional Select Data Source dialog box. Instead, you need to alter the associated Pivot Table.
  • Editing formatting: When you refresh a Pivot Chart, layout and style stays the same. Elements such as trendlines, data labels, error bars, and other changes to data sets, however, may be lost.

If you usually deal with standard charts in Excel, then Pivot Charts may take some getting used to. As long as you know what you’re doing, though, making a Pivot Chart takes just minutes.

The Benefits of Pivot Charts

Pivot charts are more than just a colorful way to represent important data. There are a number of reasons to make the switch from standard tables to Pivot Charts when presenting data. If you’re trying to glean information from select cells in your data set, using a Pivot Chart is often less time-consuming. It allows you to eliminate any unwanted categories in one swoop instead of forcing you to go through and highlight individual cells. This also reduces the chance of human error messing up your numbers.

When making a Pivot Chart, the information that you use will come from a Pivot Table. If you need to alter any of the data points in your table, you’ll find that your graph updates automatically, making it easy to tinker with numbers and make quick fixes when necessary. This can also help to save you time and frustration over standard charts.

How to Create a Pivot Chart using a Pivot Table

Excel screen showing where to find the pivot table menu.

To make a Pivot Chart, it’s easiest first to create a Pivot Table. A Pivot Table helps to summarize data from a large set into a smaller table that contains just the essential information.

You can use data from an Excel worksheet as the basis for a PivotTable, or you can import data sets from external sources such as a software database, an Online Analytical Processing (OLAP) cube, or a text file. You can even base a new Pivot Table on an existing file.

Once you’ve created a Pivot Table either manually or using Excel’s Recommended Pivot Table tool, you can use that information to make a Pivot Chart. While both the table and the chart will contain the same data, they present it in different ways.

Often, graphical representations are easier to read, especially when it comes to spotting patterns.

Creating a Pivot Chart from an Existing Pivot Table is Easy.

In just a few easy steps, you can have a graph that’s ready to go for your next major meeting or presentation. Here’s how to create a Pivot Chart using a Pivot Table:

  1. Select a cell that’s within your Pivot Table range.
  2. Go to PivotTable Tools > Analyze > PivotChart
  3. Select the chart type you want from the options available and click OK.
  4. Format your Pivot Table to your liking using different fonts, colors, and styles.

If you don’t have a Pivot Table at the ready, there’s no need to worry. You can make a Pivot Table and a chart at the same time if you want. Excel’s Recommended Charts tool automatically draws up a Pivot Chart and an associated Pivot Table based on raw data.

You can also do this manually:

  1. Select a cell within your worksheet data.
  2. Go to Insert > Pivot Chart > Pivot Chart.
  3. In the dialog popup box, specify your data source and where you want your chart to be placed. You can also choose whether you want to analyze multiple tables.
  4. Press OK, and Excel will add a new worksheet with a blank Pivot Table and Pivot Chart. Go to the Field List to pick out which fields you want to include in your chart.

Conclusion

Pivot Charts can help you to understand complex data sets and make more informed decisions, both in business and in your personal life. By breaking down data, Pivot Charts allow you to better focus on the information that’s important to your enterprise.

By following the steps laid out in this tutorial, you can create attractive visual representations that are sure to get you noticed at your next presentation.

Person using a laptop with excel on screen

HLOOKUP In Excel: Everything You Need to Know

HLOOKUP is a tool that makes it easy to find the information you’re looking for without the hassle. You can search for specific data in any row of a table or spreadsheet quickly and efficiently, giving you the time to focus on more pressing issues. Using HLOOKUP can make your job just a little bit easier when using Excel.

Here, we’re going to go over everything that you need to know about the HLOOKUP function. We’ll be discussing:

  • The basics of the HLOOKUHow to use HLOOKUP
  • How to use HLOOKUP
  • When to use HLOOKUP

Let’s get started!

What is HLOOKUP

HLOOKUP is a handy search function in Excel that allows you to sort through large amounts of data effortlessly. You can isolate the information that you’re interested in based on specific criteria such as dates, numerical values, and text. HLOOKUP works by searching for and retrieving values from the first row in a table. As you may have guessed, the H in HLOOKUP stands for horizontal.

​How HLOOKUP Differs from VLOOKUP

If you’re familiar with using VLOOKUP in Excel, then you shouldn’t have too much trouble picking up the finer points of HLOOKUP. Both are search functions, but while VLOOKUP finds data on a vertical axis, HLOOKUP searches horizontally. The main reason to switch between VLOOKUP and HLOOKUP is the orientation of the data in your table or spreadsheet. If the data you’re looking at is categorized and organized along the horizontal axis, you should use HLOOKUP to find your results.

​How to Use HLOOKUP

HLOOKUP is a worksheet function, allowing you to bring up values directly on the table you’re currently using. The syntax you should use is as follows:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

This formula can be a little bit confusing at first glance, so let’s break it down into its basic components:

  • Lookup_value: The value that the function will search for in the first row of the table. This can be a number, text, or another cell.
  • Table_array: The range of cells you plan to search. You can either highlight cells or type in a reference to an array.
  • Row_index_num: This value is relative to the table_array that you choose. Instead of typing in a cell reference, you should type in the numeric value of where the row lies on your table. For example, you should input the first row as 1, the second row as 2, and so on.
  • Range_lookup: This is the only optional parameter when using HLOOKUP. It allows you to specify if you want Excel to search for an approximate value by typing in TRUE, or an exact value by entering FALSE. When this syntax argument is left out, the formula defaults to TRUE.

It’s best to use the FALSE range_lookup when dealing with text, as you’re looking for an exact result. This also allows you to use wildcard characters when entering your lookup_value. A question mark will let you search for any single text character, while an asterisk can match with a sequence of numbers. If you need to search for actual question marks or asterisks, you can simply add a tilde (~) before the character.

Person using excel HLOOKUP on a laptop
Image via Pixabay

​Common Problems

​While HLOOKUP can be an indispensable tool when used properly, just a single mistake in syntax can cause a major headache. If you’re having trouble getting the formula to work, don’t worry. Plenty of people have problems with Excel search functions, but they’re easy to tweak and troubleshoot so that you get the results you’re looking for. Here are some of the most common problems that Excel users face when it comes to HLOOKUP:

Results return as #N/A: Sometimes, HLOOKUP fails to find a match for a lookup_value. This is often due to the range_lookup that you use. If TRUE, an #N/A error has probably occurred because the smallest value in the lookup row is greater than the lookup_value. If false, then the error occurs because a match wasn’t found in the specified cells.

Results return as #REF!: This error generally means that the row_index_number is larger than the number of rows in your table, leaving the HLOOKUP formula without a proper reference.

Results return as #VALUE!: This error may occur because your row_index_number is less than one or nonnumeric. You may also see a #VALUE! error thanks to a typo in the [range_lookup] argument, since this parameter only recognizes TRUE or FALSE.

​Examples of Formulas

​There are plenty of useful applications for the Excel HLOOKUP function. It’s an invaluable tool in both business and at home that can help you keep track of large quantities of valuable information.

The formula is most commonly used in database maintenance, as it makes it easy to pinpoint specific points of data or information from a certain timeframe. HLOOKUP is an invaluable tool both in a business and home setting.

  • ​Get an approximate match: =HLOOKUP(lookup_value, table_array, row_index_num)
  • ​For an exact match: =HLOOKUP(lookup_value, table_array, row_index_num, FALSE)
  • ​Get the first text value: =HLOOKUP(“*”,range,1,FALSE)
  • ​Display “Not Found” in results instead of an error message: =IFNA(HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]), “Not Found”)
  • ​Using HLOOKUP from another workbook or spreadsheet: =HLOOKUP(lookup_value, sheetname!table_array, row_index_num, [range_lookup])

If you use a separate database in your HLOOKUP formula, it’s important to remember formatting. Worksheets with names containing spaces or non-alphabetical characters should be enclosed in single quotation marks. If you’re referencing another open workbook, you should include the name in square brackets. Closed workbooks need to have their entire path specified.

​Conclusion

Whether you’re an Excel expert or are just getting started, search functions can help you to complete your projects on schedule. Tools such as HLOOKUP make it easy to sift through data to find the information that’s relevant to your work. You can use HLOOKUP to help streamline operations at home and in the workplace.

We hope that our tutorial has helped you to better understand the many uses of the HLOOKUP function in Excel. By following the steps we’ve laid out, you can save time and frustration when searching through important databases.

person reading book with desktop

The Best Excel Book to Purchase from Amazon

Microsoft Excel is supposed to make your life easier; but if you find using it brings you nothing but frustration, it may be time to find a good book that can teach you the ropes of using Excel. We’re here to help with that.

We have compiled a list of the ten best Excel books that can help you use Excel with ease and to its full potential.

Microsoft Excel Books FAQs

software books

Image via Pixabay

1. What Is Microsoft Excel?

Microsoft Excel is a digital spreadsheet software program used to organize, store, format, calculate, and manipulate data. Excel is part of the Microsoft Office Suite and is compatible with other Office applications.

2. Who Needs Microsoft Excel Books?

Almost anybody can benefit from Excel books: from total beginners to experts. Excel is such an efficient way to organize and track data that those who have never used it could surely benefit from learning how.

Even people who have used Excel for years are probably not using it to its full potential and could benefit from Excel books aimed at users with Excel experience who are looking to learn about more advanced concepts.

3. What Should I Look for in an Excel Book?

What to look for is largely subjective. First you want to make sure that the book you choose matches your skill level. If you have never used Excel before, don’t choose a book that is teaching you how to integrate Excel with Microsoft Power BI.

If you are a visual learner, you will want a book with a lot of pictures, charts, and/or graphs. Conceptual learners may benefit more from a book that leans heavily on examples.

An important feature of any book, no matter your skill level or learning style, is well organized and structured information.

man sitting while reading a abook near his laptop

Image via Pexels

4. Where Can I Buy the Best Excel Books?

You can find the best Excel books in most bookstores such as Barnes & Noble or B.A.M.. Amazon.com is a great resource for buying new, used, and digital books. Every Excel book featured on our list is available on Amazon.com.

How We Reviewed

man wearing gray crew neck shirt holding a book

Image via Pexels

We evaluated the Excel books on our list by considering the organization of the information provided, the expertise of the authors, how clearly the information was conveyed, and by the amount of information covered.

We wanted to present a diverse list of the best Excel books so that newbies and experts alike could find what they are looking for.

What We Reviewed

  • Excel 2019 Bible 1st Edition
  • Excel Formulas & Functions for Dummies
  • Slaying Excel Dragons: A Beginners Guide to Conquering Excel’s Frustrations and Making Excel Fun
  • Excel 2019 All-in-One for Dummies
  • Excel Dashboards and Reports
  • Microsoft Business Intelligence Tools for Excel Analysts
  • Excel Macros for Dummies
  • Building Financial Models with Microsoft Excel: A Guide for Business Professionals
  • Excel: QuickStart Guide – From Beginner to Expert
  • Power Pivot and Power BI: The Excel User’s Guide

Excel 2019 Bible (1st Edition, by Michael Alexander)

Excel bible 2019

Image via Amazon

Summary

First on our list of the best Excel books is The Excel 2019 Bible. This 1120-page reference book is written by Michael Alexander, a Microsoft MVP and the author of several advanced business analysis books.

This book is for all types of Excel users; total beginners or novices using Excel for business or at home will find this book to be a valuable resource.

Also, this book is a great introduction to the new features and tools of Excel 2019 and will teach readers to incorporate new templates, use and apply formulas, create pivot tables, analyze data, create functional spreadsheets, and much more.

Gain a comprehensive overview by reading the Excel 2019 Bible cover to cover or finding the chapter or chapters that give you the specific information you are looking for. This book is available in paperback and digital format.

Excel Formulas & Functions for Dummies (5th edition, by Ken Bluttman)

 Formulas & Functions For Dummies

Image via Amazon

Summary

This 400-page book from Ken Blutton is one of the best Excel books from the popular “For Dummies” line. Best for beginner to intermediate Excel users, this book focuses on the formulas and functions aspect of Excel.

Get the most out of Excel by learning how to properly utilize formulas and gain access to step-by-step instructions on Excel’s 150 most-used functions.

Examples of specialized functions help you learn the material and understand how to apply it when using Excel. This book is available in paperback and digital formats.

Excel 2019 All-in-One for Dummies(1st edition, by Greg Harvey)

All-in-One For Dummies

Image via Amazon

Summary

Yet another one of the best Excel books in the “For Dummies” line, this book has condensed 8 of the “For Dummies” Excel books into 815 pages, including Excel Basics, Worksheet Design, Formulas and Functions, Worksheet Collaboration and Review, Charts and Graphics, Data Management, Data Analysis, and Macros and VBA.This book is for beginner to intermediate Excel users. Beginners can learn about importing data, building and working with worksheets, creating formulas and pivot tables.

Advanced Excel users can learn about worksheet sharing and auditing, error trapping, macros, charting data, and integrating Excel with Microsoft Power BI. This book is available in paperback and digital formats.

Slaying Excel Dragons: A Beginners Guide to Conquering Excel’s Frustrations and Making Excel Fun (by Mike Excelisfun Girvin)

Slaying  Dragons

Image via Amazon

Summary

This is one of the best Excel books for total beginners—those who view Excel as an opponent to be conquered and then become friends with it.

In 532 pages, authors Mike Girvin and Bill Jelen will teach you all the basics; learn about rows, columns, cells, subtotaling, sorting, pivot tables, and more.Over 1,100 screenshots will come as a relief to visual learners. It should be noted that this book came out in 2011 and is therefore a little outdated.

It still contains a ton of useful information for the total beginner, but anyone hoping to learn about new features of the latest version of Excel won’t find them here. This book is available in paperback and digital formats.

Excel Dashboards and Reports(2nd edition, by Michael Alexander)

Dashboards and Reports

Image via Amazon

Summary

This is one of the best Excel books for people who already have experience with Excel and want more specific instructions on using Excel dashboards and creating Excel reports.

It should be noted that, in regards to Excel dashboards, this book is more of an introduction to the concept and may not go deep enough for more experienced Excel users. 

It should also be noted that this book came out in 2013, so it will not have the most up-to-date information; but readers will still find a lot of useful information here. This 432-page book from Michael Alexander is only available in paperback.

Excel Macros For Dummies(2nd edition, by Michael Alexander)

Excel Macros For Dummies

Image via Amazon

Summary

This is the second book by Michael Alexander on our list of the best Excel books and another one from the “For Dummies” line. As you may have guessed from the titles, this book will teach you everything you need to know about Excel Macros in its 312-pages.Published in 2017, you can expect the information to be fairly up to date. This book will demonstrate the time-saving power of Excel macros and will introduce you to over 70 of the most-used customizable Excel macros.

Like most books in the “For Dummies” line, the information is well organized, making it easy to find what you are looking for, and an icon system provides visual cues for important information. This book is available in paperback and digital formats.

Microsoft Business Intelligence Tools for Excel Analysts (1st edition, by Michael Alexander

Microsoft Business Intelligence Tools for Excel Analysts

Image via Amazon

​​​Summary

Here we will see Michael Alexander’s work again—this guy must know what he’s talking about! Mr. Alexander has teamed up with Jared Decker, Bernard Wehbe, and John Walkenbach to bring this book aimed at business analysts and managers who need to learn more about Microsoft Business Intelligence Tools.

This will help analysts learn skills such as database management, query design, data integration, and multidimensional reporting, among other things.This book provides information on using BI tools such as Power Pivot, Power Query, and Power View. It should be noted that this book will be most useful for those dealing with big data tables. This 384-page book is available in paperback and digital formats.

Building Financial Models with Microsoft Excel: A Guide for Business Professionals (by K. Scott Proctor)

Building Financial Models with Microsoft Excel

Image via Amazon

Summary

The next entry on our list of the best Excel books comes from K. Scott Proctor, is part of the Wiley Financial Series, and is a good option for business professionals who want a refresher on financial models or students who are learning this material for the first time. Visual learners will love the fact that this book includes many picture examples.

While this book was published in 2009 and will not have the most up-to-date information, it will still be very useful for those looking for information on how to build financial models within Excel. This book is available in paperback, hardcover, and digital formats.

Excel: QuickStart Guide – From Beginner to Expert (by William Fischer)

Excel QuickStart Guide - From Beginner to Expert

Image via Amazon

Summary

This book is a brief 100-page guide from William Fischer and is advertised as a good option for newbies or veterans. This book will covers basic Excel information to get you started, including functions, formulas, shortcuts, and macros.

While the title says this book can take you from beginner to expert, nobody is becoming an Excel expert in a mere 100 pages.This will not be the best Excel book for visual learners, as there are no pictures to accompany the text. This book is available in paperback and digital formats.

At the time of this writing, this book is available on Kindle unlimited, which means Amazon Prime members can see this book for free.

Power Pivot and Power BI: The Excel User’s Guide (2nd edition, by Rob Collie)

Power Pivot and Power BI

Image via Amazon

Summary

Last on our list of the best Excel books is this 308-page guide from Rob Collie and Avi Singh. Full of color images, this book is less likely than others to have your eyes glazing over and will be great for visual learners.

This book is for those looking to learn more about Microsoft Power BI, including Power Pivot and Power Query.Learn the difference between calculated columns and measures, how to reuse formulas across reports of different shapes, how to use Power Query to enhance your Power Pivot models, how to write Dax in Power Pivot, and so much more.

This book passes on information in a conversational tone that is easy to follow. Owners of this book swear by it, and will tell you they pick it up over and over again. This book is available in paperback and digital format.

The Verdict

woman reading a book

Image via Flickr

If we were to name one of these books as the overall best Excel book, it would have to be Excel 2019 All-in-One For Dummies. This book is really eight “For Dummies” books all wrapped into one.

It is great for those new to Excel as well as those looking for more specialized materials. As with most books from this line, the information is very well organized and structured well.

We also like the use of the icon system, which provides a visual cue for you to pay extra attention to important notes and concepts. The affordable price of this book is just the icing on the cake.That concludes our list of the ten best Excel books. We hope we have been helpful in your search for a quality Excel reference material and wish you the best of luck in all your future Excel endeavors!

Featured Image via Pexels

Person in front of laptop with excel display

How to Create a Database in Excel

A database in Microsoft Excel makes it easy to input formulas and organize information. This is beneficial when doing everything from staying on top of business numbers to grading term papers. Whatever the reason might be, if you’re looking at how to create a database in Excel you’ll find all the information and answers you need right here.  

Entering the Data Correctly

Whenever it comes to entering in formulas and creating databases it is very important for you to focus on entering the data correctly. it sounds simple enough, but the failure to enter even one number incorrectly (or even putting the decibel in the wrong location) can throw off the entire formula and skew the database.

You don’t want to go back later and search through every single line of coding just to catch a single number that might be off. In many ways, it takes less time to just start from scratch instead of searching and trying to find these incorrect numbers. So, always take your time when entering information into the database.

Creating a Table

Creating a table in Microsoft Excel is rather straightforward. When you first open Excel you can select how many columns are in the database. You can always add more columns and rows later, so you’re not stuck with a set number.

Just in case you’re not sure, columns are the vertical bits of information. Every alphabetical identifier is a column. Rows, on the other hand, are horizontal. So every number is horizontal. when you create a table you will begin by creating an initial row. Each bit of information in these rows will be the heads of columns.

For example, if you’re creating a table for student information (let’s say you’re a teacher), the first header might be the student name. The second header might be the student number, and the third header might be the student grade. Each of these headers are within the same row (horizontal). However, as “headers”, the information are the tops of individual columns.

When creating a database, it is helpful to note that all rows are referred to as records, while all columns are referred to as fields. So, if someone is talking to you about the formation of a database and uses the terms records and fields, you will understand what they are referring to.

Adding Information

You can now begin to add information to your table. Follow the headers and type in the information under the appropriate headers (given the previous example, you’ll type the student names under the name header, the number under the student number header, and the grade under the grade header).

Cells That Can and Cannot Be Left Empty

When creating a database in Excel, you need to understand what cells can be left blank and what cells must have information. It is possible to leave full rows blank. You might want to include separate information under the main information of the database. You may have separate classes or other information in which you wish to keep portions of the database separate. You can do this by leaving one (or more) rows blank.

However, when it comes to how to create a database in Excel, you are not able to leave columns blank. All column information must come one after another. When you input codes and other information into the spreadsheet Excel will stop when it runs into full blank columns. So, if you leave a full column blank Excel will not look at any information to the right of this column. This is why you can’t skip any columns or leave information blank.

Turning the Spreadsheet into a Table

After you have the spreadsheet information typed upyou may want to convert it into a table within the file. This can be done in just a few short steps. First, you’ll want to highlight all the cells you want to include within the table. Once you have done this click on the “Insert” button at the top of the program window. Now choose “Table.” A pop-up window will ask you if the data for the table is correct (and if it has headers). Click “OK” and Microsoft Excel will create the headers.

Formulating the Database

Now that you have the individual table within your Excel document it can be used as a database. However, you will need to know how to use the database and how to complete the formatting of the database to finish off the creation process. Once you know how to do this, you will complete the how to create a database in Excel exercise.

There are several tools available to you when it comes to how to create a database in Excel. It comes down to what exactly you want to do with your database. Should you want to move into the individual tools and format specific cells within your database you are able to click on the “Format As Table” drop-down menu and input whatever coding you might find necessary. You can also click on the “Format Cells” box, which allows you to customize the information in each individual cell.

The exact formula you use depends on the kind of information you are using and how you want it to interact with other cells within the database. There is no one specific kind of formula to use. There are literally thousands of formulas. You can also create your own mathematical formula, which is used to implement specific equations within the database. When entering in a formula through the “Format As Table” option, make sure the information is correct, otherwise, the entire formula will give you incorrect answers for what you’re looking to accomplish.

Relational Database

Creating the relational database makes it possible to include several tables and spreadsheets into the database. To do this, after you have created the initial table you will want to press “CTRL” and “A” at the same time. This will highlight the entire table without clicking and dragging.

From here, the next step for how to create a database in Excel is you’ll want to click the “Insert” button at the top of the screen and then choose “Table.” Once this happens a new window will appear. Check off the box that reads “My Table Has Headers,” then click the “OK” button. You will want to name the table in the new window that asks for the “Table Name.” If you don’t, Excel will name the table for you, which is nothing but a series of numbers and letters that are especially confusing (and will be difficult to track down later on).

Slave Tables

There will be times where you’ll want to create what is known as a “Slave Table,” which can help with how to create a database in Excel. The slave table will include some coding or other bits of information that will influence the main table (and also carry out the requests of the main table).

In order to create the slave table (which you may find necessary when addressing the how to create a database in Excel question), you’ll want to click the “+” sign found on the Navigation bar, then add two additional spreadsheets to the selection. After you have done this, name the first spreadsheet two spreadsheets. You will want to title the new slave table after one of the fields within your master table. This will make it possible to include more data regarding the original field in the slave field. It also allows you to edit information within the master field and have it copy over into the slave field without typing the changes a second time.

Now, you’ll want to copy information from the master table and paste it into the slave table. For example, perhaps you only need the student name within the master table, but you want to provide more information based on the name in the slave table. Click inside of column A in the slave table and paster the information in. Add in whatever necessary information you might need in the slave table, then copy the entire table (you can click and drag or use the CTRL+A hotkey shortcut). Click “Insert,” followed by “Table.”

Within the slave table, there are bits of data that will influence what goes on with the master table. For example, if you have a grade point field within the master table, and a series of grades for a student within the slave table, whenever the numbers within the slave field are adjusted it will influence and change the information displayed within the master table. This way, you can always go into each individual student (as an example) and create slave tables to give you greater control over each table database.

How to Create a Database in Excel | Conclusion

If you want to utilize formulas within your spreadsheets, you will probably want to create a database in Microsoft Excel. By using this information you will learn how to create a database in Excel, which makes it easier to control the data you input. Once you have everything entered in you can always edit the data and alter the information to better fit your personal (or professional) needs.

Person in front of laptop with excel display

Google Sheets vs Excel: How Do They Differ?

When it comes to creating a spreadsheet on your computer, the program most professionals have routinely turned to is Microsoft Excel. As part of the Microsoft Office suite of programs, this title has been around for decades and has built a level of trust few other programs can replicate. For years this was the only true option available when it came to creating reliable spreadsheets on the computer: at least until Google Sheets came out. Here is what you need to consider regarding Google Sheets vs Excel.  

What Is Google Sheets?

When looking at Google Sheets vs Excel, Google Sheets is the new kid on the block. Google has been releasing applications for some time now, and it is now going head to head with Microsoft. There are still a number of powerful features Microsoft has that Google doesn’t, but competition in this sense is good. For years, Microsoft didn’t have to worry about anyone creating something similar to Excel, and because of this it did not do much in the way of upgrades or updates. But with Google Sheets, that is all different.

Google Sheets has been around for longer than most people might assume. Google first released the application back in March 2006, so it’s been around for 13 years. The spreadsheet software runs through a user’s Google account, so it is designed to be an online application (although it is possible to download the program and work offline).

Google Sheets is also available to work on nearly every platform you might use. Not only does this mean that it runs on Windows, Microsoft, and the Google Chromebook OS (which is a form of Android), but it also runs on mobile platforms like macOS, Windows Phone, Android, and even Blackberry.

The History of Google Sheets

While the spreadsheet application has technically been around since 2006 it hasn’t always gone by the same name. XL2Web created the web-based spreadsheet application and was eventually bought out by Google. Google went on to name the application Google Labs Spreadsheets, and users could begin using the application in June 2006.

Eventually, Google purchased DocVerse in 2010. The company created a word processor application that worked online in a similar fashion to how the spreadsheet application did. Google then purchased Quickoffice in 2012, which offered other Microsoft Office-like applications, but for free. By the end of 2012, Google renamed its Google Labs Spreadsheets to just “Google Sheets” and started offering it as a Chrome application download. Now, it is available everywhere and on any device you might want to use it.

What Is Microsoft Excel?

When making the Google Sheets vs Excel comparison you need to look at the overall history and creation of Microsoft Excel. The very first release of the spreadsheet software, which so many other spreadsheet softwares have been based on, came out in 1985.

This wasn’t the first foray into the world of spreadsheets for Microsoft though. It had initially created a similar program in 1982 called Multiplan. However, as computers began to move into the world of MS-DOS, most spreadsheet users turned to a Lotus 1-2-3 application. Microsoft retooled the application and released its first version of Excel in 1985 as a standalone application. However, it wasn’t officially packaged with the rest of its Office suite of programs until 1987.

As Microsoft started to increase its foothold in the world of personal computers Lotus began to fall behind. Microsoft didn’t release a spreadsheet program to the Windows OS until the early 90s, and by then Excel (and the rest of Microsoft Office) was one of the best selling programs anywhere.

Further Development of Microsoft Excel

Over the years Microsoft Excel has basically re-invented the world of spreadsheets over and over again. In 1990, the big thing was its new inclusion of toolbars and add-in cell support. Then when Windows 95 came out, all Microsoft Office programs received a new facelift and all kinds of new features, including Excel (known as Excel 5.0 at the time).

Today, Excel is still the best selling spreadsheet application around; but if you are looking for a program that fits your own needs, it is now important to compare Google Sheets vs Excel to see which addresses what you’re looking for in a spreadsheet application.

Google Sheets vs Excel: Differences

While both Google Sheets and Excel are spreadsheet applications, it is important to compare the two and to look at the differences between them. This way, when attempting to decide the winner of a Google Sheets vs Excel battle, you’ll have a better understanding of the applications.

Price

With this there is a clear winne: Google Sheets is free. Most Google applications are free, and this is no different. Microsoft, on the other hand, has a few different tier options. First, you can pay a subscription fee, which is either about $7 a month, or approximately $70 a year. Not this does give you full access to the entire Microsoft Office suite (for a single use license), but when you subscribe receive not only patch updates, but also full system updates when released.

You can also buy the application as a standalone software title. You will pay about $130 for this. Basically, you’re paying either $70 a year or you’re getting something for free.

Saving

There’s nothing worse than working on something, spending countless hours on the content, only to have the computer crash and all your work go out the window. While sure, that’s a lesson in the need to be constantly saving, you also shouldn’t be forced to save content after every single move you make. That makes for an interesting Google Sheets vs Excel comparison.

For starters, Google Sheets autosaves everything. Whenever you edit or adjust anything, Google automatically saves what is done. So, if your computer disconnects from the Internet, the battery dies, or something else happens, you won’t lose anything. With Microsoft Excel, there is an autosave feature when working in the cloud. However, if you’re working on something locally, you need to remember to continually click the save button. If you don’t, you run the risk of losing everything you just did.

Collaboration

If you work with others using the same spreadsheet, this is an important feature. If you don’t, you don’t need to focus on this area. However, with Microsoft Excel you save the file locally. If you subscribe to the cloud-based version (which is what you do if you’re paying the monthly service fee), then you can access it on the cloud as well as others. However, you can’t always try it in real time.

With Google Sheets, you can see exactly who is working on the software and even see the changes as the changes are being made in real time. Everything is stored in the cloud (although you can save the files locally as well). If you work with others, either on the other side of the globe or even on the other side of the desk, there’s no need to save the file and email it over (or save it to the cloud for later use). Everything is done in real time, which makes this a real time saver.

Customize Your Spreadsheet Program

Have you ever worked in a program and wished some presets were closer by? If only you could edit the display and make it more personally user-friendly, right? Well, with Microsoft Excel, you can do that. There is a Quick Access toolbar that allows you to adjust the shortcuts and buttons you have on your dashboard. This will speed up the entire process and make things easier. Google Sheets does not allow you to do this at all.

Large Files

Perhaps you need to work on a very large account. Think really large. If you have an extremely large account and file you need to work on, you may find Microsoft is the only option for you. Now, Google Sheets can handle a rather impressive 5 million cells. Chances are you’ll never come close to this kind of size. But what if you do and what if you need to go over?

Well, that’s where Microsoft Excel comes in. With Excel you can handle over 17 billion cells. So in terms of the size of a document, if you need something completely out of this world in terms of size, Microsoft has you covered.

Conclusion

When it comes to Google Sheets vs Excel, the right spreadsheet application will be what works best for your particular needs. You can go with Excel or you can go with Sheets. Both are excellent applications. With Google you can’t beat the price (free), and yet with Excel you receive all kinds of powerful tools. So go through the different features of what makes the two different. You may even want to test the applications out on your own just to see which one feels more comfortable at your fingertips.

Silver laptop with excel display

The History of Microsoft Excel: How It Came to Be

Most of us are familiar with the Microsoft application Excel. It has become so popular and synonymous with data and spreadsheets that we often forget that elegant programs such as Excel did not always exist. And they did not always exist in their current format. Let’s take a look at the history of Microsoft Excel to see how this beautiful data crunching software became what it is today.

Great things usually start out as something small, and in the technology world, they are usually created to solve a problem. We will learn that that is extremely true for the spreadsheet programs that we see out on the market today and especially true for the history of Microsoft Excel. We will look at how a student at Harvard Business School was looking to solve his problem of performing analysis for a case study, and that led to the creation of one of the most popular, flexible, and widely used spreadsheet programs in the world today.

What Is Microsoft Excel?

Microsoft Excel is a spreadsheet application that was first launched by Microsoft Corporation in 1985. In order to perform mathematical functions on the data, the program organizes the data into columns and rows. This can then be manipulated through formulas which allow users to input and analyze large sets of data. Furthermore, it offers numerous automation capabilities so that users can compute large sets of data repeatedly. As we’ll learn, this feature, when first introduced, gave Excel an advantage over other spreadsheet software available.

In addition, the software offers a variety of features that help users better visualize and share data. Users can easily create 3-D charts, drawings, outlines, and more to share their data analysis with others. Excel also integrates with other Microsoft Office suite programs, giving users the ability to easily share information through different programs and in different formats. This is especially useful for using data in reports and presentations created in Microsoft Office and PowerPoint.

Popular Features of Microsoft Excel

The uses of Microsoft Excel are practically limitless — especially when you combine it with the accompanying Office Suite Programs. We could create a never-ending list, but let’s take a look at the top 10 features of Microsoft Excel. These are features that will help you improve your ability to analyze data for your personal use or for your business.

1. Efficiently model and analyze almost any data
2. Zero in on the right data points quickly
3. Create data charts in a single cell
4. Access your spreadsheets from virtually anywhere
5. Connect, share, and accomplish more when working together
6. Take advantage of more interactive and dynamic PivotCharts
7. Add more sophistication to your data presentations
8. Do things easier and faster
9. Harness more power for building bigger, more complex spreadsheets
10. Publish and share through Excel Services

These are just the start of what you are able to do with this well-designed piece of software. It is a great tool for data analysis and can be used for both personal and business use. Its flexibility is one of its key features that have lead to its popularity throughout the history of Microsoft Excel.

History of Microsoft Excel Up Until This Point

It is hard to talk about the history of Microsoft Excel without talking about the entire history of spreadsheet applications and software. Let’s start where it all began: Harvard Business School. In 1978, Harvard Business School student Dan Bricklin needed to perform an analysis for his case study. At the time, he had two options: complete the analysis by hand or use a clumsy mainframe program. Bricklin envisioned creating something akin to the blackboard in the classroom where data could be compiled, displayed, and computed.

By the fall of 1978, he had created the first working prototype of his vision and called it VisiCalc. It was capable of manipulating matrices of 5 columns and 20 rows. It could perform basic arithmetic operations, instant automatic recalculation, and scrolling. It was a far cry from modern-day spreadsheet programs and software, but it was the program that started it all.

After the introduction of VisiCalc in 1978, other spreadsheet programs began to appear on the market. Each had their own merits and values but a few stand out as becoming more popular than others. In 1982, Microsoft released its first spreadsheet software, Multiplan. During the same year, Lotus Development Corporation released its spreadsheet software Lotus 1-2-3. Lotus 1-2-3 was capable of iteratively solving circular references, integral charting, graphing, and rudimentary database operations. These features made it a popular choice for MS-DOS users at the time.

Noticing their loss in the spreadsheet market, Microsoft introduced the first version of Excel in 1985. At first, it was only available on Apple, Inc.’s Macintosh computer. Being the first to use a graphical interface and pull-down menus it made it easy for users to use the software with the point and click capabilities of a computer mouse. The software also offered strong graphics and fast processing.

Later, in 1987, Microsoft released a version of Excel that could be used on their new Windows Operating System. By 1988, Microsoft Excel was beginning to outsell its main competitor Lotus 1-2-3. Lotus Development Corporation was slow to release a Windows version of Lotus 1-2-3 and Excel quickly became the popular spreadsheet software of the Mid-1990s. In 1993, Microsoft released Version 5.0 of Excel which contained the Visual Basic Applications (VBA), more commonly referred to as macros. This created unlimited possibilities for automation that helped propel Excel’s popularity.

Excel continued to grow in popularity with later versions of the software. Later versions offered more features and capabilities making it easier to use. Upgrades included toolbars, outlining, drawing, 3-D charts, numerous shortcuts, and automation features. These features made it the ideal program for data computation and analysis because it could easily adapt to any business process.

Improvements to the Original

In 1995, Microsoft released Version 7.0 of Excel, more commonly known as Excel 95. It was the first 32-bit version of Excel and while no external changes were made, it offered more stability and faster performance. In 1997, Microsoft introduced versions of Excel that featured the paperclip office assistant, validations, and a new interface for VBA developers. Later, with Excell 2000, Microsoft introduced an improved clipboard, capable of holding multiple items at one time, and introduced the Excel Self-Repair.

In 2003, Microsoft released Excel 2002 as part of the Office XP suite. In addition to allowing more shareability between the Microsoft programs, this new version of Excel allowed users to recover data in the event of a computer crash. In 2007, Microsoft redesigned the user interface of Excel and its sharing features. It made it easier to move smoothly between other Microsoft Office Suite programs such as Word and PowerPoint.

Later, in 2010 and 2013, Microsoft made major upgrades to the Excel program. New features included extended image capabilities, improved pivot tables, the ability to customize the ribbon, more conditional formatting options, PowerView, FlashFill, and new functions. These features continue to make Excel an easy-to-use platform for data analysis.

Conclusion

Today, Excel is familiar, flexible, and widely used around the world for both personal and business use. As we look to the future, it is clear that cloud-based computing is on the rise. More and more people are using it to increase their accessibility to their data and collaborations. There is no doubt that given the history of Microsoft Excel, this popular program will continue to look for new ways to innovate and make data analysis simple and easy for everyone to use.

Microsoft continues to make upgrades and additions to Excel that make it more powerful than ever. These additions include PowerPivot, which is used to access larger data sets. They have also increased the row and column limit from 64,000 to 1 million. Microsoft also has plans to develop the integration of Excel with CTP Hadoop connector for SQL server. This development will provide for better integration with older technology as well as providing seamless integration for newer technology. This creates the huge benefit of a more expansive dataset usage capability — something of great value as data becomes the leading currency of the modern world.

As more and more businesses are moving toward cloud-based computing for the accessibility of their data and collaboration, Excel needs to move there, too. And we are seeing Microsoft make those moves. They have plans to provide multiple user access for analysis and reporting. This will help businesses increase their efficiency and production.

In today’s competitive business environment, custom solutions are becoming a necessity to maintain an edge against the competition and to maintain profits. Having the power of Excel behind you as a data analysis tool will enable your business to accomplish this. This allows you to have the increasing power of data analysis which will be the tool of choice for businesses to compete in the 21st century.

As demand for these tools increase we will continue to see improvements made to the existing features of Excel and the additions of new ones. We have not seen the end of Excel yet. We will continue to see improvements and upgrades as the world changes and the demand for better and stronger data analysis increases.

man wearing white top using MacBook

What to Do When Excel Keeps Crashing

Microsoft Excel is one of the most-used programs on any PC. For accountants and anyone else who does bookwork on their computer, Excel is a must-have piece of software. However, if Microsoft Excel has crashed, it means there is some kind of issue going on behind the scenes. Excel doesn’t take up much in the way of processing power. In fact, of all the programs you run it is probably on the lower end of the processing power use spectrum. Due to this you likely don’t have any RAM or processor issues when Excel crashes. Instead, you’ll need to look into other reasons for why Microsoft Excel keeps crashing.

Run Excel in Safe Mode When Excel Keeps Crashing

There are times where the best course of action for figuring out why Microsoft Excel keeps crashing is to run the application in Safe Mode. Sometimes unwanted files leach onto the software, so whenever Excel loads, the unwanted file loads as well. Other files may be corrupting the software. When you run Excel in Safe Mode, you’ll discover these leach files do not pop up while you boot in Safe Mode, which in turn will help you not only run the program, but potentially figure out what is causing the issue.

How to Boot in Safe Mode

Booting your software in Safe Mode doesn’t require you to start Windows in Safe Mode. After all, if the problem is only occurring in Excel you likely don’t need the entire computer running in Safe Mode.

In order to boot in Safe Mode you’ll want to hold down the “CTRL” button on the keyboard and then double-click Excel. A prompt window will appear asking if you want to load the application using the Safe Mode.

You can also choose the “Rub” option on the Start menu and then type “excel /safe” (without the quotations) and click “OK.” This will load Excel in Safe Mode. You will need to repeat this process every time you wish to open the application in Safe Mode.

Booting in Regular Mode

Sometimes running Excel in Safe Mode once will correct the problem on the next time you boost the software. Running in Safe Mode can disconnect the application from the issues that are plaguing it, so when you finally start in the standard method you’ll have the application running as it should.

However, if the next time you boot Excel you find that the problem is still popping up, you will need to proceed to the next steps in troubleshooting why Microsoft Excel keeps crashing.

Remove and Investigate Add-Ins

After attempting to run Excel in Safe Mode, if the problems continue, you must continue on with some of your own investigating. Several third-party designers will create add-ins that run on top of Excel. Generally, these applications will work fine as each has been tested time and time again.

However there are times where Microsoft Excel will update and the add-ins will not receive an update for weeks (if not longer), which can cause the issues; or the third-party will not automatically send out the update. You will need to determine if these add-ins are causing the problems.

Sifting through Add-Ins

Many of the add-ins will be installed on the computer registry. You don’t want to dig through the computer’s registry without knowing what you’re doing. If you start doing
that you run the risk of deleting important files from your computer and causing further problems.

Instead, you will want to disable the COM add-ins first. To do this, click the “File” button and then choose “Options.” From the secondary pull-down menu choose “Add-ins.” This will pull-up a new prompt pop-up window. Change the COM add-ins and choose “Go.” When the new window appears, check off all enabled COM add-ins and then select “OK.”

Restarting the Program

Now that all the COM add-ins have been turned off, reboot Excel. If Microsoft Excel keeps crashing, the issue is not with any of the COM add-ins. However, if the application runs correctly, you will know the issue is one of the add-ins.

With this knowledge you can return to the “File,” “Options,” “Add-ins” prompt window and enable them one at a time. Turn one of the add-ins on and then boot the software up. Continue with this process, adding one new COM add-in at a time, until the problem occurs. This will show you exactly which of the add-ins is causing the problems.

After determining which of the add-ins is causing the issue, you can go to the third-party website and see if there is an update or another fix provided by the designer.

Troubleshooting Excel

If none of the previous troubleshooting steps for determining why Microsoft Excel keeps crashing have worked, you’ll need to dive deep into the investigation process. There are a number of different steps you’ll need to perform, but as you check each off at a time, you’ll eventually identify which is the problem and what is causing the problem.

File Details

Over time your Microsoft Excel will update. Microsoft will release updates and patches throughout the year that are designed to improve functionality and features on the application. However, this can change how the file is accessed. Due to this, the Excel file you’re attempting to use may not be compatible with a more recent updates.

If Microsoft Excel keeps crashing when opening a specific file, it is likely because there are improper formulas within the file that cannot be converted. Sometimes there are hundreds (if not more) hidden heights and widths within a file, or there are too many different styles that combine when information is copied and pasted between different files. When this is the case, the file may not open correctly and may mean that Microsoft Excel keeps crashing.

Try opening different files. If the other files load correctly, you know the problem is with the file. If the file is being sent to you by a third-party (or a third-party application designer), you will want to test out features of the file outside of the provided third-party application. This way you can see if it is working accurately.

Selective Startup

If you are at this point and Microsoft Excel keeps crashing, you will now need to perform a selective startup to see if the program itself, or a process, is causing issues with Microsoft Excel.

To perform the selective startup you’ll want to click on the “Start” button and then choose “Run.” When the Run window appears, type “msconfig” (without the quotations) and press “Enter.” This will bring up a System Configuration Utility window. Choose the “Startup” tab at the top of the screen. A new display will show everything that loads when you boot up the operating system.

Knowing what is running when your computer boots up is informative, as you may not know everything that is up and running. Some files might surprise you. You can uncheck the applications you do not want to load. If you installed a program and then Microsoft Excel keeps crashing, you may want to uncheck the application. You may want to experiment with this feature until you find that Microsoft Excel loads correctly.

Sometimes files will run in conflict with Excel. If you receive any error messages when Excel crashes, it might be because of this. So testing out and removing different programs and applications from the startup may be what is needed to determine what is causing the issue.

Check Your Antivirus Software

It is possible your antivirus software is not playing nice with Excel. Sometimes the antivirus software will identify an application on your computer as a potential threat, and as such shoot it down before it can get running (or once certain files are loaded).

When this is the case, you will want to check for any possible updates for your antivirus software. If the software is up to date, you will want to check which files it is identifying as threats. This will vary depending on the antivirus software you have running, but usually it will maintain a list of threats it has shut down. If you see Excel is listed as one of the threats, and the antivirus is stopping the run process, you’ll want to go into the antivirus software and remove Microsoft Excel as a known threat. Once you’re done with that, the software should run correctly.

Conclusion

It is a big pain when Microsoft Excel keeps crashing all the time. Constant crashing will not only prevent you from getting your work done, but it may also cost you work as well. To correct the issue, you’ll want to follow these troubleshooting steps. Typically you’ll be able to correct the problem with the Safe Mode or the Add-In sifting. However, if you don’t, keep at it and follow the secondary troubleshooting steps. You will identify the problem and, once it is corrected, be able to avoid the constant crashing problem with Excel and get your work done.

Microsoft office icons including excel

How to Find Outliers in Excel in # Easy Steps

Microsoft Excel is generally considered the industry standard in spreadsheet software, specifically because it can adapt to multiple functions and because it is easy to use. Once you know how to use Excel, you know how to use Excel for life. Excel has a variety of uses, from preparing simple order sheets to calculating to creating complex graphic statistical analyses. In this article, we will give you a walk-through on how to find outliers in Excel and why finding outliers is an essential piece of data analytics in statistics.

What Is the Outliers Function in Excel?

Before learning how to find outliers in Excel, you should first know that there is an outliers function embedded in the software that makes it easy to calculate what is and isn’t an outlier. In fact, there are two methods of doing this, including a helpful graph that gives you a visual of the outliers and a formula that helps identify the outlier without forcing you to identify the outliers by hand.

Let’s take a look at some vocabulary you’ll need to know before you start learning how to find outliers in Excel.

Outlier

An outlier is a value that is significantly higher or lower than most of the values in your data and skews your data enough that you might draw an incorrect conclusion about the statistics represented by your data. Outliers aren’t bad, but they definitely aren’t good, either. You can think of an outlier as a bit of imperfection that can ruin your data – like finding a fly on your cake.

Box Plots

A box plot is a kind of graph that makes it easy to visually spot outliers. You can, of course, use Excel to create a box plot if you are so inclined, although that information will be on another tutorial. To put it simply, a box plot is useful because the box is the central tendency of the data. These are plots that show you how data is clustered around a central measure such as the median (middle) value in the data. The upper bound line is the limit of the centralization of that data. If you have values that are outliers on a box plot, then they become very obvious and stick out like a sore thumb.

Quartiles

Essentially, quartiles represent how data is broken up into quarters. Quartiles are actually a central tool in learning how to find outliers in Excel, since the formula for outliers relies on quartiles to make a calculation. A quartile is a dividing point which splits the data into quarters. There are 3 quartiles: Q1, Q2, and Q3. The first quartile (Q1) marks the lower quarter of your data where lesser values live. The middle values live between Q1 and Q3, with Q2 representing the center of your range, and values above Q3 are the upper portion of the data. Outliers live outside the inner quartile range. We’ll go over how to calculate quartiles below.

Array and Quart

The array is the range of values that you are evaluating. And the quart is a number that represents the quartile you wish to return (e.g., 1 for the 1st quartile, 2 for the 2nd quartile, and so on). We mention array and quart under this vocabulary lesson because it is vital that you get these two meanings ingrained in your head since they are the values that you need to actually perform the calculation in Excel. Be careful not to get the two confused.

Now that you have a firm grasp on the terms you will be using to input the formulas for how to find outliers in Excel, it’s time to get down to the step-by-step instructions.

Steps for How to Find Outliers in Excel

As you know, Excel has a ton of functions for statistical analysis that can greatly improve your data analysis. One of those functions is the ability to input a formula to find outliers in statistical formulas. Outliers are pests in statistical analysis since the extreme data points can skew your results and cause misleading assumptions. Outliers can drag your statistical average up or down, which means you could be looking at wildly inaccurate data. Removing outliers gives you a better idea of your actual data.

So, how do you do it? There are a few steps involved in how to find outliers in Excel – enough steps that it could be potentially intimidating to get a good grip on the process. But if you stay with it and follow each step, you will be finding outliers very quickly.

Step One: Calculate the Quartiles

This first step is sort of like a preparation for the main outlier formula. You need to perform this formula well in order to move on to the next step. Remember, a quartile is how your data is split into quarters.

In Excel, you can easily get quartile values by using the QUARTILE function. This function requires two arguments: a range of data (array) and the quartile number you want (quart). The two arguments in the functions are the data that you want to explore. When you have your data in Excel, it is mostly a list of values in a column, which makes this list your data array. The other part of the arguments is the quartile you want to define.

You should identify the 1st and 3rd quartiles by using these formulas:

=QUARTILE(array,1)

and

=QUARTILE(array,3)

Excel will do most of the hard work for you. But say that your target data array covers the cells from B3 to B22. When you input the formula to find the quartile, your array will actually be B3:B22 (the colon sets the range of the cells). That means that your final formula for quartile 1 will be:

=QUARTILE(B3:B22,1)

and your final formula for quartile 3 will be:

=QUARTILE(B3:B22.3)

After inputting the formula, hit enter and Excel will do the rest.

Step Two: Calculate the Interquartile Range

The interquartile range (or IQR) is the middle 50% of values in your data. It is calculated as the difference between the 1st quartile value and the 3rd quartile value.

There is a simple formula that subtracts the 1st quartile from the 3rd quartile. You can use any cell you want to calculate the interquartile range, but for the sake of this example, we will use F cells.

You calculate the interquartile range by subtracting Q1 from Q3. So in Excel, you would simply input

=F3-F2

and hit enter.

Step Three: Find the Lower Bound

The lower and upper bounds are the smallest and largest values of the data range that we want to use. Any values smaller or larger than these bound values are the outliers. We’ll calculate the lower bound limit in cell F5 by multiplying the IQR value by 1.5 and then subtracting it from the Q1 data point, which will look like this:

=F2-(1.5*F4)

Step Four: Find the Upper Bound

To calculate the upper bound in cell F6, we’ll multiply the IQR by 1.5 again, but this time add it to the Q3 data point, which makes the formula

=F3+(1.5*F4)

Calculating the lower and upper bounds might seem tedious at first glance, but it’s actually another vital piece of information that you will use later on. By knowing what the smallest and largest values of your data range are, you are more easily able to find data that doesn’t quite fit. If any of your data falls below or above these limits, it will be considered an outlier.

Step Five: Identify the Outliers

Now that we’ve got all our underlying data set up, it’s time to identify our outlying data points. We’re looking for values that are lower than the lower bound value or higher than the upper bound value.

In Excel, you can use the OR function to perform this logical test and show the values that meet these criteria by entering the following formula into cell C2:

=OR(B2UPPER BOUND)

After you hit enter, Excel will spit out a value. This is the value that you will copy into each of the corresponding C cells of your data array, which would be C3 to C22.

A TRUE value indicates an outlier, while a FALSE value indicates that there is no outlier.

Why Should You Find Outliers?

When performing data analysis, you usually assume that your values cluster around some central data point (a median). But sometimes a few of the values fall too far from the central point. These values are called outliers (they lie outside the expected range). Outliers can skew your statistical analyses, leading you to false or misleading conclusions about your data. Using outliers is an easy way of proving or disproving your statistical assumption. If you have more outliers than you have accurate data points, then the chances are that your conclusion about your statistical data is not going to fall in line with your hypothesis.

Conclusion

Person using excel on a laptop

image source: pixabay.com

It’s easier than you think to learn how to find outliers in Excel. And since an outlier is a value that causes a misleading assumption, it’s actually pretty important to know how to find them and how to get rid of them in order to accurately interpret your data. Excel makes this highly involved piece of statistical analysis into something that anybody can perform.

Woman is using microsoft excel to calculate something

How to Calculate Z Score in Excel: What You Need to Know

We get it. Statistics can be intimidating. All those numbers and all those graphs and all of the rules on how to interpret everything – it’s enough to make your head spin. Which is why we’re happy to tell you that some things can be less stressful with the use of modern technology. Specifically, the little helper we’re talking about is – no surprise – Microsoft Excel. Keep scrolling to learn everything you need to know about how to calculate z score in Excel. You’ll be glad you did. It’s easier than you think to learn how to calculate z score in Excel!

Z-Score Fundamentals

First things first! We need to break down the whole concept of a z score. Getting some clarity on the topic will make it that much easier to learn how to calculate a z-score in Excel when the time comes. You have to learn to walk before you can run, right?

What Is a Z Score?

In statistics, a z score is the golden child of standard deviations. A z score is a number that acts as a relative number that exists for the sole purpose of comparing random numbers to some kind of average. The z-score is that average, which means that learning how to calculate z score in Excel is really just a way of calculating a fancy average. And you’ve been calculating averages since grade school – so there’s no reason to be intimidated!

The more technical definition of a z score represents the number of standard deviations from the mean a data point is, so a z score measures how many standard deviations below or above the population mean a raw score is. Z scores range from -3 standard deviations (which would fall to the far left of the normal distribution curve) up to +3 standard deviations (which would fall to the far right of the normal distribution curve).

What Does a Z Score Do?

Basically, a z score can tell you how close or how far your highest or lowest data points are from the average. This is important because when it comes to interpreting your data, the z score tells you how good your data is. When your data points are too far from the z score – meaning when the standard deviation is -3 or +3 – that means you can safely assume that your data points show that there is a problem with your prediction, whatever it is. A z score is a useful tool that is there to tell you how extreme your data points are.

Easy Real World Z Score Example

Repetition makes for memorization. For the sake of making this as clear as possible, the final definition of a z score will be even more simple: a z-score is the standard by which you measure your data. It’s there for the sake of comparison, like the numeral equivalent of a devil’s advocate.

Z scores are a way to compare results from a test to a normal population. Results from tests or surveys have thousands of results and units. However, those results can often seem meaningless, just a mishmash of numbers that don’t mean anything. For example, knowing that a house cat typically weighs 8 pounds is great, but if you want to compare it to the “average” cat’s weight, looking at a table of data can be overwhelming. A z score can tell you how that cat’s weight is compared to the average population’s mean weight, and then you can decide if your cat needs to lose weight or not.

The Z Score Formula

Actually, depending on what you’re using the z score for, there are two different z score formulas to choose from – and choosing the right formula is important. But rest assured that for any zscore formula you will need two numbers: the mean (μ) and also the population standard deviation (σ). You will need both of these when you learn how to calculate the z-score in Excel.

The Basic Z Score Formula

The basic z score formula for a sample is:z = (x – μ) / σ

This is a formula that assumes a normal distribution for the z score is possible. In this formula, x represents the selected data point from your data set, the μ is the mean of your data points and the σ is your standard deviation. This formula is good to use for proving your predictions correct.

Standard Error of the Mean

When you have multiple samples and want to describe the standard deviation of those sample means (the standard error), you will use this z score formula:z = (x – μ) / (σ / √n)

This z score will tell you how many standard errors there are between the sample mean and the population mean. This z score formula essentially tells you how probable it is to find a sample that is above the mean of your sample population – or, in other words, how often your standard deviation will be disproven.

How to Calculate the Z Score in Excel

Now that you know what a z score is and the basic formula you will be using, it’s time to learn how to calculate z score in Excel. Be prepared by having your data points easily accessible and be forewarned that there will be a lot of data entry. Just as a reminder – double check your numbers and always calculate your z score twice!

Step 1: Open Excel

Start from the top and open Microsoft Excel on your computer. Excel is the application that has an icon of a green sheet with an “X” in front of a table. Open the Excel file with the dataset you want to find the z scores for, otherwise, input your data into the first column (A) of a new blank Excel document. Make sure your numbers are vertical and not horizontal – don’t make the mistake of inputting your data into a row instead of a column. Also look over your data entry before going any further!

Step 2: Enter the Mean Formula

Enter the mean formula in a blank cell in the same column you put your data points in. When you have all your data points recorded in an Excel spreadsheet, you can calculate the mean using this formula: =AVERAGE(“cell range”). You will be replacing “cell range” with the range of cells that contain your data points.

So, say that your data points are from cell A1 to cell A13. in A 14, you will put =AVERAGE(A1:A13) and then you will hit ENTER on your keyboard so Excel knows to calculate that formula. It’s important to remember that Excel responds only to commands that start with an equal sign (=) and that there should be no spaces in between any part of the formula. After you hit ENTER, if you’ve entered the mean formula correctly, Excel will replace your formula with the mean of your data points.

Step 3: Enter the Standard Deviation Formula

For data points that are recorded in an Excel spreadsheet, you can calculate the standard deviation using the formula =STDEV(“cell range”) in a blank cell, replacing “cell range” with the same range of cells that contain all your data points.

So, continuing with our previous example, in the A15 cell, you would put =STDEV(A1:A13) and hit ENTER. Once again, Excel will replace your formula with a number, which will be your standard deviation.

Step 4: Finding the Z-Score

Now that you’ve come this far, it’s time to finally learn how to calculate a z score in Excel. In a blank cell next to the cell of the data point you want to find the z score of, enter the formula =(datapoint-$mean)/$standard deviation, replacing “data point” with the cell of the data point, and replacing “mean” and “standard deviation” with the absolute cell locations.

So, say you want to find the z score of the data in the A8 cell. What you would do is click on the blank B8 cell and input =(A8-$A$14)/$A$15 and then hit ENTER to calculate the z score. Again, you will be typing in the equal sign, open parentheses, the cell that has the desired data point, the minus sign, a dollar sign, the column of the mean score, another dollar sign, the row of the mean score, close parentheses, a forward slash, a dollar sign, the column of the standard deviation, another dollar sign, the row of the standard deviation, and then finally ENTER.

As a point of note, the dollar signs in the z score formula are the way that Excel knows that the numbers in your mean formula and standard deviation formula cells will remain constant even if you use those cells over and over to calculate the z score for all of your data points.

Conclusion

two men working together

image source: unsplash.com via Helloquence

See how easy that was? Learning how to calculate z score in Excel is a cakewalk because Excel does all the really hard work for you. Of course, the job of interpreting what the z score means still falls to your shoulders, but at least you don’t have to make your head ache by hand-writing and calculating the z score formula. Simply use Excel to find your mean formula and your standard deviation, and then you’re only one simple input away from finding the z score for any point of data you want.

Still not seeming so simple? Try it for yourself! Trust us, it gets easier after inputting the formula a few times!

Excel Table icon

How to Find Variance in Excel? Follow These Tips

Image source: ​https://pixabay.com/illustrations/office-windows-word-excel-1356793/​​​

Microsoft Excel is a ubiquitous product, and one that gets used for many organizational tasks. Most people are familiar with the program’s basic format even if they don’t own it themselves. In addition, simple operations such as adding or deleting columns or populating individual cells with data are easy to figure out with little instruction. There’s a lot more to Excel than the basics, though: the program contains many logical and mathematical operations that are useful when dealing with large amounts of data, and these operations require more knowledge to use effectively. One of the most useful among these is the ability to calculate a value known as variance. Knowing how to find variance in Excel is a very helpful skill to have while designing any spreadsheet, especially if one understands the nuances of the function.

What Is Variance?

The Meaning of the Term

One of the most important steps towards understanding how to find variance in Excel is knowing what variance is. Variance is a value found in any large set of data that represents the average distance any single data value in the set is from the mean. If we made an equation to predict what the next data point in a set would be, the variance would be how far off the actual data point would likely be from that prediction.

Two Kinds of Variance

There are actually two forms of variance. Each of them is useful in a slightly different situation, and each value is calculated differently. The form of variance we just defined is also known as population variance. This means it’s the variance of a complete set of data, which distinguishes it from the other form of variance: sample variance.

Sample variance is a form of variance that’s calculated when you only have access to a sample of values from a larger set. Both variances represent the same thing and have the same uses, but they are each calculated differently. Fortunately, Excel has multiple functions for finding each.

How to Use Variance

While it can show the reliability of a set, variance by itself is not always a popular value to draw conclusions from. Because of how variance is calculated, it can be confusing: Variances are difficult to express meaningfully on a graph, and often aren’t expressed in the same units as the actual data within their set.

To find a value that can be better understood visually, one must take the square root of a set’s variance. This results in a value known as the standard deviation, which is expressed in the same unit as the data in the set and is closer to each point’s difference from the mean.

When you hear of a prediction that states the result ‘give or take’ a certain value, the value they’re talking about is the standard deviation. Fortunately, knowing how to find variance in excel also means knowing how to find this: To find a set’s standard of deviation, you just need to take the square root of the variance once it is calculated.

The Six Variance Functions In Excel

Plenty of Options

Knowing how to find variance in Excel isn’t as simple as knowing the one function that will calculate the value. Excel has six different functions that can calculate variance, each of which has unique properties. Using the functions well requires knowing the qualities of each and understanding what situations they work best in. Fortunately, Excel’s six VAR functions can be split into three easily explained groups.

VAR.P and VAR.S: Population and Sample Functions

VAR.P and VAR.S are the most basic functions for calculating variance. The letter after the period specifies the variance being calculated: VAR.P calculates population variance, and VAR.S calculates sample variance.

These are the most commonly used functions, but there is one complication involved in using them: the function does not exist in older versions of Excel and opening a spreadsheet that uses it with one of these versions will cause compatibility issues.

VAR and VARP: The Old Functions

Before VAR.P and VAR.S were implemented, VAR and VARP were Excel’s basic variance functions. Like the more modern functions, they calculate the two basic kinds of variance: VAR calculates sample variance while VARP calculates population variance. These functions may eventually be phased out of Excel, but for the time being, they exist in every version of the program.

Microsoft considers VAR and VARP ‘compatibility functions’, and they exist to provide reliable choices for spreadsheets that will be opened on older versions of the software. However, they are slightly less accurate than the modern functions.

VARA and VARPA: Assigned Values

Of all the variance functions in Excel, VARA and VARPA are probably the most complicated of the options. VARA and VARPA calculate sample variance and population variance, respectively, but these two functions interact differently with other contents within the spreadsheet.

While Excel’s other four variance functions only recognize the content of referenced cells if that content is a number, VARA and VARPA read any assigned values within referenced cells or arrays. This means that VARA and VARPA will recognize written numbers in any cells or arrays referenced in the function: if one contains the word ‘four’, the functions will calculate the variance as though that cell contained the number 4. The functions will recognize all other text, including an empty cell, as a 0.

VARA and VARPA can also recognize logical values in the same way: if the contents of a cell or array contain the word ‘TRUE’, the functions will count it as a 1, and if they contain ‘FALSE’ the functions will count it as a 0.

If a spreadsheet contains many written elements or logical values these functions can save a lot of time, but they should be used cautiously. Overlooked text can lead to cells registering a value they shouldn’t and causing an inaccurate result.

Calculating Variance Using Excel’s VAR Function

How to Find Variance in Excel

Now that all of the individual variance functions in Excel have been explained, it’s time to learn how to implement them. Fortunately, all six of the functions use the same syntax, and aside from the differences already mentioned, work in the same way.

In order to calculate the variance of a set, you must first select a cell in your spreadsheet you want to display the variance in. Then, type an ‘=’ sign followed by the name of the function you intend to use into that box, followed by a set of parentheses.

Place the numbers you want the variance to be calculated from inside the parentheses, separated by commas: these arguments can be numbers, logical values, or arrays. The function should automatically calculate the variance of the data you have selected and display it in the cell.

Example Using the Arguments List

Now that we’ve explained the principle, here are some specific examples of how to find variance in excel. First, we’ll use a simple example: This one only uses numbers written directly into the function as arguments and does not interact with other cells in the spreadsheet.

Let’s say we have a set that contains the numbers 13, 15, 18, 17, 24, 22 and 10. We know that this a sample of a larger set and for the sake of this example we expect the spreadsheet to be opened on older versions of Excel, so we use VAR to calculate the sample variance. To do this, we type this into the cell:

=VAR(13, 15, 18, 17, 22, 10)

Example Using an Array

Now that we’ve seen a basic example of how to find variance in Excel, here’s an example that’s more in line with how you would use the functions in an average spreadsheet.

This time let’s say we have a set that’s contained in a 10 x 4 box of cells in our spreadsheet, with cell A1 at the top left-hand corner of the box and cell D10 in the bottom right. We use VAR to calculate the sample variance and this time we enter the arrays of the cells containing the data as the arguments. That will look like this:

=VAR(A1:D10)

Conclusion

With this, we’ve gone over everything you need to know about how to find variance in Excel. You’ve learned what variance is and have a general understanding of both its importance in statistics and how to derive useful values from it. You’ve learned about population and sample variance and what situations each form of variance is used in. You’ve seen all six of the functions Excel provides for calculating the value. You know what functions to use to have your functions recognize assigned values, and you know which functions to use when compatibility is a concern. You understand the syntax of the functions and have seen firsthand what the use of those functions looks like in various situations.

Congratulations! You are now fully prepared to design spreadsheets that calculate and display variance, and are one step closer to being an Excel pro!

A person is using a laptop and editing something to a excel file and beside the laptop is a cup of coffee and a sunglasses

How to Print Labels from Excel: Ultimate Guide

There are times where you will want to print from any number of applications on your computer. This includes Microsoft Excel. The software allows you to not only create spreadsheets but also labels. If you have several labels you need to produce, whether for mailing or for personal identification, you will want to generate the labels in Excel and then go about printing the information. Here is what you need to know regarding how to print labels from Excel. 

Prepare the Worksheet and Enter the Data

Before you can begin with looking at how to print labels from Excel, you need to properly create your worksheet, format it and then set up the data to be used within Microsoft Excel. This example will focus on producing mailing labels for print (which you can print on sticky paper, so it is easy to remove the label and slap it onto an envelope or mailing box), although you can use the steps for generating any kind of labels for printing.

Creating the Labels In Excel

Open up Microsoft Excel. You don’t need to adjust the number of columns or rows you’re working in, so just dive right into the entering of information. You will want to type in the first cell of each column the basic information you will need for the label.

In the case of a mailing label, the first column should be the person’s “Title” (as in Mr, Ms., Dr., and so on). The second column should be their first name, the third column should be their last. The fourth column should be street address, followed by city, state, and ZIP code.

If you’re making something other than a mailing label, you need to break down the information into individual columns. Perhaps you’re printing off ID labels for use by individuals at a conference. You can still use the first and last name columns, only now add in their business title, company they work for, and so on.

Type In the Data

Now that you have the columns created you can go about entering in the data. Fill out all the information you have, making sure not to leave blank rows or columns. Once you have completed the process of entering all the information into your file, make sure to save it.

Formatting the File

You will need to connect to an Excel worksheet from Word to print your data. As this is likely the first time you have done this, you will need to enable a conversion format between the two Microsoft programs.

To do this, click the “File” button in the top left corner of the program window. From here, select “Options.” A new “Word Options” window will open up. In the display window, click “Advanced” along the left side of the screen, then choose “General.” This will bring up a new series of information. From the new listed material, check “Confirm File Format Conversion on Open,” then click “OK” to confirm everything. This will allow you to create the labels as you import the data.

Opening Word

Now that you are all set and ready to use Word to help aid you with the printing process, you’ll want to launch Microsoft Word and open a blank Word document (this should open automatically). Click on the “Mailings” tab, then select “Start Mail Merge” and follow up by clicking “Labels.”

Scroll through and select the brand you’d like in the “Label Vendors” box and select a product number. This will be displayed on the label package. You can also choose “New Label” if you’d like to enter in any custom label dimensions (if not, go by the size of the label you are interested in, which should be listed, unless you are specially crafting the label yourself and are not using a pre-made label size).

Connecting Labels to Word

Microsoft Word is now all set up to receive your information from Excel. The next step for how to print labels from Excel is to click on the “Mailings” tab and choose “Select Recipients” from the Mail Merge group. In the new pull-down menu select “Use an Existing List.”

Now, choose the Excel worksheet you created earlier and click “Open.” Now, select “OK” in order to confirm that you want to use this particular list. Click “OK” a second time when asked to select the table. In a moment, the Microsoft Word screen will generate new labels and say “Next Record.”

Connecting Mail Merge Fields

With everything connecting as it should, your next step for how to print labels from Excel is to connect mail merge fields. This way, the labels will appear correctly within Microsoft Word, allowing you to print the data.

In order to do this, you will need to select the first label listed on your Microsoft Word document and then click on “Mailings” from the top of the screen. Now choose “Write & Insert Fields” from the pull-down menu and finally click on “Address Block.”

A new set of options will appear on the screen. Choose “Insert Address Block” from the window and then click on “Match Fields.”

The window will now display what the labels will look like should you accept the input information. Look over the displayed information to make sure it is entered in properly (it should be, as long as you typed everything into Excel correctly). Scroll through the display window and, if any changes need to be made, click on the drop-down arrow next to the display and make the necessary edits.

Now, click “OK.” Click “OK” a second time to exit out of the dialog box and then click back on the “Mailing” tab, followed by “Write & Insert Fields,” and finally “Update Labels.”

Merge

With everything set, now all you need to do is merge the information from Excel into the document. Choose the “Mailing” tab, click “Finish” and then choose “Finish & Merge” from the list of options. From there, choose “Edit Individual Documents” to look over how all the printed labels will eventually appear in the document. If it looks as it should, choose “All” and then “OK.”

From here a brand new document will load on your screen. This imports all the labels from your Microsoft Excel worksheet. You can now make any necessary edits or adjustments to the information if need be.

Previewing and How to Print Labels From Excel

Everything has now been imported and your labels are ready to go. You are now at the final stages of the how to print labels from Excel instructions. You need to make sure everything looks good to print.

Click on the “File” tab in the program, then choose “Print Preview.” This will load a window and display exactly how the document will look after you print it. Go over everything to make sure it looks correct. When you are satisfied with how the information is presented choose “OK.”

If you are printing on sticky labels, you need to make sure the paper is inserted correctly. If you are using a standard desktop printer and the paper is inserted in a tray on the bottom of the printer, you will typically want to insert the labels facing down (the paper is spun up, so when it rotates the ink will be applied to the correct side of the document). If, on the other hand, you use a printer that is rear fed, with the paper positioned vertically behind the printer, the label side will face outward, toward you.

Once the paper is in and you’re ready to print, click on the “File” tab again and then choose “Print.” A printer window prompt will appear on the screen. As long as you don’t need to make any paper size adjustments you should be good to go. Click “Print” again and the software will send out the information to the printer. Momentarily, the printer will begin.

Make sure you monitor the first sheet that is printed. This way, if there is an error in paper alignment, you have the wrong size stickers inserted, or there is another issue, you can cancel the print job. Sticky label paper is not cheap, so it’s best to stop the printing process as early as possible to avoid destroying any other paper you might need.

Conclusion

The beauty of Microsoft Excel is that it is more than just a standard spreadsheet program designed to help organize information. It can also be used to print information, such as labels. If you have a large mailing list and you need to send out information, typing in the mailing list and creating the labels directly in Microsoft Word is time-consuming and often difficult to format. Additionally, you may already have the information typed in Excel anyway. By following these how to print labels from Excel instructions, you’ll be able to quickly and easily print any label from Excel, regardless of the information or how many labels you need.

making graphs in excel

How to Add a Row in Excel: Step by Step Process

Microsoft Excel is a powerful spreadsheet software used throughout the world. One of the features within the software is the ability to easily add rows and columns to the spreadsheet. It is a simple feature that is easy to take advantage of and, once you know how to implement it, you’ll be able to implement it whenever needed. So if you’re a user of the program it is important for you to know how to add a row in Excel.

How to Add a Row In Excel

excel spreadsheet

Image via flickr

If you are interested in how to add a row to Excel, you’ll want to click on the row heading right below where you want the new row to appear (so if you typed in row 7 and row 9 and discovered you forgot to type in your row 8 information, you’ll want to select row 9. This way, when you add the new row it will insert between 7 and 9).

With the row selected, click on the “Insert” button at the top of the Home tab. The new row will now appear above the row you currently have selected.

Formatting the Row

highlighting an excel row

Image via flickr

When you add in a new row, Excel will automatically format the row to look like the rest of the rows you already have. However, when you choose the “Insert” option there will be an “Insert Options” feature that appears on the row (the icon looks like a brush). If you want to change the look or format of the inserted row click on this brush icon, then choose either “Format Same As Left,” “Format Same As Right,” or “Clear Formatting.”

Insert a Column

There might be times where you need to insert a new column of data instead of a new row. The process is similar, but it is important to go over. To begin, you need to select the column heading to the right of where you want the new column to appear (for example, if you want a column to appear between columns C and D, you will click on column D).

Now, select the “Insert” button on the top of the Home tab. The new column will appear.

Deleting a Row

There may be times when using Microsoft Excel where you want to delete a row. The steps for how to delete a row are similar to that of how to add a row in Excel.

Click on the row you want to delete. It is important to click on the header of the row and not the cell. If you click on the cell and choose the “Delete” feature you’ll end up removing only the cell which may throw off your calculations and the alignment of the other cells in your spreadsheet.

So click on the number of the row you want to remove. You can click and drag over multiple row headers if you want to remove several rows from your spreadsheet at the same time.

With the row(s) selected, click on the “Delete” button found on the Home tab. This will delete the selected rows and all the rows underneath those removed will move up (so if you deleted row 5 and 6 your former row 7 will now be identified as row 5).

Delete Columns

If you need to remove columns instead of a row, the process is similar. You need to make sure and click on the header of the column. This selects the entire column. You can click and drag over multiple column headers if you want to remove multiple columns at the same time.

Once you have selected the column(s) you want to remove, choose the “Delete” button from the Home tab and the columns will be removed. Once again all the columns to the right will shift over to the left. So if you deleted columns D and E, the former column F will now be identified as column D.

If you renamed the column names, the names you created will remain the same.

Tips/Warnings

Tips and tricks

Image via flickr

When it comes to how to add a row in Excel, you will always want to follow through with a few tips and tricks. Following these tips and tricks will make it easier for you to implement the new row without causing problems with the overall file. The last thing you want to do is cause problems with your files and have to start over completely on a new spreadsheet document.

When looking at how to add a row in Excel, you need to make sure you select the heading of the row and not a cell. If you click on a cell and then choose “Insert,” Microsoft Excel will add a new cell and not a new row (or a new column, if you’re adding a column). So, if you’re running into the problem of only adding a new cell, this is what is going on.

Clearing the Content

There is a difference between deleting an entire row or column and clearing out the content found within the row. You may want to remove all the current information currently found in the cells of a row or column, but if you still need the rows/columns present, you don’t want to just delete everything. Instead, you need to follow a “Clear Contents” option.

In order to use the “Clear Contents” option, you will need to click and drag over the rows and columns you wish to clear out. You can either click on the row/column header to select the entire row/column, or you can click and drag over specific cells.

Once you have selected the regions you want to click out, you’ll need to right-click within the area you selected (or Control-click if you’re using a Mac), then choose “Clear Contents” from the pull-down menu. This will clear out the cells but leave the cells open.

Move a Row/Column

Perhaps you don’t need to delete, clear out, or add a row/column. Maybe instead you need to move it. You find information is in the wrong order or you think it would be easier to work if the row is found in a different area of the spreadsheet. You will not need to follow the how to add a row in Excel instructions as these do differ some.

First, you’ll want to click on the header of the row you want to move. Once you do this select the “Cut” command found on the Home tab (it is a pair of scissors icon). If you’re using a Windows computer, you can use the keyboard shortcut of Ctrl+X (or Command+C if you’re using a Mac).

Now, click on the row under where you want the copied row to go. Once you have it selected click on the “Paste” tool found on the Home tab. if you’re using a Windows computer, you can use the keyboard shortcut of Ctrl+V (or Command+V on a Mac computer).

Hide/Unhide Row/Column

There might be times where you want to compare two rows together but there are other rows in between that blocks your view. In this case, you will want to hide rows. By hiding rows, you can press two rows together temporarily.

These instructions are similar to how to add a row in Excel. Click on the row header of the rows you want to hide. Chances are there are multiple rows you want to hide so click on all the rows you want to hide for the time being.

Now, right-click on the area you selected and choose “Hide” from the pull-down menu. A green line will remain between the newly created rows. When you want to bring the rows back, you’ll want to choose the rows above and below those that are hiding, then right-click in the selected area. Choose “Unhide” from the pull-down menu and the hidden information will appear back onto your screen.

Conclusion

Microsoft Excel is one of those programs you either will never use or you will use on a daily basis. If you are a user of the software, there are a few specifics you must master. One of these features is how to add a row in Excel. When you’re able to add and remove rows in Excel, you will be taking the first major step in utilizing the power of the software and how it can make your professional life easier.

How to Remove Duplicates in Excel: An Easy Guide

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

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

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

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

Using Dates with the Remove Duplicates Feature

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

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

How to Delete Duplicates in Excel

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

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

Remove Duplicates in Excel

Steps to Removing Duplicate Data in Excel

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

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

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

Remove Duplicates Dialog Box

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

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

Remove Duplicates Confirmation Popup

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



Related Posts Plugin for WordPress, Blogger...