The post 9 Excel Print Settings You Probably Don’t Know About appeared first on Mad About Excel.

]]>So let’s begin without any ado.

This cool tip will help you print the row and column titles of your data on every page when you want to take a print of a large set of data.

Suppose you want to take a print of a large data set. Normally, the row and column titles will be printed only at the beginning of the data and not on the subsequent pages. This can make reading the data in the subsequent pages difficult. You can avoid this trouble by printing your row and/or column titles on every page.

The steps to do this are given below.

- Go to Page Layout -> Page Setup -> Print Titles

- Page Setup Window will open. Go to Sheet tab and specify the following:
**Print Area**: Select the entire data which you want to print**Rows to repeat at top**: Row titles which you want to print on every page**Columns to repeat at left**: Column titles which you want to print on every page

- Click OK

Done! Now when you’d print the data, the row titles and/or column titles will appear at the top/left on every page.

Suppose you have comments in your worksheet and want have those comments to be printed along with the other data in the worksheet. Now if you print the comments in the same way as it appears on screen, chances are it will block the contents of some other cells and make your print look clumsy.

A better way is to print all those comments at the end of pages.

The steps to do this are given below.

- Go to File Tab -> Print -> Page Setup -> Sheets tab
- In Print section, select “At the end of the sheet” in the Comments drop-down
- Click OK

Now, all the comments will be printed at the end of the sheet.

This option solves one of the most common problems that one faces while trying to take prints of Excel documents. You might have been in a situation where you wanted to print the whole document on a single page, but are not able to do so because of the size of the document.

In such situations, you can use ‘Scaling’ option to adjust your entire data to fit into a single page.

The steps are given below.

- Go to File tab -> Print
- In the ‘Settings’ area, you’ll find the option to scale the sheet to fit on a single page. It is set to ‘No Scaling’ by default. Here you can choose to:
- Fit Sheet on One Page
- Fit All Columns on One Page, or
- Fit All Rows on One Page

Although this is a very useful feature, over-scaling the data to fit on a page can affect the quality of the printed output. So you need use this option diligently.

Suppose the data that you want to take a print of is not big enough to cover the whole page. If you want you can take a print of such data in a way that the data gets printed at the center of the page.

The steps are given below:

- Go to File tab -> Print -> Page Setup -> Margins tab
- In ‘Center on page’ area you’ll see two options:
**Horizontally**: It will align your data into the center of the page horizontally**Vertically:**This will align your data into the center of the page vertically

- Select one or both the options and click OK

Suppose you want to print an Excel spreadsheet and want to start your page number from a specified number (say 6) in the header/footer. Excel gives you the option to specify that number. The rest of the pages will follow that page number.

The steps to do this are given below:

- Go to File tab -> Print -> Page Setup -> Page tab
- In the input box “First page number”, enter the number from which you want to start you page numbers
- Click OK

Errors don’t look good in spreadsheets, even more so when these sheets are printed. Excel provides you the option to replace the errors in your worksheets with one of the 3 options – <blank>, — (Double Minus), or #N/A.

Here are the steps.

- Go to File tab -> Print -> Page Setup -> Sheet tab
- Select replacement value from ‘Cell errors as’ drop down
- Click OK

You can specify whether you want to print your pages in vertical order or horizontal order.

Steps are given below:

- Go to File tab -> Print -> Page Setup -> Sheets tab
- In the ‘Page order’ area, you’ll see two options. Select the appropriate one.
- Down, then over (vertical)
- Over, then down (horizontal)

You’d be amazed by the variety of things you can insert in your Headers and Footers. The following is the list:

- Page Number
- Number of pages
- Date
- Time
- File Path
- File Name
- Sheet Name
- Picture

Note that you can insert *more than one* of these options. You can also *format* text and image.

The steps to insert custom header / footer are given below.

- Go to File tab -> Print -> Page Setup -> Header/Footer tab
- Click on ‘Custom Header…’ or ‘Custom Footer…’ button, as applicable
- In the Header / Footer dialogue box, you can insert one of more of the given options in either left, right or center section of the header or footer. Select your options and apply any formatting changes if you want.
- Click OK

Done! Your custom header and footer would now appear on all the pages.

Show Margins option makes it really easy for you to adjust the margins by simply dragging them, thereby eliminating the need to do trials and errors by entering different margin number.

Follow the below steps:

- Go to File Tab -> Print
- Below the print preview, on the bottom right, you’ll see a ‘Show Margins’ button. Click on it. It will show all the margins in the preview.
- Drag the margins as per requirement

Excel provides more than enough options for you to print worksheets in exactly the way you want to print. I really hope that the above tips will help you print Excel sheets easily.

If you think, I’ve missed a point please don’t think twice before commenting below. I’d really appreciate that.

See ya soon! Happy Excelling!

The post 9 Excel Print Settings You Probably Don’t Know About appeared first on Mad About Excel.

]]>The post How to Insert a Pivot Table in Excel – Pivot Table Basics appeared first on Mad About Excel.

]]>Pivot Table, as you might be knowing, is one of the most sophisticated and useful features of MS Excel. I believe most Excel users must have used this feature at some point and many use it quite regularly. But there is still quite a large group of people who work on Excel but are not aware of the usefulness of Pivot Tables and hence don’t want to take the trouble of learning it. Again, there are many users who think Pivot Tables is a very difficult tool to learn and simply give up before starting.

So, I thought of writing an article which covers all the basics of Pivot Tables and explains the concept in a very simple manner.

Pivot Tables is actually a very simple but equally useful tool to learn. It is used to make sense of raw data and modifies the data in such a way that one can extract useful information from it and take decisions based on it.

I wouldn’t lecture you on the usefulness of Pivot Tables, but would now take you straight to the practicals. Download the example file by clicking here and then follow the steps given below. This would not be a comprehensive discussion on Pivot Tables. Rather, this article would get you started and will familiarize you with Pivot Tables.

Click HERE to download the Example File.

Let’s get started.

Let us now learn how to insert a pivot table in an excel file. I hope you have downloaded the example file by now. If not, please download and open the file so that you can work along with me.

In the example file, you’ll see that there are 580 rows of data having a total of 6 fields. The data shows the sales figures of different products on different dates and the carriers used to ship the items.

We have the data all right, but can we make any sense out of it? Hardly any. Let’s make a pivot table from this data and see how we can use this data.

Before inserting a pivot table, make sure *any* cell in the data is selected. Then go to **Insert > Pivot Table**. The Create Pivot Table dialogue box will pop up. You’ll see that Excel has automatically selected the data for you. You can either chose to insert the pivot table in the same worksheet or in a new worksheet. Let’s simply click OK for now to insert the pivot table in a new worksheet.

A new sheet would get inserted in the workbook and Pivot Table Fields pane will appear on the screen. Now drag Category field into the Filters area, Product field into the Rows area and Amount field into the Values area.

The table thus created is what we call a pivot table. This Pivot Table shows us the amount of sale for each product.

Now let me explain the logic behind what I have done. I dragged the Product field in the Rows area because I wanted to have the different products to appear in the rows one below the other. Next, I dragged the Amount field in the Values area, because I wanted to know the total sales of each product.

So now we know that ‘Furniture and Décor’ is the highest selling item and Clothing, Apparels and Lug is the lowest selling item. In other words, we can take decisions on the basis of such analysis, which is not possible with just raw data.

Now let us see the use of Filter field. I had dragged Category field into the Filters area. I did this because I wanted to know the total sales for each product in a single category. So if I select, say, Baby Products from the drop-down I can see the sales figures for the different products in the baby products category.

Now let’s learn how we can sort data in a pivot table to make it even more helpful. Just right-click on any cell in the Sum of Amount column, go to Sort and select Sort Largest to Smallest. And now we can see that the products are arranged in the order of their sales figures.

You may now say that you don’t want to know the total sales for each product. Instead, you want to know the number of units sold. Let me show you how you can change the type of calculation that you want to use. Just right-click on any cell in the Sum of Amount column and select Value Field Settings. This Value Field Settings dialogue box will open where you’ll have the option to select the type of calculation you want.

Select Count and click OK. We can see that 157 out of 580 units sold were Furniture and Décor.

You might be wondering why I have left the Column area blank. Let me explain. The pivot table that we have made above is a one-dimensional pivot table. We just know one information from it and that is the number or the amount of sale for each product.

Now, drag Product Carrier field to Columns area. We now have a two-dimensional pivot table from which we can extract even more information. For example, if you want to know how many units of Sports and Outdoor products were shipped via UPS we can easily get the answer. 45! Great isn’t it?

So, above was an introduction to the Pivot Tables feature in Excel. I hope you would have done the exercise with me, and if you did you would have got a good clarity on what pivot tables are and how powerful this tool is. The more you practice using pivot tables, the stronger your data analysis skills will become.

This article is just the beginning. There is a lot more that you can do with Pivot Tables. If you really want to master this powerful tool, I’ recommend you join the **bestselling course **by Chris Dutton called** **Microsoft Excel – Data Analysis with Excel Pivot Tables.

I also recommend you read my other articles on Pivot tables as well by clicking HERE.

Lastly, I’d ask you to comment below and tell me what you liked or didn’t like about this article. I’d love to hear from you.

See you soon with another useful Excel article.

Chao!

The post How to Insert a Pivot Table in Excel – Pivot Table Basics appeared first on Mad About Excel.

]]>The post How to Learn Excel and Be a Spreadsheet Champion appeared first on Mad About Excel.

]]>I am often asked, “Hey Vikram, you blog about Excel. Tell me something. How does one *learn *Excel?” This got me thinking about all the methods I’ve used and still use to master this software. I made a list of all those methods and today I’d like to tell you – my readers – about how can one go about learning this great piece of work by Microsoft called MS Excel.

In this article, I’ll first discuss the broad areas that one can consider learning. For each of these areas, I’ll give a list of resources which you might consider using to master the respective areas. In the end, I’ll give a list of some other resources that you may use for your continuous learning and to improve your skills further (learning Excel is a never -ending process you see!).

So, let’s begin…

Microsoft Excel is not a software that can serve only one specific purpose. It can be used in a variety of ways by a variety of people to do a variety of tasks. Its scope is really wide and what you want to learn in Excel essentially depends on what you want to use Excel for.

I divide the different learning areas in Excel into 9 different categories, which I have listed below with their meaning and significance. These categories are not totally distinct in the sense that one particular topic in Excel can fall under more than one of these areas. I’d advise you to read these fully and then decide which are the areas in Excel that you’d want to master first.

For each area, I’ve listed a few books and courses that I highly recommend. These books and online courses are the best that I could find and are sufficient. **You don’t need to keep looking around for better resources. I’ve already made the effort for you.** Most of these courses have been personally taken by myself, and I’ve read most of the books that I’d recommend below. So instead os wasting any time in looking for the ‘best’, I’d advise you to take action and enroll in few of these courses and purchase a few of these books and start your learning journey. I’ve made this list after a lot of research and would recommend to use it as a guide. I’ll keep updating the list (in fact this whole post) whenever I come across a better course or book.

So let’s start looking at the different areas you might consider learning in MS Excel.

The knowledge of Excel ‘basics’ is essential for *all *users of Excel, no matter what their purpose is. Why? Because, Excel Basics is nothing but what it is – basics. It is the foundation of the knowledge one gains in Excel in order to use its basic features. ‘Basics’ is not essentially a topic by itself. It is used to refer to the understanding of the basic Excel features and the methods to use these features. It involves the knowledge of the different components of Excel window and worksheets. It also includes the knowledge of basic Excel shortcut keys and basic Excel functions. After learning Excel basics, one is able to use MS Excel for basic purposes viz. making calculations, making and formatting cells and data tables etc.

**Recommended Courses**

#1 Excel 2016 – The Complete Excel Mastery Course for Beginners by Steve McDonald

#2 Microsoft Excel 2016 Beginner Level Course by Hudson Courses

#3 Microsoft Excel Step by Step Training for Beginners! by Jed Guinto

**Recommended Books**

#1 Learn Excel 2016 Essential Skills with The Smart Method by Mike Smart

#2 Microsoft Excel Start Here The Beginners Guide by C. J. Benton

Keyboard shortcuts are not something that you can use in isolation for any purpose – whether basic or advanced. The knowledge of keyboard shortcuts complements your knowledge in other areas by enabling you to work much faster in Excel worksheets. The knowledge of keyboard shortcuts is a basic, but essential requirement for any Excel user to become more efficient.

**Recommended Course**

Ultimate Excel Shortcuts Course – Become 10X Faster on Excel by CA Vikram Narsaria

Excel is basically a calculation-based software. Calculations and analysis are the main purposes for which Excel is used. And the knowledge of Excel functions and formulas is essential in order to make calculations and do analysis in Excel. Some functions serve basic purposes – for example, SUM, COUNT, LEN, etc. Knowing these functions is imperative for any Excel user. Again, there are some advanced functions which are recommended only for advanced users.

**Recommended Courses**

#1 Microsoft Excel – Advanced Excel Formulas & Functions by Chris Dutton

#2 Microsoft Excel – Advanced Formulas And Functions by Infinite Skills

#3 Excel: Learn basic and advanced formulas quick and easy by Adam Holczer

#4 Master Excel Formulas Tips & Tricks by Scott Falls

**Recommended Books**

#1 Excel 2016 Formulas and Functions (includes Content Update Program) (MrExcel Library) by Paul McFedries

#2 Microsoft Excel Functions and Formulas by

#3 Excel Formulas and Functions For Dummies by Ken Bluttman

Data Analysis is not an essential, but a highly recommended area to learn in Excel. Data Analysis means using a raw set of data and extracting useful information from it based on which decisions can be taken. Again, there are some basic Data Analysis tools that all Excel users are expected to know – for example, sort, filter, conditional formatting, etc. Then there are some advanced Data Analysis tools like Pivot Tables, What-if Analysis, Solver, Analysis ToolPak, Power Pivot, etc. Pivot Tables is a common tool used for data analysis purposes even by basic users. The other tools are only used by advanced users.

**Recommended Courses**

#1 The Data Analyst Skills Training course (DAST) with Excel by Skillfin Learning

#2 Excel Data Analysis: Produce Great Reports, Basic Statistics by Tom Vorves

#3 Advance Analytics with Excel – data analysis toolpak/ Solver by Gopal Prasad Malakar

#4 Data Modelling and Analysis with Excel Power Pivot **(Bestselling) **by Ian Littlejohn

#5 Microsoft Excel – Data Analysis with Excel Pivot Tables **(Bestselling) **by Chris Dutton

**Recommended Books**

#1 Excel Data Analysis For Dummies by

#2 Microsoft Excel Data Analysis and Business Modeling by Wayne Winston

Charts and Diagrams can be considered as a sub-area of Data Analysis. All users who use data analysis tools in Excel don’t necessarily use charts and diagrams. Charts and Diagrams are a great way to show a visual representation of meaningful information from a data after analyzing the data using pivot tables, etc. Excel offers a variety of charts and there are several options to modify charts to suit one’s needs and style. The knowledge of charts and diagrams is imperative for one who wants to use Excel worksheets in presenting data in a visual manner.

**Recommended Courses**

#1 Excel Charts: Visualization Secrets for Impressive Charts **(Bestselling)** by Leila Gharani

#2 Excel 2016 Course – Excel Advanced Charts by Mach 42 Productions

**Recommended Books**

#1 Data at Work: Best practices for creating effective charts and information graphics in Microsoft Excel by Jorge Camoes

#2 Excel 2013 Charts and Graphs (MrExcel Library) by Bill Jelen

Excel Dashboard is a tool used mostly by managers and business leaders in tracking key metrics and to take decisions based on them. It is normally a one pager and generally contains various charts, tables and interactive views that are backed by data. This is not something that is required to be learned by every Excel user. If you really need this, you should definitely go ahead and master making Excel dashboards.

**Recommended Courses**

#1 Excel Dashboards Masterclass by Andreas Exadaktylos

#2 Excel Dashboards: Build a Dashboard from Scratch! by Abdelrahman Abdou

#3 Excel Dashboard Secrets – Create Awesome Excel Dashboard by Amey Dabholkar

#4 Excel Dashboard – Basic to Advanced by Yoda Learning

**Recommended Books**

#1 Excel Dashboards and Reports by Michael Alexander

#2 Dashboards for Excel by Jordan Goldmeier

If you are a Finance professional, you’d surely be knowing about the need to learn Financial Analysis and Financial Modelling. For others, this might not be a useful area to learn. Financial Analysis uses Advanced Excel and Data Analysis techniques to manipulate and present financial information in a way that is useful for an organization in order to take strategic and tactical decisions.

**Recommended Courses**

#1 The Complete Financial Analyst Course 2017 **(Bestselling) **by 365 Careers

#2 Excel Crash Course: Master Excel for Financial Analysis by Tim Vipond

#3 The Financial Analyst Skills Training (FAST) course 2017 by Skillfin Learning

**Recommended Books**

Financial Analysis with Microsoft Excel 2016 by

Excel VBA is definitely a necessary skill to have if you don’t just want to *use *Excel, but what you really want is to *program* Excel in such a way that it works like magic. Excel VBA (Visual Basic for Applications) is essentially the language in which Excel has been programmed, and if you want you too can learn Excel VBA and use it to speed up your everyday repetitive tasks in Excel and perform some really cool tricks. In other words, Excel VBA is nothing but Excel programming. So, learn it if you really want to become an Excel super-user. Excel VBA can also empower you to build programs based on Excel. I’d recommend users to learn the other areas in Excel first and then only come to learning Excel VBA.

**Recommended Courses**

#1 The Ultimate Excel Programmer Course **(Bestselling)** by Daniel Strong

#2 Ultimate Excel VBA **(Bestselling)** by Mark Talbert

#3 Excel VBA Macros: Hyper-disambiguated Excel VBA Programming by Grant Gamble

#4 Become an Excel VBA Expert for Beginners by EDUmobile Academy

**Recommended Books**

#1 Excel 2016 Power Programming with VBA (Mr. Spreadsheet’s Bookshelf) by Michael Alexander

#2 Excel VBA in easy steps by Mike McGrath

#3 Excel 2016 VBA and Macros (includes Content Update Program) (MrExcel Library) by Bill Jelen and Tracy Syrstad

‘Advanced Excel’ is a term that is commonly heard these days among professionals and other Excel users. ‘Advanced Excel’ is not a specific tool to learn in Excel. It is a term that is generally used to describe a combination of various advanced tools discussed above. In other words, if you master all the tools in areas 1 to 5 above, you can be termed as an advanced Excel user. Some people like to include the knowledge of Excel VBA in the definition of ‘advanced Excel user’ but I don’t think VBA is an essential skill to have (although it can change the way you think of and use Excel totally!) unless you want to make programs based on Excel.

**Recommended Courses**

#1 Microsoft Excel – Excel from Beginner to Advanced **(Bestselling) **by Kyle Pew

#2 Advanced Excel – Beginner to Ninja level (includes Charts) by Rishabh Pugalia

#3 Microsoft Excel 2016 Master Class: Beginner to Advanced by Bruce Myron and Joe Parys

#4 Microsoft Excel 2016 Advanced Training Master Class by Simon Sez IT

**Recommended Books**

#1 Excel 2016 Bible **(Bestselling)** by John Walkenbach

#2 Learn Excel 2016 Expert Skills with The Smart Method by Mike Smart

In addition to the above, you may use the following tools to complement your learning. Excel keeps improving and you can use the below-mentioned resources to keep yourself abreast with the latest developments and new tricks.

Below is the list of my favorite websites on Excel. You can find a lot of value from the articles given on these websites. There are thousands of websites and blogs which have Excel as their primary topic. Below are arguably the best ones among those.

#1 Excel-Jet

#2 Chandoo

#3 Contextures

#4 Excel Easy

There are a lot of helpful YouTube channels that may help you learn some real cool Excel techniques. Some of those are run my Excel MVPs (Microsoft Certified Excel Super-users). I’d encourage you to subscribe to these channels and learn from their tutorials. Some of my best learning has come from these channels and I urge you to subscribe to these immediately.

#1 ExcellsFun

#2 Excel Campus

#5 Computergaga

Below, I’ve listed a few forums and groups where you can post any queries you might have. These are communities having thousand of Excel users as their members. So, you can expect to get your queries resolved right away.

#1 Quora.com

#2 Advanced Excel Tips and Tricks

#3 MICROSOFT EXCEL AND ACCESS PROFESSIONALS

#4 Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

No matter what we want information about, we cannot ignore Google. And Excel is no exception. So whenever you want to know about anything specific in Excel, just type in your search query and you’d normally get the answer you are looking for.

So that’s about it for today! I hope this article would help you decide your priorities in respect of the areas you’s like to learn in Excel, and the resources would help you learn Excel to its core.

Below are the links for a couple of helpful articles you may be interested in reading.

Become An Excel Ninja – Course Review

10 Best Online Courses to Make You an Excel Wizard

Goodbye for now. Have a great day.

Happy Learning!

The post How to Learn Excel and Be a Spreadsheet Champion appeared first on Mad About Excel.

]]>The post Making Excel Worksheets Look Professional – A Case Study appeared first on Mad About Excel.

]]>Don’t you get annoyed when you see an ill-formatted excel worksheet? Inconsistent fonts and font sizes, alignment, and other non-uniform formatting is not something anyone likes to see in excel sheets. Who doesn’t like to see worksheets which are properly and consistently formatted? Professional-looking worksheets are not only easy on the eyes but also helps easy understanding and save time figuring out the significance of different column heads and other data.

Having worked in a multi-national consultancy firm, I know how important it is to present the Excel files you work on in a properly formatted manner to your seniors and clients. It makes things easier for them and is highly appreciated. On the other hand, a file having inconsistent font sizes, color highlights, and alignment and really prove to be a big turn off for anyone viewing the file.

Today, I’d be discussing the principal areas which one should be keeping in mind when formatting a spreadsheet and finalizing it for presentation. I’d be doing this with the help of a case study.

I’d like you to download the example file which I’d be using for explanations. If you download the file, you’ll see that it has two sheets. The first sheet is the raw data that we need to format to make it look more presentable. Basically, the result that I’d be aiming for is given in Sheet 2. You can see the difference I’m sure! The first sheet looks totally unprofessional and it looks that the same has been worked upon by an amateur. The second sheet, on the other hand, looks much more presentable. I’ve taken a very small and simple example data on purpose, as it will help you understand my point easily.

Please note that the formatting options that I suggest below are based on my personal preferences. I suggest you experiment with different options (fonts, font sizes, colors etc.) and come up with something that suits you and your organization.

**Step #1:** I like to have the **first row and first column of an excel file as blank**. Insert a blank row above the first row and a blank column before of the first column. To insert a blank row, select the first row and press Ctrl Shift +. You can also right-click on any cell in the first row > Select ‘Insert…’ > Select ‘Entire Row’ and click OK. You can insert blank column by following a similar method.

Next, you’ll need to adjust the height and width of the first row and first column you just inserted to 5 and 0.5 respectively. You can do this by selecting the row (Row 1 in our case) > right-clicking on any cell > selecting ‘Row Height…’ > setting the row height to 5 and clicking OK. You can set the column width by following a similar procedure.

**Step #2:** Next, you’ll need to adjust the **alignment **depending on the type of data contained in the respective columns. You can see that the alignment in the sheet is horribly inconsistent. Let’s fix this. As per the general standards, numbers and dates should be aligned to the right, and text should be aligned to the left. So, make columns B, E and F aligned to the right and the other columns aligned to the left. To do this you just need to select the respective columns and press keyboard shortcuts Alt H A L and Alt H A R to align to the left and to the right respectively.

**Step #3:** You must have noted by now that you cannot see all the text contained in the Product column. Also, the column width of the Category column is unnecessarily high. You’ll need to **adjust the column widths** such that all the text is clearly visible, but make sure the width is not too high either. You can easily do this by selecting the whole data and press the keyboard shortcut Alt O C A.

**Step #4:** Next, select the table and make its **background color as White**. This makes the grid-lines invisible in the table and makes the data look better. You can do this by pressing keyboard shortcut Alt H H and then select the white color.

**Step #5:** Now, select the table and change its **font** to Calibri. Calibri is the font that I prefer when aiming for a professional-looking spreadsheet. I suggest you try a few fonts and chose the one that you think looks suitable. Avoid flashy fonts (like Comic Sans) though. The option to select the Font is available on the Home tab of the main menu.

**Step #6: **Next, select the table and change the **font size** to 11. Again, this is a personal preference. 11 is a font size that I find most appropriate. Being not too small nor too big, I find it easy on the eyes. The option to select the Font Size is available in the Home tab of the main menu.

**Step #7: **Now, let’s format the column heads of the data table. First, select the column headings and press Ctrl B to make them **bold**. This makes them stand out from the rest of the data.

**Step #8:** Also, change the **font color** of the column heads to dark blue. Dark blue is a standard color used by many organizations in their worksheets to make the headings stand out, and I totally agree with them. The option to select the Font Color is available in the Home tab of the main menu.

**Step #9: **Next, select the heads and apply a dark blue (same as their font color) **border** under them. For this, you can press keyboard shortcut Ctrl H B to open the Borders panel and then select the Bottom Border option. To change the color of the border, you should select the column heads > press keyboard shortcut Ctrl 1 to open the Format dialogue box > go to Border tab > select the Border > chose the appropriate color > press OK.

**Step #10:** You must have noticed by now that the **number formatting** is not consistent in the Amount column. Some amounts have a decimal point and 2 digits after the decimal point. Ideally, amounts should have 2 digits after the decimal point. Even if the amount is a whole number, it should have two zeros to make them consistent and presentable. Also, the amounts should have a thousand separator enabled i.e. the amounts should have comas after every set of 3 digits staring from the right.

To apply such formatting, select the numbers (amounts) > press Ctrl 1 to open the Format dialogue box > in the Number tab, go to Number option > set 2 in the Decimal Places > tick Thousand Separator option > click OK.

**Step #11:** Lastly, you’ll need to make the inconsistent formatting of the Date column consistent. Select the cells having dates > open the Format dialogue box > in the Number tab, go to Date option > select the first option in Type > Click OK.

Now, your worksheet should look like the one given in Sheet 2.

After going through the above exercise, you must have got an idea about how important the look of your worksheets is, why you need to make them look professional and how you can do that.

What other formatting choices you prefer to make when you work in your Excel worksheets? I’d love to know about them. Please leave a comment and me about them.

See in soon with another set of Excel tips. Till then…Happy Excelling!

**(Click here for more Excel Tips)**

The post Making Excel Worksheets Look Professional – A Case Study appeared first on Mad About Excel.

]]>The post Freeze Panes in Excel – The Complete Guide appeared first on Mad About Excel.

]]>This article explains how you can freeze panes i.e. lock row(s) and column(s) to fix them while scrolling up/down or right/left.

How many times do you come across a situation where you want to fix the headings/titles of a column or row so that you can view the same even while you have scrolled down or right?

The answer to this common problem is the Freeze Panes feature in MS Excel. In this article, I’ll explain how you can apply this basic but very useful feature. Let’s take an example for better understanding.

Suppose you are working on the following excel worksheet.

This file has a total of 4627 rows and 26 columns. If you scroll down normally, its obvious that you won’t be able to see the column heads. Again, if you scroll right you’ll not be able to see the Item Code (given in Column B). In such situations, the Freeze Panes options comes in really handy. It allows you to view the a specified number of rows and/or columns while scrolling down/right.

The steps are given below:

**Freeze Top Row:**To freeze just the top row, View -> Freeze Panes -> Freeze Top Row.**Freeze First Column:**To freeze just the first column, View -> Freeze Panes -> Freeze First Column.**Freeze Multiple Rows:**To freeze multiple rows (starting with the first row), select the row below the last row you want to freeze -> View -> Freeze Panes -> Freeze Panes.**Freeze Multiple Columns:**To freeze multiple columns (starting with the first column), select the column to the right of the last column you want to freeze -> View -> Freeze Panes -> Freeze Panes.**Freeze Rows and Columns at the Same Time:**To freeze both rows and columns at the same time, make sure the cursor is below the row(s) and to the right of the column(s) you want to freeze -> View -> Freeze Panes -> Freeze Panes.

So, in our example, suppose you want to lock the first row, so that you can view the column heads. Go to View -> Click Freeze Panes -> Click Freeze Top Row. Done. You can now view the column heads even if you scroll down.

Now, suppose you want to lock the first row and also the first 2 columns, so that you can always view the column heads and also the item codes. To do that, you’ll need to select cell C2, which is the cell just below the row you want to fix and just after the column you want to fix. Then go to View -> Click Freeze Panes -> Click Freeze Panes. You can see the effect in the below screenshot. Row 1 and Columns A and B would be fixed and you’ll always see them no matter how much you scroll down or right.

**Important:** Please remember that you can only freeze rows and columns from the top and left respectively. In other words, Excel doesn’t allow you to fix rows or columns in the middle of a worksheet.

To remove the freezing of rows or columns, you just need to go to View -> Click Freeze Panes -> Click Unfreeze Panes.

I’d encourage you to try the Freeze Panes option explained above and see the effect for yourself. It’s a very useful feature of Excel and is a must-use option when working on big excel databases.

**I hope you liked the article and if you did, please help me spread the word about MadAboutExcel by sharing it with your friends on Facebook, Twitter or LinkedIn using the sharing buttons above and below this article.**

Also, below are a few articles, I think you’d like to read:

Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques

How To Easily Select All Cells Containing Formulas

Thanks for reading. See you soon. Happy Excelling!

The post Freeze Panes in Excel – The Complete Guide appeared first on Mad About Excel.

]]>The post How to Convert Pivot Table into a Frequency Distribution in Excel appeared first on Mad About Excel.

]]>Pivot Table is a very useful tool that our favorite MS Excel provides us with (I know you know that! Lolz!). Pivot Table helps us make sense of a large data. This post will explain one such way to making sense of data using Pivot Tables, and that is by converting a Pivot Table into a Frequency Distribution.

This post is not a post where I’d explain Pivot Tables and its uses in detail. I’d rather focus on one important way that you can use Pivot Tables and that is by making a Frequency Distribution.

**Click here to download the example file on which I’d be basing my discussions.**

Now, in the example file, you’ll see six columns of data. The first column has the serial numbers, the second column has names of certain products which a company deals in, the third column contains the categories to which the products belong, the fourth column has the amounts of sales made, the fifth column has the date of sale and the sixth column has the carrier names through which the products were shipped.

This data, by itself, does not give us any useful information. For example, just by looking at this data, one cannot tell the amount of sale of products in the ‘Sports’ category in the year 2012, shipped via UPS. Pivot Table arranges the data in such a way that it allows the user to churn out useful information, or as I said earlier ‘make sense’ of the data.

Now, assuming that you’ve downloaded the example file, let’s start the process. The steps are given below.

**#1.** Make sure that a cell in the Pivot Table is selected. Click on ‘Insert’ in the main menu and click ‘Pivot Table’. ‘Create Pivot table’ dialogue box will open. Simply, click OK to insert a Pivot Table in a new tab.

**#2.** Drag the ‘Amount’ field into the Rows as well as into the values area.

**#3.** Right-click on any cell in the Sum of Amount column and select Value Field Settings option.

**#4.** Select ‘Count’ and click OK. This will change the *sum* of the amounts to the *count* of the amounts, i.e. the frequency of occurrence of each amount.

**#5.** Right-click on any cell in the Row Labels column and click ‘Group’.

**#6.** In the dialogue box that opens, you’ll need to fill 3 values. Now, after looking at the amounts in the original data, I know that the lowest amount is around $1 and the highest amount is around $2000. So in the ‘Starting at’ and ‘Ending at’ fields, you’ll need to put 1 and 2000 respectively, as that is the range we want a frequency distribution for. The ‘By’ field is where you need to put the size of one range in the distribution. So if you put, say 100 there, the data would be divided into 20 ranges of $100. Put 100 in the By field and click OK.

You have your frequency distribution ready in front of you now. Now you can easily tell the number of sales for amounts 1 to 100, 101 to 200 ad so on till 1901 to 2000. Easy, wasn’t it?

Now you can also insert a **Pivot Chart** to get a better comparative picture of the different ranges. To insert a Pivot Chart, just click on any cell in the pivot table and go to the Analyze tab and click Pivot Chart. You’ll see all sorts of options here. Select the type of chart you want to see, and click OK. You’ll have your Pivot Chart for the frequency distribution ready.

Hope you found the post useful.

**If you like what I do, please share the post with your friends on Facebook, Twitter or LinkedIn using the buttons right above and below this post.**

I’ll see you very soon with another useful article.

Till then, take good care and…Happy Excelling!

The post How to Convert Pivot Table into a Frequency Distribution in Excel appeared first on Mad About Excel.

]]>The post Rounding Numbers in Excel – The Complete Guide appeared first on Mad About Excel.

]]>So, in this article I provide you with 10 excel functions that you can use in different situations to make your task of rounding of numbers in Excel seem like a cake-walk. Do have a look and practice these to understand the differences between these and the situations in which each of these have to be used.

ROUND function rounds a number to a specified number of digits.

The syntax of the ROUND function is =ROUND(number, number of digits). The ‘number of digits’ is the number of digits after decimal that you want to have in the result. Note that if number of digits is less than 0, the number is rounded to the left of the decimal point. Also, if number of digits is zero the number is rounded to the nearest integer.

Looking at the below mentioned examples of the usage of this function along with the results will help you understand this function better.

=ROUND(15.4, 1) | 15.4 |

=ROUND(17.218, 0) | 17 |

=ROUND(-24.358, 2) | -24.36 |

=ROUND(36.5, -1) | 40 |

=ROUND(843.3,-3) | 1000 |

=ROUND(7.64,-1) | 10 |

=ROUND(-58.91,-2) | -100 |

If you want to always round up (i.e. away from zero), use the ROUNDUP function and if you want to always round down (i.e. towards zero), use the ROUNDOWN function.

The syntax of ROUNDUP and ROUNDDOWN functions is same as that of ROUND function. These functions are similar to ROUND, except that they always round up and round down a number respectively.

=ROUNDUP(28.3,0) | 29 | =ROUNDDOWN(28.7,0) | 28 |

=ROUNDUP(6.26789, 3) | 6.268 | =ROUNDDOWN(6.76789, 3) | 6.767 |

=ROUNDUP(-8.147832, 1) | -8.2 | =ROUNDDOWN(-8.67832, 1) | -8.6 |

=ROUNDUP(67546.48943, -2) | 67600 | =ROUNDDOWN(67546.68943, -2) | 67500 |

TRUNC function truncates (as the name suggests) a number to an integer by removing its fractional part.

The syntax of TRUNC function is =TRUNC(number, number of digits), where ‘number of digits’ is optional. The ‘number of digits’ is the number of digits after decimal that you want to have in the result. Note that if number of digits is less than 0, the number is rounded to the left of the decimal point (similar to ROUND, ROUNDUP and ROUNDDOWN).

Looking at the below-mentioned examples of the usage of this function along with the results will help you understand this function better.

=TRUNC(10.8) | 10 |

=TRUNC(10.8,0) | 10 |

=TRUNC(-10.8) | -10 |

=TRUNC(0.38) | 0 |

=TRUNC(364.17,1) | 364.1 |

=TRUNC(364.17,-1) | 360 |

=TRUNC(364.643) | 364 |

=TRUNC(-364.76,1) | -365 |

INT function rounds a number DOWN to the nearest integer.

The syntax of INT function is =INT(number or reference to the number)

So, the result for =INT(12.7) will be 12 and that for =INT(-12.7) will be -13 (Rounding a negative number down will round it away from zero).

ODD function rounds a number UP to the nearest odd integer.

The syntax of ODD function is =ODD(number)

So, the result for =ODD(11.7) will be 13 and that for =ODD(-11.7) will be -13 (Rounding a negative number down will round it away from zero).

EVEN function functions in similar way. The only difference is that it rounds a number up to the nearest *even* integer.

MROUND function returns a number rounded to the nearest multiple of the specified number.

The syntax of the MROUND function is =MROUND(number, multiple)

So, =MROUND(21,4) will result in 20 (being the nearest multiple of 4 to 21). Similarly, =MROUND(-21,-4) will result in -20.

Please remember that if there are two possible results, then Excel will round the number *up *(and not down)*. *So, =MROUND(21,2) will result in 22 (and not 20).

If number and multiple have different signs, the result will be #NUM!

FLOOR function rounds a number towards zero to the nearest multiple of ‘significance’.

The syntax of FLOOR function is =FLOOR(number, significance), where number is the number you want to round and significance is the multiple to which you want to round.

So, =FLOOR(15.8,2) will round the number 15.8 towards zero to a multiple of 2 i.e. the result will be 14. Similarly, the result of =FLOOR(3.76,0.1) will be 3.7.

=FLOOR(-5.4,-2) will result in -4 (towards zero).

If the sign of number is negative, a value is rounded down and adjusted away from zero. So, the result of =FLOOR(-16.87,3) will be -18.

If number and significance have different signs, the result will be #NUM!

CEILING function is just the opposite of FLOOR function with the difference being that CEILING function rounds the number *away* from zero, to the nearest multiple of significance.

So these were 10 different excel functions to help you round numbers easily. Practice these well and understand the difference between them. Many of these can seem to be performing similar tasks. But trust me when I say that each of the above discussed functions have their own distinct usage. It is difficult for me to jot down each and every possible situation and the best function to use in that situation. So I have left that task for you to do. Reading this article and the practicing these functions will definitely make your concept clearer. And if you still have doubts, I am there to help you!

If you think this article can help your friends and colleagues, please share the articles on Facebook / Twitter / LinkedIn using the buttons above and below the article.

There are couple of more articles that I think you might find interesting. Do have a look.

14 Excel Tips – Make Yourself Super-Efficient in Excel

Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques

I’ll see you soon with another such article.

Till then…Happy Excelling!!

The post Rounding Numbers in Excel – The Complete Guide appeared first on Mad About Excel.

]]>The post 14 Excel Tips – Make Yourself Super-Efficient in Excel appeared first on Mad About Excel.

]]>Excel is a very important skill to have these days. There is a plethora of excel tips available on the internet. YouTube is full of Excel tips videos and channels. And there seems to be no end to how much we can learn in this spreadsheet software. There is virtually no limit to what we can do with and how much we can explore MS Excel. But still we use hardly a fraction of the functionalities Excel has to offer.

Below I give you a total of 14 important Excel tips that one is expected to know. This list of Excel tips is in now way exhaustive, but I am sure knowing these is a good starting point for anybody aspiring to gain deep knowledge in Excel.

So let’s dive in!

To become truly efficient in Excel, you *have *to develop the habit of using keyboard instead of mouse. Using mouse slows down the speed at which you work in Excel and can severely impact your efficiency. There are many keyboard shortcuts which you can use to save yourself a ton of time.

You can join my mailing list (see the box in the sidebar or below this post) to get my course – “Ultimate Excel Shortcuts Course – Become 10X Faster on Excel” – for FREE. In this course, I’ll show you how to use over 200 shortcuts to speed you up in Excel.

As I always say, Paste Special is a special feature in Excel. It has so many options and makes pasting data so much easier and this makes it is a must-know feature. You can even perform calculations and transpose data using Paste Special.

To open the Paste Special dialogue box, copy the data/cell and press the keyboard shortcut Ctrl Alt V after placing the cursor on the cell where you want to paste the data. Open the Paste Special dialogue box and explore all the options. I am sure you’ll find them handy while working in Excel.

Comments are a very useful, but highly underused feature of Excel. I use comments all the time to keep notes related to data in a cell or table so that I can refer to it later. I also use comments to note the details about a formula, so that I don’t risk forgetting all about it when I look at the worksheet again after several days.

You can insert comments by right-clicking on a cell and Select Insert Comments option.

I hate scrolling up or left when working on a data having a large number or rows and columns. It is annoying to scroll up or left to see the row or column heads. So I use Freeze Panes option to lock the rows/columns so that the first row/column is always visible no matter how much I scroll down or right. The Freeze Panes option is available in the View menu.

‘Go To Special’ is again a very powerful feature in Excel. You can find and select various items in the entire worksheet with a single click using this feature. The items may be comments, formulas, blank cells, objects, precedents, dependents, conditional formatting, data validation etc.

I use this feature mostly when I need to find the cells with formulas, blank cells and objects.

To access the Go To Special feature you just need to press Ctrl G to open the Go To dialogue box and then press Special button (or press keyboard shortcut Alt S). This will open the Go To Special dialogue box.

In my previous post, I discussed the most important techniques to clean data in Excel. So, I won’t get into a lot of detail in this post. Click here to read my post – “Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques.” I’d also encourage you to watch the below video by Sumit Bansal in which he discusses some really cool ways of data cleaning in Excel.

If you are working on documents that you’d be sharing with others, it is important to protect your work, especially the cells in which you have inserted formulas. Protecting formula cells is very important in the sense that any change in them can ruin all your hard work. That’s why you should consider protecting the cells that contain formulas. The steps are given below:

Step 1: Select the entire sheet by pressing Ctrl A –> Press Ctrl 1 to open the Format Cells dialogue box –> Go to Protection tab –> Untick *‘*Locked’ and ‘Hidden’ check-boxes –> Click OK

Step 2: Select a cell containing Formula –> Right-click on it and select Format cells –> Go to Protection tab and tick ‘Locked’ and ‘Hidden’ check-boxes → Click OK

Step 3: Go to Review Tab in the Main Menu –> Click on Protect Sheet –> Provide a password (optional) –> Click OK

Format Painter is a very handy tool to quickly copy the formatting from one cell to other cell or cells. Clicking once on the Format Painter icon allows you to apply formatting to a single cell. If you want to apply the formatting to multiple cells, you’ll need to double-click on the icon.

Excel provides many sorting options. But most of us sort data in a column in either ascending or descending order and don’t go beyond that. You might not know that you can sort data in excel based on values (ascending or descending), cell color, font color and cell icon.

Just click on filter drop-down and go to Sort by Color. There you’ll see the Custom Sort option. Click on it to open the Sort dialogue box where you can explore these sorting options.

Color coding different types of cells helps in organizing and analysis of data. One of the most common types of cells where color coding is applied to differentiate them from other cells, are the formula cells. By doing this, you know which values have been hard coded and which ones are the result of a formula.

To do this, press Ctrl G to open the Go To dialogue box. Click on the Special button. Select Formulas and click OK. Then, select the color highlight from the Fill Color button in the Home menu.

The ability to assign a name to a range is another cool feature offered by Excel. Assigning a unique and descriptive name to a cell range give’s the range an identity and helps you in recognizing it and referring to it in formulas. So instead of referring to a range as, say G1:J26 you can simply use the name of the range in the formula. This also saves time if you need to refer to a particular range several times.

You can access this feature by by going to Formulas menu and clicking on Name Manager.

Sometimes, when working with big formulas and/or large data, it may become difficult to trace the cells on which the formula is dependent (precedents) or the cells which depend on the formula (dependents). You may need this information when you get lost in several formulas and are not sure which cells will be affected if you change some data.

It is very easy to know precedents and dependents of a formula. Select the cell containing the formula –> Go to Formulas menu –> Select Trace Precedent or Trace Dependent (as applicable) in the Formula Auditing group. Arrows will appear showing the cells dependent on the formula or the ones on which the formula depends (as applicable).

Whenever I am writing a formula, I always make it point not to hard-code them.

Let me explain what I mean with the help of an example. Let’s say I want to calculate interest on various amounts @ 8% which is the current rate of interest. Normally, one can write the formula as =A2*8%, A3*8% and so on. But what if the interest rate changes to 8.5%? You’ll need to change the rate in all these formulas individually. So you should always write the interest rate in another cell and reference to it in the formulas. By following this method, you’ll just need to change the value in the cell having the interest rate and all the changes will take effect automatically.

Errors are common when it comes to using formulas in Excel. I find it really annoying to see errors like #N/A, #DIV/0!, #VALUE in my worksheets. To manage such errors, I use the IFERROR function. This function returns the value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. To learn more about the IFERROR function, click here.

I am completely aware that I have not included a lot of similar tips above. But I never said that this is the end. I’ll keep posting such lists in the future and continue giving you important tips to help you excel in Excel.

So what have I left out? What would you like me to include in the next list of Excel tips. I’d love to hear from you. So, please tell me by commenting below.

Thanks for reading. See you soon. Happy Excelling!

The post 14 Excel Tips – Make Yourself Super-Efficient in Excel appeared first on Mad About Excel.

]]>The post Best Books and Courses to Learn Excel VBA appeared first on Mad About Excel.

]]>A few months ago, I conducted a survey with my Facebook fans. One of the few questions I asked in the survey was “What topics are you most interested in learning about in Excel?”. The question had 10 choices and the responses that I received clearly indicated one fact – the area that people are most interested in learning in Excel is VBA.

Now frankly speaking. VBA is not the easiest subject to learn. And it takes a very systematic approach to master. In short, you need a few good resources which can help you to learn Excel VBA in a step-by-step manner, such that you find it interesting and easy to learn.

I also used (and still use) a mix of books, courses, websites, forums etc. to learn VBA. Below, I give you a list of the resources which, either I have used myself, or have been used by somebody I know. So, I know for a fact that these are possibly the best resources to master Excel VBA available today.

You don’t need to use all of these resources. Just buy any one of the books and enroll in any one of the courses mentioned below. **All the books and courses are equally good, but personally speaking, I’d recommend the first two courses and books in the list**. That should be enough for you to get started. Then, when you are done with them, you can use one more book and course to fill in any gaps which still exist. Other than books and courses, you can use the groups and forums given below to clarify any doubts and learn anything not mentioned in the books or covered in the courses. In other words, use these communities to complement your learning.

So let’s see what these resources are:

**1. Excel 2016 Power Programming with VBA **by Michael Alexander and Richard Kusleika

**2. Excel VBA Programming For Dummies **by John Walkenbach

**3. Excel 2016 VBA and Macros **by Bill Jelen and Tracy Syrstad

**4. Excel VBA in easy steps **by Mike McGrath

**5. Programming Excel with VBA: A Practical Real-World Guide **by Flavio Morgado

**6. Mastering VBA for Microsoft Office 2016 **by Richard Mansfield

**The Ultimate Excel Programmer Course** by Daniel Strong

**Master Microsoft Excel Macros and Excel VBA **by Kyle Pew

**Ultimate Excel VBA **by Mark Talbert

**Excel VBA Tutorials – Learn Excel Programming with practical approach** by Rishabh Pugalia

Obviously, the above list is not exhaustive. There are plenty of other great books, courses, groups, forums and websites that can help you learn and master Excel VBA. The above are just my recommendations. So, if you have time you should definitely go out and research about what book or course will suit you the best. Otherwise, you can safely rely on the above list. I have put a lot of time and effort into researching these resources for my own learning, as well as for the benefit of my readers.

If you know of any other book or course that is not mentioned above, I’d definitely like to know about it and share the same with others. So, please write about them in the comments below so that the other readers can benefit from those resources as well.

One last thing. The above links to the products are affiliate links. In other words, if you purchase these products (books and courses) from the above links, I’ll earn a commission. But I promise that I have not included these resources to make a quick buck at the cost of my readers. The affiliate commissions are just a way for me to be able to run MadAboutExcel and serve you with good quality content.

Thanks for reading. I am sure the above resources will help you in your journey to master Excel VBA.

See you soon. Have a EXCELlent day!

Chow!

The post Best Books and Courses to Learn Excel VBA appeared first on Mad About Excel.

]]>The post Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques appeared first on Mad About Excel.

]]>Data cleaning is one of the necessary excel skills that you are expected to possess today. A data set might be having certain inconsistencies, and to make it more presentable and to help proper understanding of the data it is necessary to make the data look better and error-free.

Below, I mention 11 best techniques to help you ‘clean’ data.

Get Set Gooooo…

You’d agree that spelling errors are annoying. You wouldn’t want anybody to question your dedication just because of some spelling mistakes or typos in your excel workbook. So, you must do a Spell Check before finalizing any excel document. You simply need to press keyboard shortcut F7 to open the Spell Check window and run the spell check.

You should make sure that the text case in the data is consistent. The data should not have some text in lower case, some in upper case and the remaining in proper case. To make the text case consistent throughout the data you can use the following functions, as required. Click on the function names below to learn how to use them.

UPPER – Converts text to Upper Case.

LOWER – Converts text to Lower Case.

PROPER – Converts text to Proper Case.

Extra spaces can make the data look clumsy. By extra spaces I mean more than one space between words and any spaces before of after the data string. Assuming you are working on a big amount of data, it can be next to impossible to spot and remove extra spaces manually. But you don’t need to do this manually.

You can simply use the TRIM function to remove such extra spaces. You can click here to learn more about using the TRIM function.

You may have applied several kinds of formatting to the different parts of an excel file while working and while finalizing your work you might not be needing those formatting and may want to clear the data of all the formatting applied. Or you may have inserted several comments or hyperlinks for your references, which you now want to delete. You can clear all the formatting / comments / hyperlinks together for the whole data by simply selecting the data and going to Home –> Clear –> Select the appropriate option (Clear All / Clear Contents / Clear Comments / Clear Hyperlinks).

In a data, the cells containing errors can cause a lot of trouble for you when you sit down to analyze the data. So it is best to fix them. You can do this by either highlighting or selecting the cells containing errors.

**Highlighting Errors**

- Select the data.
- Go to Home –> Conditional Formatting –> New Rule. ‘New Formatting Rule’ dialogue box will open.
- Select ‘Format Only Cells that Contain’ option.
- In the Rule Description, select Errors from the drop down.
- Click on ‘Format’ button.
- Select the formatting options and click OK. This highlights all the errors in the data.

**Selecting Errors**

- Select the data.
- Press F5 to open the Go To dialogue box.
- Click on ‘Special…’ button.
- Select Formulas and uncheck all the options other than ‘Errors’.
- Click OK. All the cells containing errors.

You can use the Find and Replace feature in Excel to replace certain ugly-looking text to their better-looking alternatives. For example, you can replace ‘&’ (ampersand) with ‘and’. You can replace error results viz. #N/A, #VALUE etc. with blanks or zeros as well (You first need to use ‘Paste Special’ to replace formulas with values.)

All you need to do is to select the appropriate cells and press keyboard shortcut Ctrl H. Then put the text you want to replace in the ‘Find What’ field, and the text you want to replace it with in the ‘Replace With’ field. Simple!

You can replace or substitute text in a column using formulas like REPLACE and SUBSTITUTE. You can use SUBSTITUTE when you want to replace specific text in a text string, and use REPLACE when you want to replace any text that occurs in a specific location in a text string. To know more about these functions, you can use the links given below:

Blank cells may cause many problems if you are converting the data into pivot tables or are making charts with the data. It is best to replace these blank cells with some appropriate data or text. It need a very simple exercise. Just follow the below steps:

- Select the data.
- Press F5 to open the Go To dialogue box.
- Click on ‘Special…’ button (or press Alt S). This will open the Go To Special dialogue box.
- Select ‘Blank’ option.
- Click OK. This will select all the blank cells in the selected data.
- Type the text (‘Not Available’, ‘0’, etc.) and press Ctrl Enter. This will replace all the blank cells with the text.

The data maybe such that you don’t want to have the same values more than once (or maybe you’d want to know which of the values appear more than once in the data).

**To find duplicate values:**

- Select the data.
- Go to Home –> Conditional Formatting –> Highlight Cells Rules –> Duplicate Values.
- Specify the formatting options and click OK. All the duplicate values in the data will be highlighted.

**To delete duplicate values:**

- Select the data.
- Go to Data –> Remove Duplicates.
- Select the Column(s) from which you want to remove duplicates and click OK. (If your data has headers, you need to check the checkbox ‘My data has headers’.) All the duplicate data will be removed.

Sometimes you may need to merge the contents of two or more columns together to make the data more meaningful. You can easily do this by using & (ampersand) or the CONCATENATE function (not recommended). To know more about how to merge the contents of columns, please click here.

Contrary to the above, you may need to split the contents of the cells in a column. For example, you might need to split a column containing dates such that you have dates, months and years in separate columns. You can do this using the ‘Text-to-Columns’ feature in Excel. To know more about how you can use the Text-to-Columns feature, please click here.

I hope you found the above data cleaning techniques useful. If you did I’d request you to share it with your friends on Facebook, Twitter, LinkedIn etc. using the buttons above and below the post so that this post can benefit them as well.

Thanks for reading. See you soon. Happy Excelling!

The post Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques appeared first on Mad About Excel.

]]>