Category Archives: Features

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

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?

Excel Tables

Image via pixabay.com

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

Employee learning on microsoft image

Image via pixabay.com

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.

  • Efficiently model and analyze almost any data
  • Zero in on the right data points quickly
  • Create data charts in a single cell
  • Access your spreadsheets from virtually anywhere
  • Connect, share, and accomplish more when working together
  • Take advantage of more interactive and dynamic PivotCharts
  • Add more sophistication to your data presentations
  • Do things easier and faster
  • Harness more power for building bigger, more complex spreadsheets
  • 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

Excel spreadsheet icon

Image via pixabay.com

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

person typing on silver MacBook

Image via unsplash.com

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

person holding clear and blue ballpen

Image via pexels.com

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.

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.

how to round in excel

How to Round Numbers in Excel: What You Need to Know

Use Excel’s rounding feature to round decimals or large numbers in a spreadsheet. Round with functions, round up, round down, or round numbers to a certain decimal place in Excel. In this article, we’ll show you how to round numbers in excel. If you use Excel a lot, you have probably noticed that it is frequently used to organize data in numerical form. While this is very helpful, sometimes you don’t really want to work with numbers like decimals. In certain cases, rounding your data up best suits your needs. In this guide, we are going to learn how to use Excel to do the rounding for us! Don’t sweat it. Using this feature is much simpler than you may think.

What is the Excel Round Function?

Have you ever heard of the Excel round function? Before we dive into how to round numbers in Excel, we need to address this question. While learning how to use different aspects of Microsoft software can often seem daunting, it is more simple than you may expect. The Excel round function brings a number to a given amount of rounded digits. You can round either to the left or the right of a decimal point.

how to round in excel

Why Should We Round?

To keep it simple, rounding numbers just makes them easier to deal with. First of all, if you are working with decimals, it can be such a pain to record them. Even worse, if you are presenting these numbers to someone out loud, reading decimals can get tedious.

Not just decimals are rounded, though. Whole numbers can be rounded too, most often to the nearest 10. Rounding is often used in business when dealing with money amounts or sales.

Instead of reporting in a meeting, “we sold $5,999,956.97 worth of products this year,” it is easier to say “we sold about $6 million worth of products this year.” If a business is looking for an estimate of numbers, they can easily use Excel to do so.

How to Round Numbers in Excel

Let’s say a company is trying to figure out its estimation of sales for the week by the transaction. Each transaction has a very different and specific number, but the company is just looking for rounded numbers. All of the sales are listed in Excel. How can we round them?

We’ll use a shortened version of their sales list as our example. Here are some examples of the types of numbers we are dealing with:

  • 56.09
  • 22.31
  • 43.33
  • 90.01
  • 87.55
  • 15.78
  • 25.36
  • 38.72

Using Functions To Round Numbers In Excel

Start by opening a new Excel sheet and listing your numbers in the first column. Excel uses functions to round. With the rounding function, you can use =ROUND, =ROUNDUP, and =ROUNDDOWN. For our first example, we are going to use the =ROUND function.

To begin, we are going to start by rounding just our first number, 56.09. Select the cell where you want the new number to appear.

For example, it may be best to put the new number directly next to the old number.

In the function bar, type =ROUND(A1,0) and hit Enter. A1 represents the cell we are round, while the 0 represents how many decimal places we would like to round to. Since we want to get rid of our decimals, we are typing 0. Our new number will appear in the cell. In this case, the number is 56.

Additionally, you can use the =ROUNDUP function to round up to the next whole number, as well as the =ROUNDDOWN function to round down to the next whole number. Let’s do this with our second number, 22.31.

In the cell directly next to 22.31, type =ROUNDUP(A2,0). This will take cell A2, or 22.31, and round it up to the next whole number. Hit Enter, and you will get 23. In the next cell over, type =ROUNDDOWN(A2,0) and hit Enter. This will round 22.31 down to the next whole number, or 22.

If the Numbers have Several Decimal Places

If you are working with numbers that have several decimal places, such as 45.6726865, you may want to narrow these numbers down to just 2 decimals.

To achieve this in Excel, list your numbers in column A. Select the column next to your first number and type the function =ROUND(A1,2). This will round your number to 2 decimal places instead of its high number of decimal places.

You can complete rounding actions with any number of decimal places you desire, like simply by changing the second number in your function.

Rounding Multiple Numbers at Once

If you are working on rounding numerical data, there is a good chance you are probably working with a lot of numbers. It wouldn’t be very convenient if you had to go through and round each of those numbers individually.

Luckily, Excel makes it easy to round multiple numbers at once while still using its rounding function.

There is a simple way you can accomplish rounding all of your numbers.

The way to do this is to list out all your numbers in the first column. Then select the cell next to your first column and enter the rounding function you want to use for all of your numbers.

Let’s use =ROUND(A1,2). Hit enter to get the result for that number. Then, select that same cell and move your mouse over the bottom, right-hand corner of the cell until you see a plus (+) sign. Click and drag down until you reach the last cell you filled out.

Excel will automatically drag your function formula down through the selected cells. It will then perform the function for each number you have listed, giving you your result in the rounded column.

It is also possible to drag the function down past your entered information.

Zeros will appear where there is no information in the first column. Additionally, if you need to enter more information, anything entered in column A will automatically incorporate the function in column B; the zero will then change to reflect the newly entered number.

How to Round Numbers in Excel: Final Review

Numbers can be difficult to deal with in general, but when we add decimals to the mix, sometimes we are asking for trouble. Rounding can be difficult, especially when you are using lots of them or very large numbers with several decimal places.

For a quick, easy solution to your rounding problem, follow this guide on how to round numbers in Excel. You will be a rounding expert in no time, and you’ll save yourself a lot of effort!

How to Delete Duplicates in Excel

How to Delete Duplicates in Excel: An Easy Guide

How to Delete Duplicates in Excel

One of the main functions of a Microsoft Excel spreadsheet is that it organizes any data set into manageable rows and columns which can be easily viewed, searched, and arranged. Here we will show you how to delete duplicates in excel the easy way.

Because of the way Excel organizes this data, it should be easy to manipulate, whether this means changing your ordering principles (alphabetical, according to date, according to amount), searching for particular entries, and adding or deleting information.

This tutorial will focus on how to identify and delete duplicate entries which might exist within an Excel spreadsheet that you’ve created.

Reasons For Deleting Duplicates

Anyone familiar with Microsoft Excel knows that there are a number of reasons you might want to search for and delete duplicates. Perhaps you’ve accidentally copied a row when working within an Excel spreadsheet. It’s also possible that you or someone else may have accidentally entered the same information twice on the same spreadsheet.

In any case, where you suspect there may be duplicate information, there is a better option than searching through the entire document using only your eyes and trusting you’ll find your duplicates. Microsoft Excel has made it easy to press a series of buttons which will tell the program to automatically search for, identify, and delete any duplicates you may have added by mistake.

In this tutorial, we’ll cover:

  • How to check for duplicates
  • How to automatically delete duplicates
  • Advanced filtering
  • An alternate method for older versions of Excel

Checking for Duplicates

check duplicates how to delete duplicates in excel

The first step to deleting duplicates is to identify them. Microsoft Excel can do this relatively easily. To identify duplicates, follow the steps listed below:

  1. Once you’re in the folder which contains your file, double-click on your Excel document file. If you’re looking for a document you’ve used recently, you can also open the document from the “Recent” section of the Open tab.
  2. Once your file is open, select the range of cells you wish to test for duplicates. You can do this easily by clicking on any cell and then pressing Ctrl-A (Select All).
  3. Once you’ve selected the range, click on the Home tab. Within the Home tab, select Conditional Formatting. Once you’ve done this, highlight Cells Rules, and then within that, select Duplicate Values.
  4. Once you’ve selected Duplicate Values, a dialog box should open in the middle of your screen. Within this dialog box, click OK. This box will also inform you as to what color the program will use to identify your duplicate values (eg. Light red fill with dark red text).
  5. The duplicate values within your list should now be identified in the color stated.

How to Delete Duplicates in Excel

data tools how to delete duplicates in excel

  1. Open your Excel document file by double-clicking on the file name. Alternately, if you’ve used the document recently, you can open an existing document from the “Recent” section of the Open tab.
  2. Once your file is open, select the range that you want to delete duplicates from. You can do this by clicking the entry that is in the top left corner of your chosen range. Once you’ve clicked on this, hold down the up arrow and Shift button. While you’re holding these buttons, click on the entry that is in the bottom right corner of your chosen range.
  3. Your chosen range should now be highlighted.
  4. Click the Data tab, which is a tab on the left side of the green toolbar at the top of the Excel window.
  5. Within the Data tab, choose Data Tools, and then Remove Duplicates. Once you’ve chosen Remove Duplicates, a dialog box will appear.
  6. Within this dialog box, leave all check-boxes checked and click OK. If you do not want to remove duplicates from all of your columns, deselect the columns you’d like to the program to leave alone before clicking OK.
  7. All of your duplicates should now be deleted from your table.

Note: The Remove Duplicates function will remove every instance of the information starting with the 2nd. Excel will automatically remove all identical rows (blue) except for the first identical row found (in yellow).

Another Option: Advanced Filtering

sort filter how to delete duplicates in excel

Another option for filtering out duplicates in Microsoft Excel is using the Advanced Filter option.

  1. Once again, you’ll begin this process by opening your Excel file.
  2. 2. Once the file has been opened, you can select all of the cells in the table by pressing Ctrl + A (Select All).
  3. Once your entire table has been selected, click the Data tab.
  4. From the Data tab, choose Sort and Filter, then click on the Advanced button.
  5. Once you’ve entered the Advanced Filter dialog box, check the box that says “Unique Records Only.”
  6. Once you’ve clicked “OK,” all duplicates except for the original should have been removed.

Deleting Duplicates in Microsoft Excel 2003 or earlier


If you’re using a version of Microsoft Excel from 2003 or earlier, the method for deleting duplicates will be a little different than those we’ve mentioned above.

  1. Click on cell A1, selecting it.
  2. Choose Data, Filter, and then AutoFilter.
  3. Click the Filter arrow in cell C1 and then choose Custom.
  4. Where it says Equals, change this to Greater Than. Enter 1 and then click OK.
  5. Once the duplicate values have been identified, you can delete them individually.
  6. Once a duplicate value has been deleted, its partner value will lose its highlight.

Conclusion

Microsoft Excel is an incredibly useful tool for anyone who has a set of data which needs to be organized. Once you’ve input your data into your Excel spreadsheet, you’ll want to check it for errors. One of the most common errors will be duplicate values that have been entered.

If you’ve followed our instructions, duplicate errors should be easy to identify and delete, regardless of which version of Microsoft Excel you’re using.

how to use goal seek in excel

How to Use Goal Seek in Excel

Excel has proven itself to be very useful in various situations over and over again. The list of Excel’s benefits seems to be never-ending.

It even has a tool for answering questions and forecasting information. The Goal Seek function in Excel is a great tool for those asking “What if” type questions.

Use this guide to learn how to use goal seek in excel as well as how to put it into action.

Why Use Goal Seek?

why_use_goal_seek_

 

The Goal Seek feature in Excel is basically used to create formulas. It provides information on cause and effect situations.

It determines what specific data will impact another set of data. This feature comes in handy in lots of scenarios.

In financial and sales situations, it can be used to determine what must be accomplished in order to reach a certain monetary goal.

It can also be used to calculate how many votes a candidate needs to win an election.

Once you have your Goal Seek formula in place, you can change any part of it to see how the formula as a whole change.

For example, a computer sales company has a profit goal of $3 million for the year.

Each of their computer systems cost $200, while additional accessories like keyboards cost $50 extra.

This company could use how to use goal seek in excel to determine how many of each product they’ll need to sell to reach their goal.

Here’s How To Use Goal Seek In Excel

To use Goal Seek, you will have to open a new sheet and enter the current information you have.

To make learning Goal Seek a little easier, we are going to use a simpler example.

Sample Scenario

how to use goal seek in excel

How To Start

To start, enter your information and functions into a spreadsheet in an organized table.

For our data, we’ll have the headings Playground, Movie, and Total in the first column.

Across the top, we’ll have the headings Votes and %. We have set up our table to calculate percentages base on the votes.

The goal will be to determine how many votes will it take to choose the Playground option.

Next Move

The next thing we need to do is select the cell we want to change. We want to make the Playground vote equal 66%, or 2/3, so we will select that cell.

Under the Data tab, locate the “What If Analysis” button. Click it to reveal the drop-down menu.

On this menu, you will see “Goal Seek.” Select this option, and a dialogue box will appear.

The first option you will see will ask you to set your cell. In our case, the percentage of Playground votes cell is D2, so we will enter that into the box.

Next, you will see “To value.” This is where you enter the desired goal. Our goal is 66%, so we will enter that into this box.

Final Steps

Finally, the last box will say “By changing cell.” This will be whichever cell you want to change in order to get your result.

We want to know how many votes will get us to 66%, so we want to change the number of playground votes. In our case, that is cell C2.

After you input all of your information, hit OK. Your information on your table will change to accommodate the goal number you have set.

In our case, we change the playground percentage to 66% based on the number of votes it needs.

When we hit OK, the numbers on our chart revealed that 24 out of the class’s 37 students would have to vote for the playground.

That is for them to reach 66% and establish a fair vote.

The Goal Seek function also shows us that for this to be true, 13 students will have voted for the movie.

Conclusion

 

how to use goal seek in excel

The Goal Seek function can be a very useful tool when trying to forecast sales, finances, votes, etc.

Numbers can be tricky to determine, and in important situations, it can be crucial to making decisions based off of accurate information.

The Goal Seek serves to perform exactly that.

Upon first use, the function can prove to be a little tricky. An important thing to remember is to make sure all of the functions in your table make sense.

The Goal Seek feature will not work properly if your functions don’t add up.

You may also get some funky numbers that don’t make sense if your functions don’t make sense.

It is a good idea to practice using this method a few times before you start using it for important projects.

Once you have the feature mastered, Goal Seek is an excellent tool for both professional and educational use.

Read through this guide on how to use goal seek in excel and follow the steps a few times to really nail down the skill.

This may come as a surprise to how useful it becomes. As a matter of fact, your business or job will be a lot easier.

Soon you’ll be forecasting information for all sorts of projects!

how to freeze a row in excel

How to Freeze A Row in Excel: A Practical Guide

Learn to how to freeze a row in excel (or even more than 2 rows) using this practical how-to guide.

When you are working with lots of data on your laptop or monitor, it is helpful to know how to freeze a row in Excel. After all, it is often difficult to compare one or more rows with others that you are working on at the bottom of the document. Excel’s freeze pane feature solves this problem. How? You can lock specific rows of data so that they are always visible to you as you scroll through the Excel sheet.

You may or may not know this, but a single Excel worksheet can contain as many as 1,048,576 rows. This program is well known for its ability to create vast databases of information, but how much of that information can you see on your computer screen? The answer, of course, depends upon the size of your computer screen. Even massive screens cannot contain every row of a large spreadsheet. Thankfully, Excel has an available feature allowing users the ability to freeze a row. In this article, we will teach you everything you need to know about how to freeze a row (or multiple rows) in Microsoft Excel.

how to freeze a row in excel

What Does It Mean to Know How to Freeze A Row in Excel?

Freezing a row within a worksheet will keep that row present at the top of your page while allowing you to scroll through the rest of the spreadsheet. Freezing a row is particularly helpful when you have several data points to consider.

How to Freeze a Row in Excel

Whether you are brand new to Excel or you have some experience, you will find out how to freeze a row in Excel below. Below, we will discuss different scenarios in which freezing a row could be useful and give examples of how to accomplish your desired results.

A few important things to remember when freezing rows in Excel:

  • Freezing rows is not limited to large spreadsheets
  • It is easy to unfreeze previously frozen rows
  • A row must be on screen to freeze it

Keep Your Headers at the Top of Your Worksheet

It is common practice for the top row in a worksheet to be a header row.

The header row contains information about the data found in the cells below each label. When you are working on a large spreadsheet, it is beneficial to keep your headers at the top of your worksheet while you scroll further down into the data.

To keep your headers at the top of your worksheet, you will need to freeze the top row. Keeping your headers at the top of the page will reduce the amount of time you spend scrolling and maximize your workflow.

To freeze the top row of your worksheet, scroll to the top of your spreadsheet. Scrolling to the top of your spreadsheet ensures that your header row is visible on your screen. In the Ribbon at the top of your screen select the “View” tab. In this tab, there are several available options.

To freeze your headers at the top of your worksheet, first ensure that your spreadsheet is in “Normal” view. You can find this setting in the first section of the “View” tab. If your worksheet is in any view other than simply click on “Normal” to select this view.

After you have made sure that your worksheet is in the “Normal” view, you will find the “Window” section in the “View” tab. In the “Window” group you will see a drop-down arrow labeled as “Freeze Panes.” Click the drop-down arrow to open the menu and select “Freeze Top Row.” You should find this as the second option in the drop-down menu.

With all of these steps completed, you will be able to scroll up and down your worksheet without disrupting the position of your header row.

Freezing More Than One Row in Excel

There are times when you might have more than one row at the top of your worksheet that you want to keep visible while scrolling. Excel makes it very simple to freeze more than one row.

To freeze more than one row you will begin by placing your cursor in the row below the lowest row you want to freeze. For example, if you are trying to freeze the first four rows of your worksheet, place your cursor in the fifth row.

After you have placed your cursor appropriately, click on the “View” tab. Once again you will click on the drop-down arrow beside the “Freeze Panes” icon. To freeze multiple rows, you will select “Freeze Panes.” This option should be the first option in the drop-down menu.

When you freeze a row, a dark line will appear underneath the bottom-most frozen row. This line will show you where your frozen rows end, and your non-frozen rows begin. You can now confidently scroll up and down your worksheet while keeping your first few rows in sight.

Unfreezing Rows

When you are finished scrolling through your worksheet’s data, you may want to unfreeze the rows you have previously frozen. Unfreezing rows is just as simple as freezing them. First, select the “View” tab. Next, click the drop-down arrow on the “Freeze Pane” icon. Click “Unfreeze Panes” to return your worksheet to normal.

Freezing Rows Vs. Splitting Panes

Excel offers a second option for keeping specific rows in view while scrolling through the rest of your worksheet. This option is known as the “Split Pane” feature. When you use the “Split Pane” feature of Excel,  your screen will split into two or four. You can scroll through each of these panes independently from one another.

In contrast to freezing rows, split panes do not hold a particular set of rows at the top of your worksheet. This flexibility can be useful in some situations, but remember, you cannot freeze a row while splitting panes.

Split panes can be useful in some situations, but they will not always keep your headers in place at the top of your page. To ensure you can view your first rows without scrolling back and forth, choose to freeze rows instead of splitting panes.

Although being able to freeze the first row (or first several rows) is quite handy. It is important to realize that you can only freeze rows from the top down. You cannot freeze rows from the bottom up or freeze a row independently in the middle of the worksheet.

Freezing a Row in Excel: Life Hack

Freezing or unfreezing worksheet sheets commonly includes different mouse clicks, however, I’ll demonstrate to you an approach to complete this assignment with a solitary keystroke. For the uninitiated, solidifying sheets enable you to secure sections or lines that you determine along with the left-hand or potentially top of Excel’s worksheet zone. As you look to one side or down, the solidified segments or lines stay set up so you can generally see worksheet headings or the underlying segments. When you never again need the lines or segments secured, you thus can unfreeze them. 

Commercial 

To do this errand in Excel 2007 and later, first snap on the worksheet position you wish to solidify. Next, go to the View tab, click on Freeze Panes, and afterward make a determination from the submenu, for example, Freeze Panes.

To open the lines or segments, explore to the View tab, pick Freeze Panes, and afterward Unfreeze Panes. It’s somewhat more straightforward in Excel 2003: pick Window, and afterward Freeze Panes or Window, and afterward Unfreeze Panes, individually. 

In any case, you streamline this procedure down to a basic keystroke of your decision in Excel 2007 and later. Select Commands Not in the Ribbon, and after that look down the subsequent rundown until you discover Freeze Sheet Panes. Either double tap on this direction or snap once on it and snap Add to add it to your Quick Access Toolbar. On the off chance that you as often as possible stop and unfreeze sheet sheets, utilize the bolts on the right-hand side to move the Freeze Sheet Panes order with the goal that it’s the main direction on the rundown. Snap OK to close the Excel Options exchange box. 

When you’ve put the direction on your Quick Access Toolbar, you would now be able to press the Alt key to uncover the numeric easy route for the Freeze Sheet Panes order. In the event that you made it the primary order on the rundown, you would now be able to squeeze Alt-1 to stop or unfreeze sheet sheets. You should at present position your cursor as before when solidifying sheets, however, you can sidestep exploring through the View tab and the subsequent Freeze Panes submenu. 

How to Freeze a Row in Excel: Final Review

Learning how to freeze a row in Excel develops a useful skill to have. Keeping the first row visible while scrolling through your entire worksheet is practical. Frozen header rows can save you time and frustration, especially when dealing with large spreadsheets. Regardless of the size of your worksheet, the process of freezing rows is simple and will make your work easier.If you want to know how to freeze cells in Excel so rows and columns stay visible, we have a tutorial for that here.

How to Make a Line Graph in Excel

How to Make a Line Graph in Excel

Organize data by learning how to make a line graph in Excel. Present information over time with this simple guide to creating a line graph.

As you may have seen throughout your various uses of Excel, the program is a great way to store and organize information. Your worksheet is meant to lay out all the information you have in a way that is easy to follow.

But did you know that Excel has ways to organize this information even further? With Excel, you can organize your information into line graphs, or line charts, as they are sometimes referred to. This simple guide will show you have to present your information in a more visual manner and with more organization.

What is a Line Graph?

Line graphs are one of the simplest tools to create within Excel. With this said, its simplicity does not outweigh its importance. Even Leonardo Da Vinci said, “simplicity is the greatest form of sophistication.” If you didn’t already know, they are popular in the field of statistics and science, as they show trends and are simple to plot out. So what is a line graph? In short, it is a chart used to demonstrate a change in quantitative data over a given period of time.

Independent values (e.g. time variables) are plotted along the horizontal x-axis. Dependent values (e.g. sales, prices, etc.) run along the vertical y-axis. Any negative values are located below the x-axis. The lines that rise and fall along the graph indicate trends within the data set. Upward slopes show an increase of values whereas downward slopes show a decrease.

When Should You Use a Line Graph?

A question that goes hand in hand with how to make a line graph is when should you use one? Below, we have listed optimal situations for line graphs to work.

    • To give a visual of trends and changes
    • When you need to visualize a large and complex amount of data
  • To show relationships between multiple sets of data

When You Should Not Use a Line Graph

Just as there are optimal times to use a line graph, there are also times when you should avoid using them. Below, we have listed situations where line graphs are not optimal to use.

    • Large stand-alone data sets
    • Continuous data (you should use a bar graph instead)
    • Percentages and proportions (you should use a pie chart instead)
  • Schedules

How to Make a Line Graph in Excel

Why Use Line Graphs?

Line graphs are a great way to show information over time. Information over time can be very crucial regarding businesses. Whether you are trying to present rises and drops in sales or figuring out which month brings in the most customers, line graphs are excellent resources to utilize.

For example, the owner of a zoo may want to know which animals are the most popular and which ones are lacking popularity. Over the timespan of six months, they’ve been tracking how many people visit the giraffes, the lions, the monkeys, and the elephants.  Now they want an easy way to organize this information to present it.

There are several ways you can present information, but lines graphs are easily one of the simplest ways. One of the great things about line graphs is that you can have multiple lines on one graph. Keep reading to learn how to make one yourself.

How To Make A Line Graph In Excel

Let’s continue using our zoo example to demonstrate how to make a line graph in excel. You should start by laying out your information in Excel like you normally would. The zoo information can be categorized by listing the animals across row 1. The six months, let’s say May through August, can be listed down column A. The numbers are filled in accordingly.

Once you have all your information entered in, you are ready to create a graph. Keep in mind that since there are multiple points of data here, you will have multiple lines on your graph. The great thing about line graphs is that you can use as much data over time as you want – just try not to overwhelm the graph, or it may be hard to read.

Beginning with Creating your Line Graph in Excel

To begin your graph, highlight the entirety of the information being represented. Click on the Inset tab and locate the Charts section. Find the icon that says “Inset Line or Area Chart.” Click on that to see a drop-down menu of various charts. Here, you will see a variety of options. Feel free to explore all of these options to see what they look like. For now, we are going to select a chart from the first section labeled 2-D Line.

Click the very first option. This will give you a chart that has multiple, different colored lines intertwined. This option is the easiest way to show each individual’s information in comparison with others. If you find that it is hard to tell where each point of information is, choose the chart that says “Line with Markers.” This graph will add points to each piece of information, which can make it easier to locate them.

As you have already noticed, there are several varieties and options to choose from. You can decide which option is best for your purposes once you have nailed down how to use these graphs.

Editing Line Graphs

You’ve created your first line graph! It looks great, but perhaps you want to change it up a little. Excel offers many ways to customize and edit your line graph once you’ve made it.

For one, you can change the graph’s overall style. Select the graph you have created and click on the Design tab. Under this tab, you will see several design options to choose from.

These options change the color, the fonts, the lines, the background, and can even do things like add numbers right on your graph’s points. Take a minute to scroll through and find a style that suits your taste and your needs.

Additionally, you can add on other individual elements to boost the intelligence of your graph and make it more detailed. Still, under the design tab, locate the “Add Chart Element” button. Click to find a drop-down menu listing items you can add such as Axes, Chart Title, Data Labels, Error Bars, and more.

Rather than adding each element one by one, you can also take a look at the “Quick Layout” menu located right next to the “Add Chart Element” menu. The Quick Layout feature gives you various layouts including different combinations of chart styles and additional elements. You may find a chart you were going to create yourself already exists in this menu!

Changing Colors

The “Change Colors” menu gives you a quick and simple way to change the colors on your graph. From this menu, you can generate your own personal color scheme to your taste – even adding and editing line markers.

Finally, it is important to remember that once you’ve created your line graph, the information on it is not final. If you realize you’ve put in the wrong numbers or something has changed, you can easily update your graph by updating your original information. Simply change the information in your Worksheet, and the graph will automatically update itself.

The Advantages of Using a Line Graph to Demonstrate Data

A line graph gives a few advantages contrasted with other information portrayal strategies, for example, a bar or pie outline. There are three qualities which make it normal in numerous kinds of work, incorporating substantial use in data use or longitudinal studies.

A standout amongst the most significant advantages is the longitudinal fitness of a line chart. The timetable for a followed occasion can without much of a stretch be plotted along with an x/y pivot. This demonstrates a realistic portrayal of the ascent or fall of information focuses. Missing information can be plotted along the line with some level of sureness or mistake likelihood. This is additionally observed with chart assembly, which is regularly observed in monetary information focuses. For instance, the cost a customer is eager to pay for a thing can be crossed with a beneficial offer point for the organization to achieve the perfect market cost. 

Another advantage of the line diagram is the correlation factor as referenced quickly above. At least two things can be contrasted with cross focuses, which takes into account at the base recounted proof of an association. It ought to be noted not all line diagrams will have a similar beginning stage on a straight chart speaking to a longitudinal report. The production of a starting point along the pivot can show the continuation or incorporation of various information in time. 

The last advantage, as odd as it sounds, is the general shading generation of the line diagram. Line diagrams take into account a lot more approaches to speak to the information focuses. A reference chart can utilize shading to the contrast between thought about things, yet hues don’t generally interpret crosswise over various print mediums. Line diagrams can utilize shading or distinctive line styles, for example, dabs or dashes. While it appears to be a basic thing, this bit of leeway permits line diagrams to more readily move to dark/white paper print.

How to Make a Line Graph: Final Review

While there are still many details on how to customize and edit graphs, this guide gives you a great starting point to use this convenient feature. Use line graphs as an excellent way to organize and present data changing over time. As simple and easy as it is to create and use, it can also make a huge difference in any project or presentation you are making!

5 New and Creative Ways To Utilize Excel

Excel may not always be the most exciting program to utilize at home or at the office, but it’s an essential one that keeps over millions of users organized and able to do their tasks with relative ease.

Whether you are relatively new to the world of Excel or you’ve been using it on a daily basis for over a decade, learning how to be creative and have a little fun will keep things a little more exciting and even improve your skills.

While many fun and creative projects in Excel are designed for school-aged users, who says that kids are the only ones who can have the fun?

Although we can’t begin to explain (or even understand) the creative process of Tatsuo Horiuchi’s Excel masterpieces, here are five things to try out the next time you use Excel (but you might want to make sure you try it out on your break if you’re at work).

Create Patterns For Your Hobbies

create hobby lists

Image via Tech Advisor

Do you enjoy quilting or knitting? Have you been thinking about creating a mosaic out of tile for your outdoor patio? While the design might be a little different, depending on what your pattern or project is, an Excel spreadsheet can make the whole process easier.

While the steps for this design chart are geared towards knitters who come up with their own patterns, you could use it for various projects that require making a small scale pattern or just staying organized.

The steps are simple and are as follows:

  • Open your workbook and create a copy of the “master” tab
  • Continue by right-clicking on the tab and select “move or copy” on the menu
  • The next step is to “make a copy” and then click Ok

After you add letters, colors, and symbols to the cells (to represent each stitch/element of the pattern), save the file. Like other spreadsheets, you can edit easily until you create the perfect design.

Make A Calendar

These days there are plenty of programs and templates that allow you to customize and print off a calendar, but if you don’t know how to make a calendar using Excel now is the time to learn. Despite living in a world where you can sync calendars with someone else, by just using your phones, a calendar can still come in handy.

Follow these steps to make a calendar that you can use every month and edit as needed.

Step 1

Using the merge-and-fit feature and setting the width of your columns at the same time, you will create the weekday header and a title for each month. You can enter the weekdays by entering in one day, such as Sunday, and then drag the cell to the right using the bottom right corner.

Tip: For an easy-to-read text, make the days of the week a 12-point font and bold. If your weekdays extend beyond the size of the column all you need to set the width to 15 or 20 (Home to Cells to Format to Column Width).

Step 2

After your weekday headers are set up, you can add the month to the top of the spreadsheet. Many people spend a lot of unnecessary time manually entering information into Excel. Since Excel is all about being efficient, you can use the formula “=TODAY()“, this tells Excel to use the current date.

Step 3

The next step is to format the title with a bold font of 20 or 22 and then (Home to Format to Format Cells to Date and then select the correct month title).

If your spreadsheet looks unbalanced, you can easily fix the problem by highlighting all the cells above the weekday header and use your Merge & Center button; everything should look centered now.

Step 4

Now that you have your weekday and month headers taken care of you can make the calendar template. Use the Merge & Center feature again but use some extra empty cells (about five or six) to create a large cell for a calendar square.

When your large cell is highlighted, you’ll copy and paste into the other days. This step duplicates a box for every day of the week, and the result ends up looking like a rough draft of a calendar grid.

Step 5

Ready to add grid lines? Highlight the entire calendar, click on the grid tool in your home tab and then select “All Borders,” this should create a more polished look. Now you can manually add dates, notes, and other information. Your calendar is ready to share or print.

Create Your Own Crossword Puzzle

creating a crossword puzzle on excel

Image via YouTube

How many times have you done the daily crossword puzzle and thought you could easily make on yourself? Whether you want to make a special crossword puzzle for your kids or you just want to be like your crossword creator idol, Will Shortz, you can create a puzzle using Excel.

Before you decide to create a puzzle in Excel, it’s best to create one on paper just to get a general idea of what you want.

Step 1

After you start a new document in Excel, select a number of rows to equal one more than the vertical size of your crossword grid. If your grid is 31 spaces, select 32 rows in Excel.

Step 2

Right-click in A1, then select Row Height. You will enter 20 and then Ok. Follow the first step for the horizontal size of your puzzle. Then right click A1, Column Width, and enter 2.5, then Ok.

Step 3

Starting at B2, select the grid area and then click on the Borders button (which is in the Font box). Select All Borders.

Step 4

Type in your answers to the puzzles and make sure that you only use one letter per cell. Make sure the letters match up, as you sketched on your paper draft.

Step 5

Holding down the Control key, click each of the empty cells. Then click on Fill Color button, choose Black, Text 1.

Step 6

After you fill in all the empty cells, select the grid and delete all of the answers you typed in. Using a small font, type the clue number in the first cell of each answer space. Next, you can type your clues (one per cell) to the right of your grid.

Create A Sudoku Puzzle

There are easily just as many Sudoku puzzle fans than there are crossword puzzle fanatics, and there are probably many who love both. If you love Sudoku, but hate when a co-worker steals that section of the daily paper first, you can create your own to print out for the office (or keep for yourself).

You can take the easy route and download a program specifically for Excel, or you can watch some tutorial videos to learn how to create your own puzzles. Either way, it’s a fun way to use Excel in your downtime.

Make Your Own Trivia

Need to help your kids with their homework and want to make it more interesting or are planning a DIY Trivia Night with some friends? Use your Excel spreadsheet to create your own trivia game. Follow these steps to become the next Trivia Master:

Step 1

Open a new worksheet, naming one sheet, Quiz and the other, Answers.

Step 2

In B1 of your Quiz sheet, type the number of questions you want and in B2, title it Your Score. In Row 4, create Question, Answer, and Result (or something along those lines).

Step 3 

In Column A, type the questions (one per cell) then type the correct answers in the corresponding cells on the Answers worksheet. If you need to, expand column A, so it’s easier to read the trivia questions.

Step 4

To create a formula to check the answers, type =IF(B5””,IF(B5=Answers!A5,”Correct”, “Incorrect”),””) into cell C5. This formula also works for scorekeeping. 

In the section, Number of Questions, use the following formula:

=COUNTA(A:A)-1

In the section for the score, use this formula:

=COUNTIF(C5:C5000,”=Correct”)

Step 5

After you’re done entering the formulas, you can format your worksheet by selecting cells A4 to column C and format as a table. To get rid of any unwanted table features, go to the Design Tab and then to Convert to Range.

To keep your answers secure, you can go to column B and right click on Format Cells and then Protection Tab. You can either choose to unlock for editing or lock to keep your answers secure.

For additional tips on creating a trivia game on excel, visit this link.

Related Posts Plugin for WordPress, Blogger...