Webmasters Gallery

A pivotal role in business

“Proficient in Excel” is a common skill reported on resumes. And while many feel confident in their ability to navigate Excel, there’s a lot beyond the rows, columns, and simple mathematics that beginners are accustomed to using.

Most businesses use Excel for a range of projects, and most require a knowledge level that exceeds the basics.

Excel: An indispensable tool

A strong knowledge of Excel can give you an advantage in any industry, as it’s a powerful tool for a wide scope of both simple and complex tasks — analyzing stocks, budgeting, managing projects, and organizing client information.

Finance

Financial analysts used to spend weeks solving complicated formulas, either by hand or with slow computer programs. Today, these analysts can do the same work in minutes — thanks to Excel.

Marketing

Spreadsheets are a powerful tool when it comes to tracking sales targets and analytics. Carefully organizing your data with Excel can help you plan future marketing strategies and allow you to reflect on your past successes and on areas that need improvement. With more advanced tools, such as pivot tables — which we’ll explain in depth later — you can easily summarize customer and sales data by category.

You can also use Excel to organize social media posts or store data for mailing lists and other market research.

Human resources

Excel can help you manage payroll and employee information, giving you the ability to visualize trends, track expenses, and see an organized collection of all employees and their compensation levels.

HR professionals can use Excel to see where costs are going and to plan for — and control — future expenses.

Overview of Excel’s uses

While there are obvious uses for Excel in particular industries, as mentioned above, this software provides limitless tools for a wide variety of businesses:

• Events. Track guest lists, costs, and RSVPs as well as organize vendors.
• Website content planning. Create an editorial calendar for a website. The spreadsheet framework makes managing dates and topics easy.
• Complex calculations. Solve thorny math problems with Excel’s various formulas.
• Customer reference. Track customer information to inform your promotional calls or emails.
• Budgeting. Create budgets for small projects and track how closely you stick to those budgets.
• Modeling. Create revenue growth models.

Solid knowledge of this software means you’ll be well aware of all the formulas and shortcuts that can save you from long, tedious hours of data entry and manual mathematics. If you’re looking to tackle this sometimes intimidating program, the rest of this Microsoft Excel tutorial will provide you with key tips and tricks that will help you conquer the software.

Excel basics

Laying the groundwork

When you open a spreadsheet, the empty rows and columns stare back at you, waiting to be put to use. The options are endless, as Excel can tackle an exhaustive list of tasks. The key, however, is knowing how to use the program to its fullest potential.

Before dedicating hours of tireless work maneuvering complicated spreadsheets and manually copying and pasting data, it’s important to master the basics. In return, you’ll be better prepared for any Excel project that’s thrown your way.

The building blocks of Excel

When you get started with a new spreadsheet, you’ll probably need to make some tweaks and adjustments to the standard layout of a new Excel sheet.

Inserting rows/columns

As you input data, you’ll probably need to add more rows and columns. While you could do this one by one, this is a perfect example of how understanding Excel and its shortcuts can save you significant time and frustration.

To add multiple rows or columns in a spreadsheet, highlight the number of preexisting rows or columns that you want to add. If you want to add four rows, highlight four rows on the spreadsheet, right-click, and then select insert. Now you’ve efficiently added an additional four blank rows into your spreadsheet.

How to move rows and columns

You’ll probably also need to move rows and columns in your spreadsheet. Your first instinct may be to copy the row and paste it where you want it, then delete the original. While that will certainly get you the results you want, there’s a quicker way to move rows and columns in Excel, explains Microsoft Office Support:

1. Select the row(s) or column(s) you want to move.
2. Move your cursor to the upper left edge of the selection. A “move” icon (a four directional arrow) will appear. If you want to move more than one row, column, or cell at the same time, and they are adjacent to each other, hold the Shift key down while you make your selection. If they aren’t adjacent, hold down the Control key.
3. Click on the edge of the selection and hold it to move it to the new location.

How to hide and unhide rows and columns

If you’re dealing with a data-heavy spreadsheet, it may be helpful to hide or unhide rows and columns so that you can see the information you need to understand and analyze your statistics. Just follow these steps:

1. Select the columns or rows you want to hide. You can select multiple columns or rows that are side by side if you hold down the Shift key. To select multiple cells that aren’t contiguous, hold down the Control key.
2. Once you’ve made your selection, go to the Home tab.
3. Under the Cells group, click Format > Hide and Unhide > Hide Rows (or Hide Columns).
4. The column or row will be removed, and you’ll see a thin double line that indicates where the hidden column or row was.

To unhide columns or rows

1. For a single row or column, right-click the thin double line that indicates a hidden row or column and click Unhide
2. To unhide all rows and columns, click the thin double line, and select all using the keyboard shortcut CTRL + A. In the Home tab, under the Cells group, choose Format > Hide and Unhide > Unhide Rows (or Unhide Columns).

In some cases, you may need to hide an individual cell in Excel, which is a little less straightforward than hiding rows or columns. Deleting a cell will clear it from your spreadsheet, so it will no longer be used in formulas, and you’ll lose the content. To remove the individual cell from view, but keep it in action

1. Select the cell you want to hide and right-click on it
2. Choose the Format Cells option from the dropdown menu
3. Under the Format Cells categories, select the bottom option, Custom
4. Enter ;;; (three semicolons) as the format, then press OK
5. The cell is now hidden, but the data is intact

How to combine and merge cells

There may come a time when you need to merge two or more cells into one large cell. This can help to display your data or better fit the amount of content you’re trying to share. If you’re trying to merge cells that contain data, the Excel “merge cells” feature will eliminate the data in all cells except for the cell in the upper left position of the selection.

The Merge & Center option in Excel is the fastest way to do this:

1. Select all of the cells you want to merge into one.
2. On the Home tab, in the Alignment group, click the Merge & Center option.
3. Once you click Merge & Center, the selected cells will be combined into one cell, and the text will be centered.

This can be useful if you have a long list of items and want to use the cells to the right to create a larger space to fit your text. If you click the Merge & Center option, a dropdown menu will appear. This menu gives you the options to Merge Across, Merge Cells, and Unmerge Cells. Make sure that all of your important data is in the top-left cell, or it will be erased.

To unmerge cells in Excel, select the cell and the arrow next to Merge & Center so you can select Unmerge Cells from the dropdown menu.

How to split cells

Sometimes you may want to separate a list of items in one cell. Instead of re-entering all of your information, there’s a shortcut that can make your life easier.

If a set of data is separated by a comma or other characters in a single cell, you can use Text to Columns to spread the data into their own individual cells:

1. Click on the Data tab, and then select Text to Columns.
2. A Wizard will pop up, displaying two file types: Delimited, where characters, such as commas or tabs, separate each field, and Fixed Width, where fields are aligned in columns that contain a fixed number of characters.
3. If your data is separated by comma or another character, select Delimited (for example, item one,item two,item three).
4. If your data is separated into columns, select Fixed Width (see below).

item one

item two

item three

5. If your delimiter (character) is a comma, select it on the next screen. Excel gives a variety of options, including semicolon and tab, to choose from. You can also enter Other in case you’ve used a different character.
6. The last step is to format each column’s data. For simple tasks, the General option will usually provide the desired outcome.
7. Excel will split the selected cell into multiple columns based on how many delimiters it found.

How to lock/protect cells

This is a good preventive measure to take if you anticipate sharing your spreadsheet with others. Locking cells will prevent accidental changes to your data. You can lock all the cells in a worksheet, or you can simply select specific cells to lock if you want to allow other people to alter parts of your spreadsheet.

When you protect a sheet or workbook, by default all the cells will be locked. This prevents them from being reformatted, deleted, or edited. Here’s how to lock a sheet:

1. In the Review tab, select Protect Sheet.
2. In this window, enter a password to unprotect the sheet (while this isn’t necessary, it may be useful to ensure that your sheet remains untouched). You can also get specific and determine which parts of your spreadsheet, if any, can be altered by other users.
3. Click OK to protect your spreadsheet.

If you’re looking to protect only certain cells in an Excel worksheet and want to leave the remainder open to editing, you can take a different route. For example, if you have a collaborative worksheet that has names and information that will always stay the same but quantities that change every month, you may only want to lock the unchanging cells.

You can choose which cell should be locked by using the formatting properties for the cell:

1. Select all the cells you don’t want to lock. (These cells can be edited after the sheet is protected.)
2. Right-click your selection, select Format Cells, and then click the Protection tab.
3. Uncheck Locked — which is selected by default — and then click OK.
4. Go to Review > Protect Sheet > OK. Any cells that weren’t unlocked under the Format Cells option will now be locked, and the unlocked cells will remain editable.

Formatting text

To apply either a strikethrough, subscript, or superscript to text within Excel, start with the Font Settings button, which appears in the lower right corner of the Font section. You can also use a keyboard shortcut: CTRL + 1.

This will open a dialog box, which provides more extensive font options. Here, you can adjust the font type, color, size, style, and effects. Under the effects is where you’ll find options for strikethrough, superscript, or subscript. Just check the box for the one you’d like to apply. Click OK, and the text in your selected cell will be modified.

Using simple math in Excel

Excel has useful formulas that can complete calculations. Here are four simple tips to remember when applying Excel formulas to perform basic math:

1. All Excel formulas start with an equal sign (=). This is how Excel knows that it’s dealing with a formula.
2. Cells are referenced in a formula according to their column-row identifier (A1, B2, etc.).
3. The symbols for addition, subtraction, multiplication, and division are +,-,*,/, respectively.
4. Excel will automatically capitalize letters in your formula.

Operation	Formula	Explanation
Addition	=A1+A2	The contents of cells A1 and A2 will be added together. (You can add more cells if you need to add more numbers.)
Subtraction	=A2-A1	The contents of cell A1 will be subtracted from the contents of cell A2.
Multiplication	=A1*A2	The selected cells will be multiplied.
Division	=A2/A1	Cell A2 will be divided by cell A1.

If you’re looking to use multiple operations in one formula, remember the order of operations (PEMDAS). Apply parentheses around the math equations that should be calculated first.

Using Goal Seek

Goal Seek is a tool that helps you see how one value in a formula is going to affect another value. Essentially, it figures out what you need to do in order to get the result you want in the cell that contains your formula.

This is really helpful because it does a lot of the thinking for you. All you need to do is specify these three things:

1. Formula cell
2. Target/desired value
3. The cell you need to change to achieve the target

Goal Seek is useful for financial modeling. Business owners often use it to determine the amount in sales they need to make to reach a target benchmark.

To use Goal Seek

1. Set up your data so you have a cell that will change based on the formula.
2. Locate the What-If Analysis tool under the Data tab. Select the Forecast option and then select Goal Seek.
3. A dialog box will appear that allows you to input the appropriate items. Once you type in the cells and/or values, click OK. The Set cell field is for the formula cell. The result you want to set up is in the To value field. The By changing cell field is for the cell that you want to make adjustments to.
4. After you click OK, a new dialog box will open. It will change the value if it finds a solution to the problem. If you want your information to be changed to the new value, just click OK. If you want to stick with the original information, click Cancel.

Discovering formulas

Understanding formulas

Large amounts of data and complex calculations can be overwhelming for anyone. However, Excel has many tools, like some key formulas we’ll cover here, to make spreadsheets less daunting. Using these formulas can increase productivity, decrease stress, and maximize accuracy — no more calculation mistakes!

There’s an exhaustive list of complicated formulas available to every Excel user, and while they’re all waiting at your fingertips, it’s important to know which formula to use and when to use it. Here are some formulas that can help you master Excel and streamline your tasks.

How to calculate standard deviation

Standard deviation is a statistic that measures how much variation is in a data set. It’s a useful tool when scientists are conducting a study and need to track the degree of variation in the data. A low standard of deviation illustrates that the variations are very close to the mean, and a high standard of deviation reveals that data points are spread further out from the mean.

Often, finding the standard deviation for an entire population can be unrealistic, hence the popularity of using a sample of the population when dealing with a large data set.

Excel allows you to calculate the standard deviation (which is functionally the same as calculating standard error) of your data without having to do any math yourself. You just need to understand which formulas to use, as there are six standard deviation formulas in Excel.

For sample standard deviation — the most common choice — the applicable formulas are STDEV.S, STDEVA, and STDEV.

Here are the three key formulas broken down:

• STDEV.S: This is used when data is numeric, which means that it will not use any text or logical values.
• STDEVA: Unlike STDEV.S, this formula is appropriate when you want to include text and logical values in the calculation along with the numeric values. Text and “FALSE” are considered 0, and the text “TRUE” equates to 1.
• STDEV: This formula is compatible with older versions of Excel (2007 and prior). It completes the same function as STDEV.S.

If you decide it’s reasonable to calculate the entire population, these formulas function the same way.

Using the STDEV.S formula in practice

The syntax of the function used in Excel looks like this: STDEV.S (number 1,[number 2], etc.).

Number 1 is the mandatory argument in the formula. Number 2 is the optional argument in the formula. The optional argument can be something as simple as a single data point, a range of data, a reference to an array, or any of the additional 200+ arguments

If you’re using the numbers in column A, the formula may look like this when applied: =STDEV.S(A2:A10). Excel will then provide the standard deviation of the formula.

How to calculate an average

You may need to find the average of your data, for example, determining the average price of your products or the average amount of time it takes to complete a task. You don’t need to waste time calculating this yourself when Excel has a formula that can do it much quicker.

The AVERAGE function looks for what is called the central tendency, says Microsoft Office Support. Basically, this means that it looks for the center point in the distribution of your data.

The average is an arithmetic mean, which is calculated by adding a group of numbers and then dividing by the total count of those numbers.

To calculate the average of numbers in a contiguous row or column

1. Click a cell below or to the right of the range of numbers you want to determine the average of
2. Go to the Home tab and click the arrow to the right of the auto sum symbol (?) in the Editing section. This will bring up a dropdown menu. Click Average and press Enter.

Using the AVERAGE function

If the range B2:B20 contains numbers, the formula AVERAGE=(B2:B20) will return the average of those numbers. Number 1 is required as the first number, cell reference, or range for which you want the average. Number 2 is optional and can involve additional numbers to include, as well as cell references or ranges you want the average of.

How to calculate linear regression

If you have a lot of data and need to use it to make predictions about the future of your business, there are endless factors that can impact these numbers. Regression analysis can tell you what factors matter the most and how they relate to each other. Linear regression models the relationship between two variables, one of which is independent and the other, which is dependent, using a linear function.

In Excel, you perform a linear regression using the least-squares method. The formula is y = bx + a.

The goal is to find a and b.

The LINEST function in Excel uses the least-squares method to calculate a straight line that best explains the relationship between your variables and returns an array that describes the line. The syntax will look something like the following:

=LINEST(known y,known x)

The “known ys” are required and reference the dependent y values in the regression equation. Typically, this is a single row or column. The “known xs” are optional and involve a range of the independent x values.

This must be entered as an array formula. Select two adjacent cells in the same row, type the formula, and press Ctrl, Shift, and Enter (simultaneously) to complete it. In return, the formula provides the b coefficient and the a constant.

Analysis ToolPak

You can run regressions in Excel by using a special tool included with an add-in. This will eliminate the need to use the LINEST function. To run the Analysis ToolPak

1. Click File > Options
2. Select Add-Ins on the left sidebar, then select Excel Add-Ins and click Go
3. Select the Analysis ToolPak and press OK
4. Select Regression within the Data Analysis tab after enabling the ToolPak
5. Configure your settings and fill out your variables to observe the regression analysis

How to show formulas

Once you get comfortable with formulas, you may find that you’re using one of more of them frequently in one spreadsheet. This can start to make your data confusing. You might wonder how these formulas relate to each other and what your original data was. Opting to show your formulas within Excel instead of the results can help you keep track of what was used in each calculation as well as make it clear whether you made any errors in your formulas.

When you enter a formula in a cell and press Enter, Excel will immediately display the results. However, there are a couple other options for displaying all the formulas within the cells instead.

Using the Excel ribbon

While on your Excel worksheet, go to the Formulas tab and select the Formula Auditing group. Next, click the Show Formulas button.

You’ll instantly see the formulas displayed within the cells. If you want to perform a quick check of everything for accuracy, you can just as easily revert to the original display. In that case, just select the Show Formulas button again.

Show Formulas shortcut

To quickly see every formula displayed, this is the fastest route. If you plan on toggling back and forth often, remembering this shortcut will save you a lot of time.

The keyboard shortcut is Ctrl + ` (hold the control key and the grave accent key simultaneously). The grave accent key is the furthest to the left at the top of your keyboard — just to the left next to the number 1 key.

How to use absolute reference

In an Excel spreadsheet, a cell reference is used to set up a formula that will calculate information you want from a cell or a range of cells. It can refer to data from contiguous cells, in different areas of the worksheet, or on other worksheets in the same workbook.

Relative cells copied to more than one row or column will change depending on their position. This is useful when you want to use the same calculation in each row or column. But if you need your cell reference to stay the same when it’s moved, copied, or filled, you’ll find absolute reference useful. It will allow the reference to point back to the same cell, regardless of the appearance of the workbook.

You may find yourself using absolute reference when dealing with fixed multipliers, creating invoices with a fixed tax rate, or for project management purposes.

Here are the steps to creating an absolute reference:

1. Click the cell where you want to enter a formula.
2. Type = (an equal sign) to start the formula.
3. Select a cell and then type the appropriate arithmetic symbol (=, -, /,*).
4. Select another cell and then press the F4 key to make the cell reference absolute. If you continue to press F4, Excel will cycle through the different reference types. When you do this, note the placement of the dollar sign.
5. Click the Enter button on the formula bar or press Enter on your keyboard.

Mastering functions

Why functions matter in Excel

Mastering Excel is all about knowing how to do things in the most effective, time-efficient way. Since Excel is the go-to software for making sense of data, the more you know about it, the better.

According to the Harvard Business Review, if we work in an office setting, we spend a surprising amount of time — over 10 percent of our lives — working with spreadsheets, and for people who work in data-driven fields (like research), that percentage triples.

That’s a lot of time, which is why improving your Excel skills and decreasing the amount of time you spend with it each day would make you more productive and get some hours of your life back. Here we’ll cover some Excel functions that can make your data-driven tasks a bit easier.

Using the IF function

According to Microsoft Office Support, a lot of Excel users find the IF function one of the most useful. It helps you run a kind of logical test on your data. Basically, it enables you to ask Excel to test a condition, and it will tell you whether the condition is TRUE (has been met) or FALSE (has not been met).

For reference, here’s how the syntax for the IF formula looks:

IF(logical_test, [value_if_true], [value if false])

There are three arguments in what we see above, but only the first one (logical_test) is required. This is where you establish the rules of your test using a value or other data point. The remaining arguments are optional:

• Value_if_true essentially tells the program to return a value if the condition in the test has been met.
• Value_if_false is the opposite. It’s the value if the condition fails the test.

Example of IF functions

If you’re testing to determine whether a score is pass or fail, it could look like this:

=IF(A1>=75, “Pass”, “Fail”)

So if the value in A1 is equal to or greater than 75, Excel will return with a “Pass.” Otherwise, it’ll return with a “Fail.”

Using VLOOKUP

VLOOKUP can be a huge help when you’re looking for something specific within Excel, as it eliminates the need to scroll through all of your data. It helps you find things in a table or a range based on rows, depending on your preference. Generally speaking, the VLOOKUP function tells Excel

• What you want to look up
• Where you want to look for it
• The column number in the range that contains the value to return
• To return an approximate or exact match, indicated as 1 (true) or 0 (false)

Steps to using VLOOKUP

Microsoft Office Support suggests the following to build your VLOOKUP syntax:

1. Tell Excel the lookup value, the information you’re trying to find.
2. Tell Excel the area where the information will be located. Place the first column in your range as the first element. If the value you’re looking for is in D2, then your range should start with D. If the value is in B2, then your range should start with B.
3. Include the column number in the range where the return value is located (for instance, if the range starts with B, B is the first column; C, the second; and so on).
4. If you want a little more flexibility in the information that Excel returns, you can tell the program to give you matches that are close (by specifying TRUE) or matches that are exact (by specifying FALSE).

The function will look like the following:

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, approximate match (TRUE) or exact match (FALSE))

Using the MATCH function

The MATCH function is another lookup and reference tool within Excel. It searches for a specified value in a range of cells and returns the relative position of that value.

Here’s the syntax for the MATCH function:

MATCH(lookup_value, lookup_array, [match_type])

The first two elements are required. The look_up value sets up the value you’re looking for. The lookup_array specifies the range of cells to search. The match type is optional. It can be one of these values: 1, 0, -1.

• 1 or omitted: This finds the largest value in the lookup array that is less than or equal to the lookup value.
• 0: Finds the first value in the array that’s exactly equal to the lookup value.
• -1: Finds the smallest value in the array that’s greater than or equal to the lookup value.

Using the INDIRECT function

Excel’s INDIRECT function is unique because it doesn’t perform calculations or evaluate conditions or logical tests. The INDIRECT function is a reference tool that allows you to make adjustments to a cell reference in a formula rather than having to make changes to the actual formula. It gives you a lot of flexibility because adding or deleting rows or columns won’t impact the indirect references.

The syntax for using the INDIRECT looks like this:

INDIRECT(ref_text, [a1])

Ref_text is a cell reference or a named range. A1 is a logical value that specifies what type of reference is contained in the ref_text argument. If true, ref_text is seen as an A1-style cell reference. If false, ref_text is treated as a R1C1 reference.

If you’re looking to build dynamic cell references or lock cell references to prevent automatic changes, INDIRECT will be an incredibly useful tool.

Data filtering, sorting, and formatting

Getting organized

Now that you’ve familiarized yourself with the basics of Excel and some useful formulas and functions, we’ll cover some ways to format, sort, and filter your data so that you can more easily manage it.

Making a pivot table

A pivot table can be an incredibly helpful tool for summarizing and making sense of your large and potentially overwhelming data sets. Pivot tables provide a summary of the data within a chart, making worksheets with long rows and columns much more visually appealing and understandable. You can use them to compare sales of different products, combine duplicate data, or count rows that have data in common.

Here are the key steps to creating a pivot table:

1. First, it’s helpful to sort your data so that you can more easily manage it in the pivot table. Select Sort under the Data tab and indicate how you want your data organized.
2. Select any cell in your data set by clicking on it. Enter your data into a range of rows and columns.
3. Go to the Insert menu and select PivotTable. This will open a dialog box where you can insert the cells that you want included in your pivot table. Click OK when you’re finished.
4. Next you’ll see the PivotTable Fields window. You can drag and drop a field into the Rows area.
5. Drag and drop a field into the Values area.
6. Drag and drop a field into the Filters area.
7. The pivot table will calculate your values, but it’s always good to check the calculations to make sure that everything is functioning correctly.

Creating a dropdown list

Dropdown lists can be useful tools for showcasing predefined lists or creating an interactive form on an Excel spreadsheet.

Using data from cells

If you already have a list in the cells of your Excel worksheet, follow these steps:

1. Select a cell where you want to create a dropdown list.
2. Go to Data > Data Tools > Data Validation.
3. Within the Settings tab, select List as the Validation criteria.
4. In the source field, enter your cells (=$A$1:$A$4, for example) or manually select them and click OK.

How to group worksheets

A Microsoft Word file is referred to as a document. An Excel file is referred to as a workbook. A number of individual worksheets can often represent related information within one Excel workbook. You may occasionally find it useful to combine the worksheets in a workbook to access the data represented in each sheet at the same time.

Here’s how to consolidate your worksheets:

1. Start with a new worksheet. This will serve as your master worksheet. The data you combine will appear here.
2. Select a cell. When your data is merged, all the information will fill the columns below and to the right of this selected cell.
3. From the Data Tools group, in the Data tab, click on Consolidate.
4. In the dialog box, select the Sum button from the menu. As you probably know, you use the Sum function to add values in a worksheet, but you can also use the function to combine the data in different worksheets.
5. Next, choose the data you want to combine in the master worksheet. Click in the Reference field and highlight the desired cells from the worksheets you wish to consolidate. Click Add in the dialog box on your master worksheet.
6. You’ll need to repeat the previous step for your second worksheet. When you’re ready, click OK in the dialog box. This will consolidate the information into your master worksheet.

How to find and remove duplicates

Duplicate values can sometimes create problems in your Excel worksheets, largely because they can skew your data. If duplicates are complicating your data sets and making them harder to understand, Excel has some ways to manage this. For example, you can use conditional formatting to find and highlight any extra data you may want to clear.

Here are the steps to apply conditional formatting:

1. Select any cells that need to be checked for duplicates.
2. On the Home tab, in the Styles group, click on Conditional Formatting, select Highlight Cells Rules and then Duplicate Values.
3. A dialog box will pop up that contains two dropdown menus. In the dropdown on the right, choose the type of formatting you want to use for duplicate values and click OK.
4. Now all of your duplicate data will appear with the formatting you selected, such as Green Fill with Dark Green Text.

Removing duplicates from a single column

To begin, click on the column that you want to work on:

1. In the Data tab, go to the Data Tools group and click on Remove Duplicates.
2. A dialog box will open. If your column includes a header, check the box next to My list has headers.
3. A popup window will alert you to how many duplicates were removed, and what remains.
4. Excel only removes exact duplicates. If you have a typo or misspelling, Excel will not note it as a duplicate, so be sure to double-check.

Removing duplicates from multiple columns

1. First, specify which range of cells you want to remove duplicates from.
2. In the Data tab, go to the Data Tools group and click on Remove Duplicates.
3. Under Columns, select the columns you want to remove duplicates from. You’ll be able to deselect specific columns if they contain any data that you don’t want to remove.
4. Click OK, and you’re done.

How to alphabetize

You can easily organize your data from A–Z with the filter function in Excel. Follow the steps below:

1. Select one or several column headers.
2. On the Home tab in the Editing group, click Sort and Filter and then Filter.
3. Click Sort A to Z. You can also choose to sort in reverse alphabetical order (Z to A).

How to transpose

Sometimes you’ll need to switch data that’s in different cells. Of course, you can take the more manual route of copying the information you want to move and pasting it where you want it, but this sometimes creates the problem of duplicate data.

Excel has a handy formula that will actually do the work for you and help you avoid that problem. Here’s how to use the TRANSPOSE function to rearrange your cells:

1. Select an area of blank cells that corresponds to the number of cells you want to switch. So if you have six cells you want to flip, select six empty cells that are opposite of the original cells’ layout. (If you have two columns of three rows side by side, the area of empty cells should have three columns of two rows.)
2. In the formula bar, type

=TRANSPOSE(area you want to transpose)

If you want to transpose the information from A10 to B14, the formula will be =TRANSPOSE(A10:B14).

3. Press Ctrl + Shift + Enter to complete.

How to autofit

The quickest way to autofit in Excel is by double-clicking the column or row border.

• For one column: Place the mouse pointer over the right border of the column heading until a double-headed arrow appears, then double-click the border.
• For one row: Double-click the border over the lower boundary of the row heading.
• For multiple columns/multiple rows: Select them and double-click a boundary between any headings in the section.
• For the whole sheet: Press Ctrl + A and double-click the border of any column or row heading (or both).

Data visualization

The power of visuals

Excel is really impressive when you’re dealing with complex calculations or extensive data entry, but it isn’t always the easiest thing for our eyes to make sense of. Long, exhaustive rows and columns can be hard to process.

So when you’ve made sense of your data and need to present the story the data reveals, sometimes a visual aid can really help. Here are some tips and tricks for data visualization that will take you beyond the spreadsheet.

Using tables in Excel

When your data is organized in rows and columns, it may look like a table. But an Excel worksheet tends to lack the stylized, colorful presentation of a true table. Using the Excel table option allows you to create the look of a table and isolate certain portions of a worksheet that will function independently of other information in the worksheet.

Besides the obvious aesthetics, here are some other bonuses of using a table:

• When you want to add or remove rows, tables adjust automatically.
• There are different table styles, which makes formatting easier.
• Sort and filter options are part of the layout.
• You can create a “total” row that automatically totals as you add rows.
• If you’re scrolling through data, the column headings will always be visible.

How to create a table

Here’s how to create a table that uses a default style:

1. First, click on a cell within the set of data.
2. In the Insert tab, go to the Tables group and click on the Table button.
3. A dialog box titled Create Table will open. Excel will select your data automatically, but you can make changes. You can make your first row a header row by selecting the My table has headers box.
4. Click OK to finish.

If you’d prefer to select your own style for the table, follow these steps:

1. As with the previous instructions, first click in a cell within your data set.
2. On the Home tab, go to the Styles group and click on Format as Table.
3. Excel will display a gallery of styles to choose from. Just click on the one that suits your needs.
4. You may need to make adjustments to the range. Don’t forget to set the header row if you need one.
5. Click OK.

How to name your table

The name of your table will automatically default to Table1 or Table2. This is fine if it suits your needs, but it’s often easier to locate a specific table if it has a distinct title.

You can rename a table in a few easy steps:

1. Click on any cell in the table you want to rename.
2. Go to Properties, and enter a new name in the Table Name box.
3. If you press Ctrl + F3, you will call up the Name Manager, which displays all of the table names in your workbook.

Using charts in Excel

Charts are undoubtedly one of the best tools to translate your data from a chaotic group of numbers and unwieldy text to something that’s understandable and effective. Here, we’ll cover how to make a range of graphs and charts — from a pie chart to a histogram:

• Enter your data into Excel. Save yourself time and stress by making sure that all of the data you enter is clear and accurate. Otherwise, you may discover that things look a little strange once you look at the final chart or graph.
• Choose your chart or graph. Excel has plenty of choices, but it’s important to consider what your data is and what will be the most effective way of presenting it to someone who doesn’t have the same knowledge. The choices are pretty extensive — from bar graphs to scatter plots and more.

Creating a pie chart

1. In your spreadsheet, select the data you want to use for your pie chart.
2. Click Insert > Insert Pie or Doughnut Chart and then make a selection from the available pie charts.
3. Select the chart and sort through the icons to make the finishing touches. Chart Elements will help you format axis titles and data labels. Chart Styles will allow you to manage colors, and Chart Filters will help you hide selected data.

Creating a line chart

1. Select the data you want to plot on the line chart.
2. From the Insert tab, select Insert Line or Area Chart.
3. Select Line with Markers.
4. You can change design elements in the Design tab.
5. On the chart, click the legend — or you can add it from a list of chart elements (Add Chart Element > Legend).
6. To plot one of the data series along a secondary vertical axis, select it from the Format tab, which you can find under Chart Elements. Click Format Selection.
7. In the Format Data Series dialog box, go to the Series Options tab. Under Plot Series On, select Secondary Axis and then click Close.
8. In the Chart Layouts group, under Add Chart Element, you can add and edit titles.
9. In the Chart Styles group, select a theme to use for colors and personalization.

Creating a bar or column chart

1. Enter your data in a spreadsheet.
2. Select the data you want to present in a bar or column chart.
3. From the Insert menu, select Insert Column or Bar Chart and pick which chart you’d like to use.
4. If you want to customize your chart, you can do that by going to Design > Chart Styles and Format > Shape Styles. This is where you can select various formatting options.
5. For further formatting options, like adjusting the axes, click Format and pick a component in the Chart Elements dropdown menu. Next, click Format Selection and make the necessary changes. You’ll need to repeat this step for every element you want to customize.

Creating a Gantt chart

Excel doesn’t offer a built-in Gantt chart option, but with a little clever formatting and the bar graph functionality, you can build one yourself.

1. Enter your data into the spreadsheet.
2. From the Insert menu, click on Insert Column or Bar Chart and select Stacked Bar.
3. Right-click anywhere within the chart and click Select Data. In the Select Data Source dialog box, click the Edit button under Legend Entries (Series). The Edit Series dialog will open. Name the field, then click the range selection icon (next to the Series name field option). Now select the data from your spreadsheet by clicking on the first appropriate cell and dragging down to the last. Click OK in the Edit Series dialog, then click OK in the Select Data Source dialog.
4. Next, add task descriptions to your Gantt chart by replacing the left side of your bar chart with your chosen descriptions.
5. You’ll still have a stacked bar chart, but with the modifications to the fill and border colors, it will resemble a Gantt chart.
6. Now, we just need to fix our tasks, because they’re listed in reverse order. Click on your list of tasks, which will display a Format Axis dialog box. Select the Categories in reverse order option under Axis Options.

Creating a histogram

A histogram is a graphical representation of the distribution of numerical data. Like Gantt charts, it isn’t available through Excel’s built-in options, but you can use a formula to get the presentation you want.

The FREQUENCY function will return the number of values that fall within specific ranges, ignoring text values and blank cells.

The syntax is as follows:

=FREQUENCY(data_array, bins_array)

• Data_array is the set of values you want to examine for frequencies.
• Bins_array is an array of bins for grouping the values.

Enter the FREQUENCY function as a multi-cell array formula. Select a range of adjacent cells to output the frequencies, type the formula in the formula bar, and complete it by pressing Ctrl + Shift + Enter.

Applying Excel to your life and business

Being confident in your Excel capabilities will help you succeed in the office and beyond. Once you’re acquainted with the software and know the wide range of its capabilities, you may find yourself using Excel to complete a much wider range of tasks, both at work and at home.

To make things easier, we’ve included tips for converting your Excel documents into different formats, as well as several real-life use cases for Excel.

Excel conversions

Excel to PDF in Microsoft Office

1. Open Microsoft Excel and the file that you want to convert to PDF.
2. Under the File menu, select Export and then Create PDF.
3. Once you click the Publish button, you can open your newly created PDF file.

Excel to PDF using Acrobat PDF Maker

1. Open Microsoft Excel and select the file you want to convert.
2. The Acrobat Ribbon is where you’ll create the conversion.
3. Click Create PDF and choose the sheet you want to convert. You’ll need to name the file. Then you can view your exported PDF.

Convert a PDF to Excel using Adobe Acrobat Pro

1. Go to Acrobat Pro and open the file you want to export to Excel.
2. Select the Export PDF tool.
3. When asked which format you’d like to save the file in, select Microsoft Excel Workbook.
4. Click on Export, and you can view the exported PDF in Excel.

Convert a PDF to Excel using Microsoft Word

1. Open the PDF in Word.
2. Click OK in the popup window that appears.
3. Save the document as a web page.
4. When you open Excel, you can browse to the web page file.

Excel applied

Excel for project management

As we’ve seen throughout this guide, Excel can tackle a lot of data-related projects — from working through basic math to creating more complex formulas and completing a wide range of data visualization tasks. That makes Excel a powerful tool for project management.

Gantt charts, as an example, can be an incredibly powerful way to track the phases of a project. The series of horizontal bars each represent a specific task for your project and can make your team’s work schedule more understandable through clear visuals.

Excel can also be great for making and updating budgets. Excel’s seamless compatibility with other Microsoft Office software, like Word and PowerPoint, means that it’s also easier to present your budgets in a variety of ways.

How to make a budget

While creating a budget for Excel at work is going to be more extensive and complex than making your own, this is a great way to showcase how the software is a vital tool at the office and at home. Here’s a quick guide for making an at-home budget or for tracking work expenses using Excel:

1. Create your row headings (this is where you label the various categories of your expenses).
2. Create column headings for weeks/months.
3. Enter the figures for your week/month.
4. Using the =SUM formula in Excel, highlight the column with your figures and add them together.
5. Enter your income and use the subtraction formula we covered earlier in the guide.
6. Format your sheet to suit your preferences.

How to generate random numbers

In some cases, you might want to randomize your data. To generate random whole numbers in Excel, use the RANDBETWEEN function. This will generate a number between two numbers that you specify.

1. Choose the cell where you want the random numbers to appear.
2. Use the following formula in the cell: =RANDBETWEEN(1,100). The numbers in the parentheses are just an example. If you want a range that’s smaller or larger, just select smaller or larger numbers.
3. When you press the Ctrl key and Enter at the same time, you’ll get 10 random numbers in the cells.

How to create a database

Microsoft Excel has built-in tools that make it easier to find specific information and keep track of data. To create your own database in an Excel worksheet, follow these steps:

1. Enter your data, and make sure to double-check that it’s all correct. Data errors will lead to future problems.
2. Your rows will function as records, and columns will function as fields.
3. Convert your entered data into a table.
4. You can use the dropdown arrows located beside each field to filter your data.
5. You can sort data A–Z and filter your data through text specifications.
6. There are functions you can apply specifically for databases. These include DAVERAGE, DCOUNT, DGET, DMAX, DMIN, DSTDEV, and more.

Microsoft Office Excel alternatives

We’ve dedicated a lot of time to covering what you can do with Excel and how to do it, but there are other options out there for completing your Excel-oriented tasks.

Google Sheets vs Excel

Google Sheets is probably the number-one alternative to Excel; its cloud-based technology makes it optimal for sharing your data with coworkers and clients, and it can do the majority of what an average Excel user needs. Its auto-save feature prevents you from losing your worksheets during unexpected computer issues.

Although Excel has a significant head start, Google is catching up in terms of both functions and formulas.

Other Excel alternatives

Apache OpenOffice Calc is another alternative to Excel. It resembles Microsoft Office software from around 2003. Though it can complete most of the same functions as Excel, it often does so in different ways.

On the downside, LibreOffice Calc struggles with advanced charts, and it can’t handle outside data sources, says TechRepublic.

JotForm and Excel

JotForm is well integrated with Excel, which can save you the headache of converting data and help make sharing more efficient. You can export your forms from JotForm to an Excel spreadsheet with ease, and you can indicate which fields to include in the report. You can also apply password protection to your report.

While Excel can seem daunting at first glance, once you’ve become comfortable with the ins and outs, you’ll find it to be an incredibly useful tool. Taking it step by step and familiarizing yourself with the basics before moving on to more complicated territory will make it feel a lot less daunting.

When it comes to sorting, organizing, and understanding your data, Excel could quickly become your favorite tool. JotForm’s capabilities for importing and exporting Excel reports are especially helpful for all users, whether they’re a certified pro or just starting out.