Category Archives: Features

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

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.

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

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)

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)

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)

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)

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)

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)

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

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)

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)

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)

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

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

The History of Microsoft Excel: How It Came to Be

Leave a reply

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

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

What Is Microsoft Excel?

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

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

Popular Features of Microsoft Excel

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

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

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

History of Microsoft Excel Up Until This Point

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

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

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

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

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

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

Improvements to the Original

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

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

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

Conclusion

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

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

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

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

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

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.

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.

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

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

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

Leave a reply

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.

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: An Easy Guide

Leave a reply

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

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:

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.
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).
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.
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).
The duplicate values within your list should now be identified in the color stated.

How to Delete Duplicates in Excel

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.
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.
Your chosen range should now be highlighted.
Click the Data tab, which is a tab on the left side of the green toolbar at the top of the Excel window.
Within the Data tab, choose Data Tools, and then Remove Duplicates. Once you’ve chosen Remove Duplicates, a dialog box will appear.
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.
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

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

Once again, you’ll begin this process by opening your Excel file.
2. Once the file has been opened, you can select all of the cells in the table by pressing Ctrl + A (Select All).
Once your entire table has been selected, click the Data tab.
From the Data tab, choose Sort and Filter, then click on the Advanced button.
Once you’ve entered the Advanced Filter dialog box, check the box that says “Unique Records Only.”
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.

Click on cell A1, selecting it.
Choose Data, Filter, and then AutoFilter.
Click the Filter arrow in cell C1 and then choose Custom.
Where it says Equals, change this to Greater Than. Enter 1 and then click OK.
Once the duplicate values have been identified, you can delete them individually.
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

Leave a reply

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?

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

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: A Practical Guide

Leave a reply

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.

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

Leave a reply

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

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

Leave a reply

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

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

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

Image via Western Sydney University

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.

The 20 Most Useful Excel Shortcuts to Utilize

Leave a reply

Learning the shortcuts on any program can cut the time it takes to produce a document. With a program like Excel that has so many uses, knowing some of its shortcuts can make navigating, entering formulas, and setting up worksheets quicker to do.

Reasons to Use Shortcuts

Excel has many uses, although creating spreadsheets is probably its most common one. Within a spreadsheet, both written and numerical data is often used, so learning some of the shortcuts for both types of data can help save time when trying to organize information.

Image via Keyshorts

Using shortcuts takes less time because you won’t need to use the mouse to go to the ribbon to find the function or formula that you need. Also, by memorizing the shortcuts, your work will be more precise. You will be less likely to make mistakes that will need to be corrected by yourself or someone else.

Creating spreadsheets can be a tedious job, especially if there are large amounts of data to organize. However, by learning Excel shortcuts, the task can become easier because it will be quicker to do, and your accuracy will improve as well.

Here are 20 Excel shortcuts for Windows and Mac that can help you work more efficiently.

Workbook Operations

When you’re ready to start a new worksheet or continue working on one, these shortcuts can help open a current workbook or a new one and save it when you’re done finished entering information on it.

Open Workbook: Ctrl + O (Windows and Mac)

This shortcut helps you open a workbook on which you’ve been working. Once you press the keys, the recent workbooks box will appear, and you can choose the one you need.

New Workbook: Ctrl + N (Windows and Mac)

If you need to open a new workbook, this shortcut works for both the Windows and Mac operating systems.

New Worksheet: Shift + F11 (Windows and Mac)

This shortcut adds a new worksheet to a workbook.

Save As: F12 (Windows) ⌘+ Shift + S (Mac)

The F12 function key helps you save the worksheet or workbook on Windows. The combination of the keys shown above will do that on a Mac computer. After the keys are pressed, the dialog box opens so you can insert the name of the workbook.

Close Excel: ALT + F4 (Windows) Ctrl + Q (Mac)

When you’re finished with Excel, this shortcut closes the program, not just the workbook.

Ribbon Operations

If you’re new to Excel, you may not be familiar with the ribbon. The ribbon is the box above the worksheet that displays the tabs and buttons for the various commands on the application.

Show or Hide Ribbon: Ctrl + F1 (Windows) ⌘ + OPT + R (Mac)

Use this shortcut to open or hide the ribbon box.

Show Ribbon Accelerator Keys: Alt (Windows) n/a (Mac)

The accelerator keys are other shortcuts on Excel. When you select Alt on a Windows computer, a letter or number appears under the tabs on the ribbon. These include the:

File
Home
Insert
Page Layout
Formulas
Data
Review
View
Help
Save Icon

When you press the letter or number under the tab, letters or numbers will appear under the commands for that task. You can then use shortcuts to do whatever you need.

For example, when you press Alt and W under the View tab, a Q appears under Zoom. Then, selecting Q allows you to enlarge the information on the worksheet.

Editing Operations

After you use some of the shortcuts, you may want to make corrections by undoing something, or you may want to copy data to paste it into another row or cell. These shortcuts are self-explanatory.

Copy: Ctrl + C (Windows and Mac)

Paste: Ctrl + V (Windows and Mac)

Undo: Ctrl + Z (Windows and Mac)

Cut: Ctrl + X (Windows and Mac)

Spellcheck: F7 (Windows and Mac)

Formatting Operations

Some formatting functions will be used more than others, so the most common ones are listed here. These shortcuts don’t need any further explanation either.

Bold: Ctrl + B (Windows) ⌘ + B (Mac)

Italic: Ctrl + I (Windows) ⌘ + I (Mac)

Underline: Ctrl + U (Windows) ⌘ + U

Data Editing Operations

If you need to fill the same information in other cells or rows, these shortcuts will help do it.

Fill down from cell above Ctrl + D (Windows and Mac)

This shortcut allows you to fill cells with the same information from the cell above it. So, if you’re tracking inventory and the costs for a group of products are the same, you could use this shortcut to fill in the information on the worksheet.

Fill right from the cell to the left: Ctrl + R (Windows and Mac)

If you need the same information from a cell to the left of the one you’re on, this shortcut allows you to copy the information to the cell.

Find and Replace: Ctrl + F (Windows and Mac)

If you need to find information on a worksheet, this shortcut allows you to find it, and replace it with new data if necessary.

Calculations Operations

Probably the most common calculation on an Excel worksheet is addition, followed by multiplication. This shortcut allows you to add or multiple cells or rows or insert functions.

Insert Autosum Formula: Alt + = (Windows) ⌘ + Shift + T (Mac)

Insert Function: Shift + F3 (Windows and Mac)

There are 200 Excel shortcuts in Windows and 200 for Mac, but these 20 are probably the most popular and frequently used shortcuts for people who use the application at work.

How to Memorize Excel Shortcuts

Image via Udemy

The way to learn anything is by doing it. While some people are visual learners, most people memorize physical tasks better by doing them and using shortcuts on a keyboard is a physical task.

Some of the shortcuts used in Excel are also applicable to other Microsoft applications. For instance, you can use the find and replace shortcut, Ctrl + F, with MS Word. A new word document can also be opened in MS Word by using the shortcut Ctrl + N. Using F7 allows you to spellcheck on Excel and MS Word.

The duplication of some of the shortcuts can make them easier to learn and apply to applications. However, if you need more guidance as you learn shortcuts for Excel, there are other resources.

Laminated Cards

Although many of the shortcuts are easy to learn, some of them may be harder because you don’t use the function or formula very often. Instead of hunting for the correct way to perform an action or looking it up on the Internet, you can purchase laminate shortcut cards and keep them on your computer.

The laminated cards include all the shortcuts for both Windows and Mac computers listed together. Since they are laminated, the cards can last for as long as you need it. The cards are available from online retailers or stores that sell office supplies.

Online Classes

If you want to learn more about Excel, including the shortcuts, several websites offer Excel tutorials or classes. The website corporatefinancialinstitutite.com offers the free Excel Crash Course that includes learning the shortcuts, functions, formulas and other tips.

The site excelexposure.com also offers free Excel lessons and has a free shortcut tip sheet available on their site. There are several other websites that also offer free Excel courses or tutorials that include learning the shortcuts you use every day.

Write Shortcuts Down

Since you may only use a few of the shortcuts, write down those you use the most to help you learn them. Writing things down helps many people remember information better than just reading it. A study published in Psychology Today says that writing down information boosts memory and the ability to retain concepts.

Write the shortcuts you use most horizontally on a piece of paper, cut it out and then tape it to the top of your monitor. It can be used as a reference in case you need it, but you will probably retain many of them after you’ve written them and used them at work every day.

Learning the shortcuts that you don’t use often can be more difficult, but if you print out a tip sheet or buy the laminated sheets, you can keep them nearby for reference when and if they are needed. Some of them you may not ever use, but it is better to have the information nearby in case you need it.

By learning Excel shortcuts, such as the 20 listed above, you will be able to produce spreadsheets faster and more accurately than you did before. Using a mouse and the ribbon to complete actions on a worksheet takes more time, and if you are unsure of how to perform an action on Excel, it can be inaccurate.

However, by taking the time to learn the shortcuts you use the most, you could be confident that you’re performing the right and your work will be more precise.

Pivot Tables (PV Table): Everything You Need to Know

Leave a reply

When working with Excel spreadsheets, it can be difficult to extract the information you need from large sets of data. Pivot Tables (pv table) offer a great way to quickly condense and analyze, and present your data, allowing you to make informed decisions in both your professional and personal life.

Pivot Tables allow you to effortlessly summarize large amounts of data into a simple format that’s easy to read and analyze. You can subtotal numeric data, sort information into subcategories, or create custom calculations and formulas to focus your results.

Here, we’re going to discuss everything that you need to know about a PV Table, including:

Why you should use Pivot Tables
How to create a Pivot Table
What you can do with Pivot Tables

What are Pivot Tables (AKA Pv Table)?

Pivot Tables, also known as Pv Tables, are an Excel tool that allows you to organize data in a way that’s easy to understand. You can use data from a spreadsheet or import a database table to access the information you need. Excel is able to connect to external sources such as SQL Server tables, Azure Marketplace, Office Data Connection (.odc) files, XML files, Access databases, and text files. Making a Pivot Table won’t alter your original data in any way, but instead will arrange it into a tabular format that makes sense and is easier to read than the original spreadsheet.

The Advantages of Using Pivot Tables

The main function of Pivot Tables is to help you organize large quantities of data in a way that’s quick to analyze. You can filter and sort groups into a table that’s more user-friendly than a raw data set or spreadsheet. Pv Tables also make it easy to expand or collapse rows and columns to narrow down your results, giving you a more detailed picture of important data while cutting out unnecessary background noise.

Not only do Pivot Tables make it easier for you to track data more effectively, but they also make it easier to present information. Whether you’re speaking to family members, co-workers, or supervisors, Pivot Tables give a clear and concise picture of your data that’s easy on the eyes.

If you’re not happy with the layout of your Pivot Table, Excel makes it easy to manipulate and reformat information. Not only can you sort, filter, and group data. But you can also add, rearrange, remove, or change the order of fields. You can also easily change the Pivot Table form, choosing between Compact, Outline, or Tabular.

Tables are in compact form by default, but this may not suit your needs if you want headings for Row fields. If this is the case, you can switch your Pivot Table to Tabular Form, which displays one column per field and provides space for field headers, or Outline Form, which displays subtotals at the top of every group.

Creating Recommended PV Table

Pivot Tables are easy to make with just a few clicks of a button. If you’re new to Excel or Pv Tables in general, you may want to start out using Recommended Pivot Tables. This feature automatically comes up with a layout to match your data set. If you aren’t pleased with the final result, you can always experiment by tweaking rows and columns. To create a Recommended Pivot Table:

Click on any cell in your original data set or table range.
Go to Insert > Tables > Recommended Pivot Table.
Excel analyzes your data and presents you with several options based on the categories it detects in your data.
Select whichever table looks like it will best suit your needs. And then hit OK to create a Pivot Table on a new Excel sheet.

The Recommended Pivot Table feature is a relatively new one, introduced in 2013. It’s only available for users that have the Office 2013 suite or above. If you have an earlier version of the software, you’ll have to create Pv Tables manually.

Manually Creating Pivot Tables

Creating a Pivot Table manually is just slightly more complex than making a Recommended Pivot Table. In addition, it gives you more control over your end results and only takes a few more steps. Here’s how you can manually create a Pivot Table to display your data:

Click on any cell in your original data set or table range.
Go to Insert > Tables > Pivot Table.
A box will pop up displaying the Create Pivot Table dialog. You can select and name a range of Excel cells, or import from an external data source. If you want to analyze multiple tables at once, check the “Add this data to the Data Model” box at the bottom of the popup screen.
On this screen, you can also choose whether you want your report to be opened in a new sheet or an existing worksheet. If you choose to place your table in a current worksheet, you need to select both the file and the cell where you want your Pivot Table to be stored.
Click OK, and you’ll see Excel create a blank Pivot Table and display the Pivot Table Fields list. Here, you’ll select the checkbox for any field you want to add to your Pivot Table.

Using Pivot Tables

Once you’ve finalized the formatting of your PV Table, you can take things one step further. You can turn it into a Pivot Chart.

This gives you an even more powerful way to display data. Especially if you’re planning on using it in a presentation. Pivot Charts add visualizations to data in the form of a graph or other chart type.

This makes it easy to summarize data and spot trends and patterns over time. Pivot Charts automatically update when you adjust your Pivot Table.

Conclusion

Excel is a powerful tool both at home and in the office, but datasets and spreadsheets can get confusing. Pivot Tables help you to make the most of your data. It allows you to eliminate unnecessary information and highlight what’s important to you or your business.

By using Pivot Tables, you can quickly come to conclusions and make informed decisions based on large caches of data. Pivot Tables also make it easy to wow bosses, coworkers, and investors during presentations.

Follow the steps laid out in this article. Spare yourself from a headache by effortlessly organizing and analyzing large data sets using Pivot Tables.

Whether you’re keeping on top of your home life or climbing the corporate ladder. Pivot Tables can come in handy when analyzing and presenting data.

Learn How to Unhide Cells in Excel Using Keyboard Shortcuts or the Home Menu

Leave a reply

Learn how to hide and unhide columns in Excel using keyboard shortcuts or the Home Menu methods.

Today’s post will illustrate how unhide columns in Excel, as well as hide them.

How to Hide and Unhide Data in an Individual Cell

While Excel does not allow you to Hide and Unhide individual cells using the Hide/Unhide command, here’s a trick showing how to hide just one cell:

Choose the cell or cells you want to hide
Select Cells from the Format menu and the Format Cells dialog box will appear
Select the Number tab
From the list of format categories, select Custom
Enter three semicolons (…) in the Type box

This causes the information in the cell to disappear, and it won’t print. However, you will be able to see the cell information in the Formula Bar. To unhide that individual cell, enter any other type of information.

Hiding Data in Columns and Rows

Hiding data in columns and rows still allows you to reference the data in formulas and charts. Also, hidden formulas that contain cell references will still update if the data in the referenced cells changes.

How to Hide Data in Excel Using Shortcut Keys

First, we’ll discuss how to hide columns and then we will discuss rows.

How to Hide One or More Columns

The shortcut keys for hiding columns is: [Ctrl] + [zero]. Here are the steps:

Choose any cell in the column you want to hide, making it the active cell
Press and hold Ctrl
Press 0 [zero] while holding Ctrl
The entire column with the active cell and any data it contained, will be hidden

Note: Hide multiple columns with this shortcut by highlighting at least one cell in each column you wish to hide, then repeat steps 2 and 3 above.

How to Hide One or More Rows

The shortcut keys for hiding rows is: [Ctrl] + [9]. Here are the steps:

Choose any cell in the row you want to hide, making it the active cell
Press and hold Ctrl
Press [9] while holding Ctrl
The entire row with the active cell and any data it contained is hidden

Note: Hide multiple rows with this shortcut by highlighting at least one cell in each row you wish to hide, then repeat steps 2 and 3 above.

How to Hide Columns or Rows Using the Home Menu

This method has three options on how to unhide columns in excel, depending on the object selected when the menu is accessed.

To Hide a Single Column or Row

Click on the header of the column or row that you would like to hide and the column or row will be highlighted
On the Home tab, in the Cells group, select Format
Under Visibility, select Hide & Unhide, and then Hide Columns (or Hide Rows)
Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box
The selected column or row and any data is hidden (the header is also be hidden)

To Hide Adjacent Columns or Rows

To hide two or more side-by-side columns or rows:

In the column or row header, click and drag across all of the columns or rows you want to hide
On the Home tab, in the Cells group, select Format
Under Visibility, select Hide & Unhide, and then Hide Columns (or Hide Rows)
Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box
The selected column or row and any data is hidden (the header is also be hidden)

To Hide Non-Adjacent Columns or Rows

In the column or row header click on the first column or row you want to hide
Press and hold Ctrl while clicking once on each additional column or row you want to hide.
Release Ctrl
On the Home tab, in the Cells group, select Format
Under Visibility, select Hide & Unhide, and then Hide Columns (or Hide Rows)
Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box
The selected column or row and any data is hidden (the header is also be hidden)

How to Unhide Columns in Excel

To Unhide All Hidden Rows and Columns Simultaneously

Select all of the cells by pressing Ctrl+A or the gray Select All button in the upper left corner of the worksheet.

Note that if your worksheet has data and the active cell is above or to the right of the data, Ctrl+A selects the current region. Press Ctrl+A again to select the entire worksheet.

On the Home tab, in the Cells group, select Format
Do one of the following:
- Under Visibility, select Hide & Unhide, and then Unhide Columns (or Unhide Rows)
- Under Cell Size, click Column Width or Row Height, then type the value that you want in the Column Width or Row Height box
Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box

To Unhide the First Row or Column of Your Worksheet

If you’ve hidden the first row or column, take the following steps:

Select the first row or column using one of the following:
- In the Name Box next to the formula bar, type A1
- On the Home tab, under Editing, click Find & Select > Go To. Type A1 in the Reference box, then click OK.
On the Home tab, in the Cells group, select Format
Under Visibility, select Hide & Unhide, and then Unhide Columns (or Unhide Rows)
Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box

If you want to learn how to freeze cells in excel so rows and columns stay visible, click here.

The Drop Down Basics: How to Make a Drop Down List in Excel

Leave a reply

This article shows you how to make a drop down list in excel.

It provides two ways for you to quickly and easily make drop down lists in an Excel spreadsheet.

You can follow the instructions to create a new drop down list that will help streamline efficiency when collecting information in your spreadsheet.

A dropdown list can be a useful thing in your spreadsheet. Especially if you want to make it easier for others to enter information on your sheet quickly.

With a dropdown list, you can give other users a quick set of options instead of having to type in each piece of information in every cell.

It is also helpful for the person responsible for the spreadsheet because it standardizes the data you want to collect, which may cut down on confusion or problems when it’s time to inspect your data or make calculations.

This article will go through a step-by-step process of how to make a dropdown list in Excel.

We will show you:

where to find the dropdown feature
how to enter a list of options that will appear in your dropdown list
and even how to use a dropdown list that you have in a different spreadsheet

Let’s get down to business and learn how to make a drop down list in excel.

How To Make A Drop down List In Excel: Getting Started

Today we are going to use an example of a group of friend’s favorite types of fruit. There are two ways you can do this.

The first involves making one spreadsheet. The other requires you to make two.

For the method involving two separate sheets.

The first spreadsheet, which will be the one where you will eventually insert your dropdown list, will contain the list of all the friends.

The second sheet will be where you create the possible selections of favorite fruits that will make up the dropdown list.

Excel sheet with names and favorite fruit how to make a drop down list in excel

Here is the list of people who will be taking the survey:

list of fruits how to make a drop down list in excel

And here is the sheet containing the list of fruits that the participants can select:

Now that we have our two spreadsheets set up, we can go about making the list of fruits in Sheet 2 into a dropdown list that users with access to Sheet 1 can select.

Finding the Dropdown Feature

The dropdown feature is not the easiest feature to find in Excel. However, with our help, you’ll be an expert in no time.

In Excel, you must scroll to the “DATA” tab in the menu.

Be sure you are currently on the sheet that you want your dropdown list to appear. In the section named “Data Tools” you will see the feature for “Data Validation” which should look like this:

Once you have found the button, begin by selecting the cell, or in this case the range of cells, you want the list to appear in.

Then click the data validation button.

After that, a dialog box will appear with several options.

Under the “Allow” box, click the dropdown menu and select “List,” which will look like this:

After selecting “List,” you will then tell the spreadsheet what source you want to use, in this case, the list of fruit we made in Sheet 2.

Note that in the “source” line there is now a somewhat complicated looking formula. That’s just telling spreadsheet 1 to look to Sheet 2 to find the items that will be in your dropdown list.

Once you press enter, you will be taken back to spreadsheet 1 with the names of the people taking the survey.

In the column underneath the “FAVORITE FRUIT,” the cells now have a dropdown list. You’ve now made a simple dropdown list for your spreadsheet.

If you want to do all this in a single spreadsheet you will follow some of the same steps for setting up spreadsheet one, but don’t need to make a second sheet.

Instead of pointing your dropdown list to Sheet 2, when you fill out the “source” in the data validation box, you type in the possible options for the dropdown box.

The one-sheet method is useful when you have a limited number of responses, for example, if you are gauging attendance for an event and your only answers are attending/not attending.

But, as in our example, if you have a long list, it’s much easier to make everything on a separate sheet. Using two sheets will also make it easier to modify the list later.

More on Dropdown Lists

Now that you have made your dropdown list, there are a few things that you need to decide on. And you also have a few things to check.

First, if you only want the users to see one spreadsheet, you can hide the other spreadsheet by right-clicking on the tab. And then selecting “hide” from there.

However, if you do it all on one sheet other users could make changes to the dropdown list. Thus, if you are the only one who wants to be able to control what is on the lists. It’s better to follow the two sheet method.

You can also protect the spreadsheet or choose to lock it down so that no other user besides yourself can make changes.

One thing to check on is making sure that the cells are wide enough to display the whole entry.

In our example above, all of the words are relatively short and this isn’t a problem. But it is always a good thing to check before you send your spreadsheet to a bunch of people.

Modifying Your Dropdown List

Maybe you forgot to add your own favorite fruit, or just want more options. Don’t worry. It is quite simple to add items or remove them, from your dropdown list.

If you have made your dropdown list with two spreadsheets, which we feel is the better method, all you have to do is go to that sheet and add the items that you want to be included.

The dropdown list will then automatically be updated in the other spreadsheet.

Similarly, if you want to delete an item you just go to the sheet. From there, you delete whichever items you no longer want to include in your dropdown list. Easy as that!

If you created a dropdown list by manually entering the options, you just have to go into Data Validation again. And then enter the new items to the list from there.

Conclusion

You now know how to make a dropdown list in Excel!

Dropdown lists are useful any time you want users to enter specific data that you have already identified. It can keep things uniform and is more efficient for other users who are entering data.

Where is Control+Home for Excel on a Mac

I wrote a post stating that I could not find the Windows Ctrl+Home keyboard shortcut equivalent on a Mac. Well I’m here to tell you that I found the keyboard shortcut combination that does the same thing on a Mac. The Excel Gods are with me. Hallelujah!

Finding My Way Home

The key to finding this elusive keyboard shortcut is in the Keyboard Viewer. On your Mac select the Apple icon () and click System Preferences… Select Keyboard, and then make sure to click the Keyboard tab. Check the box for: Show Keyboard & Character Viewers in menu bar.

Excel home key on mac

Click the Keyboard Viewer icon in your Mac menu bar and a nice replica of your Mac keyboard will appear.

Keyboard Viewer Mac

You will notice that this viewer reflects the keys you tap on your keyboard. The screen shot above shows the Command and Shift keys are depressed. The Keyboard Viewer will also show different symbols when you press various keys, like fn, Control, Option, Command, etc.

This is where I noticed something interesting. While depressing the fn key, the left arrow button changes its angle to point up about 30 degrees. Knowing that allowed me to do a little testing in Microsoft Excel 2011 for the Mac.

Excel Control+Home Key on Mac

What I found is that the Windows Control+Home keyboard combination can be replicated on a Mac by either of the following keyboard shortcut combinations. This is the home key on Mac:

fn+Command+Left Arrow

fn+Control+Left Arrow

Another mystery solved.

Keyboard Shortcuts

Another aid in finding keyboard shortcuts comes in the form of an overlay for your Mac keyboard. The kind folks over at Excel Skin™ gave me an overlay that slips over the Mac keyboard and shows, via color coding, a wide array of shortcuts that work in Excel for Mac. Here is why you might want an excel skin.

Control + Home in Excel for Mac

One of my all-time favorite keyboard shortcuts in Excel is CTRL+Home, but on a Mac there is no Home button. Hence a constant source of frustration these last two years.

I finally decided to do something about that and recently figured out a solution using VBA and the Personal Macro Workbook. But before I go straight to the answer, let me tell you how I got there.

My Journey

I knew that VBA was going to enter into the equation, so I started to record a macro on a Windows PC while using the Control+Home keyboard shortcut. What I found out is that Excel does not record that keyboard shortcut. Nothing, nada, zip.

I then noticed that Control+Home acted differently, depending if the sheet panes were frozen or not. When sheet panes are not frozen, the Control+Home shortcut took me to cell A1. When the sheet panes are frozen, then the upper left corner cell of the window was selected.

To make a long story short, I found out that the active window’s scroll row and column were being selected when the CTRL+Home shortcut is executed in Excel for Windows.

The Control+Home Macro

I also knew that if a Chart sheet were selected the macro would fail, so I crafted the following macro to mimic the CTRL+Home shortcut on a Mac. (Works on a Windows PC too.)

Sub GoHome() ''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Make sure the active sheet is a worksheet, ' then locate the active window's scroll row and ' column, and activate that cell. ''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim lngRow As Long Dim lngCol As Long

If ActiveSheet.Type = xlWorksheet Then lngRow = ActiveWindow.ScrollRow lngCol = ActiveWindow.ScrollColumn Cells(lngRow, lngCol).Activate End If End Sub

The next thing I did was create a shortcut for the macro, but realized that the macro would only work with the current workbook. Rats!

I wanted the macro work automatically on every Excel file so I chose to store this macro in the Personal Macro Workbook, which solved the problem.

However the shortcut key combination I assigned did not work in Excel for Mac. There was a conflict. It seems that COMMAND+OPTION+H is a reserved keyboard shortcut for the Mac. I found out this shortcut hides all windows except the one that is active.

How I Created the Control+Home Keyboard Shortcut on my Mac

Here are the steps I took, with a few false starts along the way.

Open Excel 2011 and choose Tools > Macro > Record New Macro…

Type a name for the macro. I used GoHome.

Enter a Shortcut key. (I used h, which didn’t work so I changed to g as you’ll see below.)

Where you see Store macro in: click the drop-down and select Personal Macro Workbook.

Click OK.

I got a warning that the keyboard shortcut Option+Command+h was reserved.

So I chose g instead and clicked OK.

Next choose Tools > Macros > Stop Recording. This will effectively end the macro recording without recording anything.

Choose Tools > Macro > Macros…

Click the Step Into button on the Macro dialog box, which will take you directly inside the macro in the VBA Editor. (Note: While this takes you directly to the macro, it also starts the macro running inside the VBA Editor.)

Click the square Reset button to stop the macro program execution. (Note: If you know how to navigate the VBA editor, you can skip this last step and choose Tools > Macro > Visual Basic Editor and then locate the macro.

Select the entire GoHome code from this article. Or click this link and copy from a new window.

Copy the text,

Switch back to the Excel VBA editor,

Select the entire GoHome subroutine, and

Paste the code.

Close the VBA editor by choosing Excel > Close and Return to Microsoft Excel.

Now here is the important part so pay attention. You have to save changes to the Personal Macro Workbook. You will be asked to do this when you Quit Excel.

Choose Excel > Quit Excel and the following dialog box will appear.

Click the Save button when asked, “Do you want to save the changes you made to the Personal Macro Workbook?”

Run the GoHome Macro

Now lets check it out. Open Excel 2011 and select any cell that is not A1, then use the shortcut (mine was Command+Option+g) and watch the active cell change to cell A1.

If your shortcut doesn’t work you can set it now. Choose Tools > Macro > Macros… and select the GoHome macro, then click Options. Type in a shortcut key and click OK.

Now choose any cell in the top left quadrant of the current window, like C5. Choose Window > Freeze Panes. Next select any cell except C5 and run the shortcut combination for the macro, and watch the curser jump to cell C5.

The Personal Macro Workbook will load each time you open Microsoft Excel so it’s always in the background, and you don’t get that annoying “enable macro” pop-up screen.

Microsoft Excel Books FAQs

1. What Is Microsoft Excel?

2. Who Needs Microsoft Excel Books?

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

4. Where Can I Buy the Best Excel Books?

How We Reviewed

What We Reviewed

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

Summary

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

Summary

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

Summary

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

Summary

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

Summary

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

Summary

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

​​​​​Summary

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

Summary

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

Summary

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

Summary

The Verdict

What Is Microsoft Excel?

Popular Features of Microsoft Excel

History of Microsoft Excel Up Until This Point

Improvements to the Original

Conclusion

Using Dates with the Remove Duplicates Feature

How to Delete Duplicates in Excel

Steps to Removing Duplicate Data in Excel

What is the Excel Round Function?

Why Should We Round?

How to Round Numbers in Excel

Using Functions To Round Numbers In Excel

If the Numbers have Several Decimal Places

Rounding Multiple Numbers at Once

How to Round Numbers in Excel: Final Review

Reasons For Deleting Duplicates

Checking for Duplicates

How to Delete Duplicates in Excel

Another Option: Advanced Filtering

Deleting Duplicates in Microsoft Excel 2003 or earlier

Conclusion

Why Use Goal Seek?

Here’s How To Use Goal Seek In Excel

Sample Scenario

How To Start

Next Move

Final Steps

Conclusion

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

How to Freeze a Row in Excel

Keep Your Headers at the Top of Your Worksheet

Freezing More Than One Row in Excel

Unfreezing Rows

Freezing Rows Vs. Splitting Panes

Freezing a Row in Excel: Life Hack

Commercial

How to Freeze a Row in Excel: Final Review

What is a Line Graph?

When Should You Use a Line Graph?

When You Should Not Use a Line Graph

Why Use Line Graphs?

How To Make A Line Graph In Excel

Beginning with Creating your Line Graph in Excel

Editing Line Graphs

Changing Colors

The Advantages of Using a Line Graph to Demonstrate Data

How to Make a Line Graph: Final Review

Create Patterns For Your Hobbies

Make A Calendar

Create Your Own Crossword Puzzle

Create A Sudoku Puzzle

Make Your Own Trivia

Summary