How to Create a Drop Down List in Excel with Data Validation

Those wondering how to create a drop down list in Excel will be relieved to know that it is easier than it sounds. As you may already know, drop down lists make data entry a breeze. For example, if you have ever used such a menu for surveys, polls, and web forms, you know how convenient they are. As tech nerds, we’re happy that such an option exists within an Excel spreadsheet.

Adding a drop-down list to a cell or range using Data Validation is a simple matter. Data Validation is used to define restrictions on what data can or should be entered into a cell. Here we’ll use a List to restrict what values can be entered into a cell. This article walks you through a step-by-step guide of how to create a drop down list in Excel.

How to Create a Drop Down List in Excel for a Cell or Range

Select the cell or range you want to use for a drop-down list, then

  • Choose Data Validation from the Data Tools group on the Data tab
  • Select the Settings tab
  • In the Allow box, select List
  • Click the Source box
  • Type in a list of values separated by a comma
  • Make sure the In-cell dropdown box is checked
  • Click OK

Data validation - Excel pick from drop down list

The list I created was for cell A1, which is shown below.

Excel 2010 Drop Down List

Excel 2010 drop down list

List Data Sources

Manually entering the source data for the Excel 2010 drop down list is probably the least desirable method. A better way is to put the list in a range, then refer to the range.

Data Validation list source range

The same list data was put into the range J1: J6, then I changed the source reference to these cells. This is a better method than manually entering the values, but older versions of Excel require the list to be on the same worksheet. One way around this and a better solution is to give the List range a Name.

Data Validation list source Named rangeYou can give the List range a Name then use it for the Source. For example, I selected the range J1: J6 then typed TheNames into the Name box, thereby creating a Named Range. On the Data Validation dialog box, I typed in =TheNames into the Source box.

Change the Reference to the Named Range

Now let’s assume that we have to add a couple more names to the list. Instead of changing every cell that references this Data Validation list, we just change the reference to the Named Range. (Choose Formulas > Name Manager, select the Named Range, change the reference in the Refers to box, then click the green arrow to make the change and click Close.)

But if the list will grow over time, changing the reference should be done automatically with a dynamic Named Range formula. We’ll do this by using the OFFSET formula.

  • Choose Formulas > Name Manager
  • Select New
  • Type a Name in the Name box (I’ll use myNamesList)
  • In the Refers to box type =OFFSET(Sheet1!$J$1,0,0,COUNTA(Sheet1!$J:$J),1)
  • Click OK

Now select the cell or range with Data Validation and,

  • Choose Data > Data Validation
  • Select the Settings tab
  • In the Source box type in =myNamesList (or the Name you created)
  • Click OK

This Named Range formula is dynamic, which means the source list will expand when names are added to the list. If the list contains more than 8 values the drop-down list will have a scroll bar.

Excel – Pick from Drop Down List

Data Validation list source range expanded

Bonus Tips

One of the things we love the most about creating a drop down list in Excel is that the program reminds you to save your work before you click out. However, the program will not remind you to create a backup. If you do not have an automated backup system in place, we highly recommend implementing one. For example, you can download and save a copy locally to your desktop and USB drive. If you are using a thumb drive, store the saved copy offsite. Does this sound like overkill? Maybe. But you will not think so if you lose your first copy and then find yourself in need of it. 

How to use a drop down list in Google sheets. 

If you are not close to a device with Excel, you might have to use spreadsheets. Things in spreadsheets are simple. All you have to do in order to create a drop down list is to select the columns you need and afterward to go to Data. Then you will have to select data validation. 

In spreadsheets, data validation gives you the following criteria: List from a range, List of items, Number, Text, Date, Custom Formula, and Checkbox. Also, you can allow someone to type invalid data or to reject it by default. 

You can also create a Yes/No Drop down list, by using the criteria “List of items” and by typing in the box yes and no separated by a comma. The last step is to choose the option “On invalid data reject input”.

Another thing you can do with Google sheets is to search for the items in your list by just using a few letters from the word. This comes in handy when you have a long list of names or terms and you want to find a specified one or a group of them. 

How to Create a Drop Down List in Excel: Final Thoughts.

Now that you know how to create and use a drop down list in Excel and Google Sheets, you can have fun and do some easy exercises. Try to organize what each member of your family wants to eat for a week or use a spreadsheet as a grocery list.

With Google Sheets, you can also do some real time checkups with your friends or family members. Just share the spreadsheet with them and let them come up with the needed items for the events you are attending together. 

That’s it. We hope you enjoyed reading our article on how to create a drop down list in Excel with data validation. It’s not as hard as you might have thought.  Hopefully, you can now create a drop-down list that will meet your needs. Use as many drop lists as you need, now that you know how to make them it will only take you from a few seconds to some minutes. Anyways, Happy organizing!

How To Calculate Hours Between Two Dates in Excel

Recently I was asked how to subtract time in Excel (time difference) or how to calculate the number of hours between two points in time on different days. Since this was in a reader comment, I gave a brief answer that requires a fuller account here.

Dates and Times are all part of the master plan in Excel. Once you “get” the fundamentals, the rest is just icing on the cake.

A Date value in Excel looks like this: 40519
A Time value in Excel looks like this: 0.58333

Cell formatting changes how you see these numbers.

The Date: 7 Dec, 2010
The Time: 2:00 PM

How to Calculate Time Between Dates in Excel or the Duration Between Two Dates

If you want to calculate time between dates in Excel or the duration between two dates, you need to understand what they mean first. When you type a Date into Excel, you may never see the underlying number, like 40519, but it’s there nonetheless. This is a date serial number and it makes Date calculations easy.

You might ask, “Why is this such a weird-looking number?”. Well, the Excel folks started a numbering system with Dates. In Excel for Windows they gave 1 Jan, 1900 the serial date number of 1, then continued numbering until this day and beyond. So serial number 40519 represents 7 Dec, 2010.

In Excel for Mac they started numbering Dates beginning with 2 Jan, 1904. (don’t ask) So the serial date 40519 represents 8 Dec, 2014 (Actually it’s known as the 1904 date system. To be clear, Macs can change Excel settings to use the 1900 date system).

How to Subtract Time in Excel (Time Difference)

When you type 2:00 PM into a cell in Excel the underlying value is a fraction, but Excel interprets this as a time serial number and formats the cell accordingly.

Try typing 0.25 into a blank cell, then change the cell formatting to a TIME format, and you’ll get something like 6:00 AM.

As an aside, you can calculate this fraction for any time value during the day by taking the total number of seconds that have passed from midnight until your time value and dividing by 86,400 seconds in a day.

Image of an equation to calculate time difference

Dates and Times Together

In Excel the unit of time is “the Day,” a key fact to know. You’ll notice that Dates are integers, and Time is a fractional number. You can add the two together to get a Date/Time format.

So adding a Date serial number, like 40518, to a Time serial number, like 0.25, gives us 40518.25. Formatting the cell holding this value using “d mmm, yyyy h:mm AM/PM” will show 6 Dec, 2010 6:00 AM.

You can also enter something like 7 Dec, 2010 2:00 PM into a cell and Excel will recognize this as a Date/Time format. However, if you change the cell formatting to General, the underlying number is 40519.05833.

So hopefully by now you can see that subtracting two Date/Time formatted numbers can be done mathematically. Subtracting 6 Dec, 2010 6:00 AM from 7 Dec, 2010 2:00 PM is done by Excel “underneath the hood” as 40519.05833 – 40518.25 and the result is 1.3333.

Calculating Hours Between Two Dates in Excel

If we recall that the unit of time is “the Day,” this value represents 1-1/3 days of time.  Since there are 24 hours in a day, converting to hours is a simple multiplication 24 * 1.3333 = or 32 hours. (24 * 4/3 to be more precise)

Time Between Two Times / Dates

Finding the number of hours or the time between two times/dates is simple, just subtract the start date/time from the end date/time and multiply the result by 24 hours.

If you want to enter the dates and times separately (which is loads easier than typing in a date/time in one cell) then add the date/times together.

Hours = ((End_Date+End_Time)-(Start_Date+Start_Time))*24

Here’s a look at a typical worksheet designed to calculate the hours between two dates.

Image of an Excel table with dates and times

As you can see, the formula for Hours, in cell F2, shows in the formula bar. And row 3 contains General formatting so you can view the date/time serial numbers for row 2.

Change the formatting for cells B2:E2 to match what you normally use for Date and Time data entry.

The NETWORKDAYS Function

People also asked how to calculate the time between days but without taking into account weekends or specified holidays. The NETWORKDAYS function will help you to calculate the time period by excluding weekdays and specified holidays.

The formula looks like this: =NETWORKDAYS(start_date, end_date, [holidays])

Like in the previous case the start_date and end_date are required arguments, yet the argument [holidays] is optional. You can use this argument to specify which days should be excluded.

Image of an Excel list showing NETWORKDAYS formula

This time I used two random dates which contained two weekends and I added two desired dates under the holidays argument just to show you how it works.

If you need to add some weekends you will have to add one more argument (weekends) and use =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

After you calculated the days by using the NETWORKDAYS formula, to calculate the hours you can simply multiply the given number with the number of hours you need for as long as they stay the same.  Also, do not work harder by looking for complicated formulas to calculate simple things.

What else you should know:

  • If the start date is later than the end date the formula will generate a negative number.
  • The NETWORKDAYS formula will also include the start and the ending dates in the calculations, due to this if you have the same date the value returned will be 1.

If you want to find more formulas regarding the date and time feel free to check the following list of functions.

I hope this article helps you to better understand how to calculate hours between two dates in Excel. You can exercise what you learned today by calculating the time left until your next birthday, to do this you can also use the =Today function or the =Now function.

If you found this article helpful, please feel free to check our other articles about tips and tricks in Excel. We also have a dedicated section for details and formulas about dates and times in Excel.

Excel Spreadsheet Icon

How to Convert Excel to Google Sheets

Leave a reply

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

Leave a reply

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

Leave a reply

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

Leave a reply

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

Leave a reply

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.

Quick Navigation

Microsoft Excel Books FAQs
How We Reviewed
What We Reviewed
The Verdict

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.


Buy Now

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.


Buy Now

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.


Buy Now

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.


Buy Now

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.


Buy Now

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.


Buy Now

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.


Buy Now

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.


Buy Now

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.


Buy Now

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.


Buy Now

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

Leave a reply

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.

Quick Navigation

Entering the Data Correctly
Creating a Table
Adding Information
Cells That Can and Cannot Be Left Empty
Turning the Spreadsheet into a Table
Formulating the Database
Relational Database
Slave Tables
How to Create a Database in Excel | Conclusion
Entering the Data Correctly
Creating a Table
Adding Information
Cells That Can and Cannot Be Left Empty
Turning the Spreadsheet into a Table
Formulating the Database
Relational Database
Slave Tables
How to Create a Database in Excel | Conclusion

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

Excel on computer monitor

Image via pixabay.com

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

Person in front of silver laptop practicing how to create a database in excel

Image via pexels.com

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

spreadshee table cell

Image via pixabay.com

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

Person writing on how to create a database in excel

Image via pexels.com

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

Finger on block chain

Image via pixabay.com

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

Macbook on desk

Image via pexels.com

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?

Leave a reply

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.

Quick Navigation

What Is Google Sheets?
The History of Google Sheets
What Is Microsoft Excel?
Further Development of Microsoft Excel
Google Sheets vs Excel: Differences
Price
Saving
Collaboration
Customize Your Spreadsheet Program
Large Files
Conclusion

What Is Google Sheets?

Person typing on laptop computer

Image via pexels.com

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?

woman searching google sheets vs excel

Image via unsplash.com

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

Excel spreadsheet icon

Image via pixabay.com

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

Euro currency

Image via pixabay.com

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.



Related Posts Plugin for WordPress, Blogger...