You will often need to scroll down or to the right to view a particular record or field in a large dataset. But when you scroll too far, you will lose track of the row and column headings! When working with large datasets, you can use the Freeze Panes feature to ensure the top rows or first few columns will remain visible no matter where you scroll in your sheet....

It’s so common that almost everyone knows the keyboard shortcuts to copy Ctrl + C and to paste Ctrl + V. When using this in Excel, it will copy everything including values, formulas, formatting, comments/notes, and data validation. This can be frustrating as sometimes you’ll only want the values to copy and not any of the other stuff in the cells. In this post, you’ll learn 8 ways to copy and paste only the values from your Excel data....

In this example we’ll assign a simple macro that auto adjusts the column width to the data in the sheet. See my post about creating a macro to make your own. Step 1: Open the QAT options window. Add a macro to the QAT. Step 3: Assign your macro. Select the Macro you want to add to the QAT. Step 3: Run your macro from the QAT. Now you will be able to run your macro easily from the QAT....

Select all the sheets that you want to perform the action on. Now perform your action. In this example I insert a column into the table, add a column heading label of Region and add the region for each sales person. Now when you look at each sheet in the workbook all the tables have gotten this updated column. Wow like magic, what a time saver. Tip: If your workbook has a lot of sheets and you want a quicker way of selecting your sheets use this trick....

The default gridlines in Excel are a very light gray and can sometimes be hard to see. Excel allows users to adjust the color and thickness of the gridlines to suit their needs. Making the gridlines darker can help make them more visible, making it easier to differentiate between cells. Whatever your preference there are options to customize your gridlines. Get your copy of the example workbook used in this post and follow along!...

Perhaps you see so many zeros that it is distracting you from some important analyses. It may even be that your zero values are not accurately reflecting the state of the data where they appear. Excel may display zeros in places where you consider the data to be non-applicable. In such cases, showing zero as a dash (-) can be an effective way to enhance readability and improve the interpretation of your data....

Syntax DAY(Date) Date (required) – This is the date value that you want to return the day from. Example In this example we use the function on several dates in different formats. Notice the text values we try result in a #VALUE! error while numerical values outside of the range 1 to 2,958,465 result in a #NUM! error.

Syntax NOW() There are no arguments for this function. Example In this example we return today’s date and time.

Syntax TRUE() There are no arguments for this function. Example In this example, we return the TRUE value.

An Excel cell can contain many different data types such as text, numbers, dates, Booleans, and error values. Until recently, images have not been a data type that you could insert into a cell. Previously, you could only place the image above the cell and then change the image properties so it moves and resizes with the cells. There are new features available in Excel that allow you to add images directly inside a cell....

For some reason, your VLOOKUP‘s are returning an #N/A error and you have no idea why. Everything looks good and the formula should be working. But for some unknown reason, a handful of results are errors. The first time this happened to me, I spent ages trying to find the problem only to discover some of the lookup data had extra space characters attached at the end! This happens a lot....

Adding a new column based on the joining of two columns is a basic task in data analysis. In data entry forms, most of the time the developer will keep two fields for first and last name instead of the full name. It helps to avoid confusion, is easier to find, and will be very useful for further data analysis. Suppose you are creating a report where you need to display a customer’s name....

Syntax DATE(Year, Month, Day) Year (required) – This is the year number from 1900 to 9999.Month (required) – This is the month number from 1 to 12.Day (required) – This is the day number from 1 to 31. Example In this example we create a few dates by reference to a year, month and day. Notice that if we use a month greater than 12 or a day greater than the actual number of days in the month, then the result rolls over into the next month or year....

Let’s create pivot tables to answer these questions about our sales data. What were the total sales for each sales representative?What were the top 3 States for sales?What were the total sales for each region by quarter?How many of orders were there for each product? The great thing with pivot tables is it’s easy to answer questions like these about your data with just a few drag and drop actions....

Step 1: Activate the document location tool Activate the document location quick access tool. Now you’ll be able to quickly and easily copy and paste the workbooks location and name anywhere you need to from the quick access document location tool. Tip: You can also access the customization menu easily by right clicking anywhere on the quick access toolbar and selecting Customize Quick Access Toolbar.

Step 1: Enable the Developer tab in the Ribbon. By default the Developer tab is hidden and you will need to enable it to use in the ribbon. Step 2: Open the visual basic editor. Now you should see the Developer tab in your Excel ribbon and you can open the visual basic editor (VBE) from the ribbon. Alternatively, you can open the VBE with the Alt + F11 shortcut....

QR codes are two-dimensional barcodes that can be read by scanners and smartphones. QR codes are a popular way to link physical objects to digital information. When a QR code is scanned, it will link the user to a website, text, audio, or video file. This post will show you how you can make QR codes in Excel. Download your copy of the example workbook used in this post and follow along....

It’s no doubt the reason why many people need to calculate the square root of numbers in Excel. With such a common task, there are many ways to get it done in Excel. In this post, I’ll show you 5 ways you can use to calculate the square root of a number. What is a Square Root of a Number? The square root of a number is another number that produces the original number when multiplied by itself....

Excel lets you customize your workbooks in a variety of ways. One popular way to visually organize your worksheets is to change the color of the sheet tabs. Using different colors for sheets that contain inputs, data, calculations, reports, or visuals can be a helpful way to arrange your workbooks. You might also have a sheet for each month of the year and distinguish each quarter by color. Excel makes it easy to change tab colors, and doing so can help users keep track of important information....

You can find Excel’s clipboard by going to the Home tab and clicking the small expand icon in the lower right hand corner of the Clipboard section. There is also a handy keyboard shortcut (Ctrl + press C twice fast) to open the clipboard but it’s disabled by default. To open the clipboard and enable the clipboard shortcut. Now you will quickly and easily be able to access the clipboard by using Ctrl + press C twice fast....