Microsoft Excel Tip of the Week

Business, Engineering and Science

Microsoft Excel Tip of the Week Log

Microsoft Excel Tip of the Week

Listed below is a log of our previous Microsoft Excel Tip of the Week examples.

Presented below is a log of our Microsoft Excel tip of the week topics. The topics covered in the tips are the ones that we commonly present in our Microsoft Excel training. They are updated just about every week so check back often.

Log Pages: Page 1 <Page 2> <Page 3>

How to Create a 3-D Cell Reference for Summing Multiple Worksheets

Microsoft Excel Tip

When performing tasks in Microsoft Excel, the need will arise to sum values on multiple worksheets. There is a simple way to do this if the sheets have the same layout and are in sequence left to right in the workbook with nothing between them, it is called a 3-D reference.

  1. To construct a 3-D reference that sums, first select the cell to put the Sum function in. Next type the call to the Sum function in the cell and stop at the first (.
Shift Key/Click Tabs

Type in the Sum function call in the cell.

  1. Next proceed to the first sheet and click on the cell to add, then hold down the shift key and select the right most sheet tab in the group to sum, and then type a ) to close off the Sum call formula. Observe the formula bar while doing this and you will see the 3-D reference. In this example it looks like: =Sum('Department A:Department B'!E24). Press the Enter key to enter the formula. Note that you can click in the Formula bar at any time during the process although it is not necessary as the formula remains in focus when typing even though you are clicking on cells.
Shift Key/Click Tabs

Hold down the Shift key and select the worksheets tabs.

To learn more about intermediate Excel operations, check out our Intermediate Microsoft Excel Training.

How to Create an Excel Table

Microsoft Excel Tip

When designing formulas on an Excel worksheet, the need will eventually arise to have these formulas track your data as it grows. While you can design range names that will grow when a data range grows, this requires you to know how to create the range name formulas and maintain them. A simpler way is to turn a data range (i.e. table) into what is called an Excel table. These tables will expand and contract with the data automatically and will allow formulas to track ranges within the table without having to constantly adjust them.

To create an Excel table, first design a proper table (yes that is what it is called in every other medium besides Excel so we are calling it that here) on the worksheet using basic database rules. The rules are, same type data in each column, unique headers in the first row bolded and no complete empty rows or columns in the table. The columns can have blanks.

Properly Designed Table on a Worksheet

Properly Designed Table on a Worksheet

Next click anywhere within the table boundaries and press Ctrl+T. You will see Excel automatically select the entire range and flash a dialog box. Click OK on the dialog box to create the Excel table.

Press Ctrl+T to Create an Excel Table

Press Ctrl+T to Create an Excel Table

When Excel creates the table, it will automatically format it. You can control these formats and other table features by using the Table Tools Design tab. This tab will appear when you click in the table and disappear when you click out of the table. When the Excel table is created, it will have a name. This name will be used to reference it in formulas. To adjust the name (highly advised), proceed to the Design tab, go to the far left-hand side and type a new name. Use only Alpha Numeric characters and underscores, no spaces and no cell reference names like A1. You can use periods but don't because its confusing in VBA. The underscores help space the name out and maybe delimit it for later use in VBA (hint).

Table Tools Design Tab

Table Tools Design Tab

To use the table in your formulas, select the columns or table like you would a normal range in a formula. In the formula, you will see what is called a structured reference appear. This reference will change appearance depending upon what it is highlighted. To learn the syntax, just highlight various parts of the table in your formulas. The structured references will generally appear when selecting all of a column, row or table with or without the headers.

Once created, your formulas will now adjust automatically as the table grows or as the columns change location (very cool). To add data to the table, type or paste data underneath it or to the side of it. Formulas entered in a column of the table will automatically fill down on their own, just type them beneath the headers. These little jewels make templates a whole lot easier to build and maintain.

Structured References

Structured References

To learn more about Excel tables and their many uses, check out our Microsoft Excel Dashboards Training, our Intermediate Excel Training and our Advanced Excel Training.

How to Turn the Gridlines and Headers Off on a Worksheet

Microsoft Excel Tip

When designing dashboards and reports on an Excel worksheet, you want a nice, clean, paper like appearance to make your product look professional. The gridlines and headers on an Excel worksheet can be turned off to produce this appearance. To accomplish this task, select the worksheet whose headers and gridlines you want to remove, proceed to the Ribbon and select Page Layout. On the Page Layout tab, uncheck the Gridlines and Headings check boxes.

Gridlines and Headers Check Boxes

Uncheck the Gridlines and Headings Check Boxes Under Page Setup

The worksheet will now look like a sheet of paper. Be sure before you do this, to color the borders and backgrounds of the cells that you want to enhance on the worksheet. This can be done using the Format Cells dialog box (Ctrl+1) and the Home tab.

Freeze Top Row of Worksheet

Report Using This Concept (Chart, ActiveX Control and Conditional Formatting)

Freezing Rows and Columns on a Worksheet

Microsoft Excel Tip

When you have a large range of data on a worksheet, could be a table of data, a calculated display area,... the need generally arises to freeze a header row or column of information so the user can see it while they scroll the worksheet.

To freeze the top row of a worksheet:

  • Select the worksheet tab whose top row you want to freeze. Next, proceed to the Ribbon and select View/Freeze Panes/Freeze Top Row. The top row of the worksheet will now stay stationary while you scroll down the sheet.
Freeze Top Row of Worksheet

Freeze Top Row on Worksheet

To freeze the first column of a worksheet:

  • Select the worksheet tab whose first left column you want to freeze. Next, proceed to the Ribbon and select View/Freeze Panes/Freeze First Column. The first left column of the worksheet will now stay stationary while you scroll to the right on the sheet.
Freeze First Column of Worksheet

Freeze First Column on Worksheet

To freeze a worksheet at a specific column and row:

  • Select the worksheet tab whose row and column you want to freeze. Next, click on the cell below the row you want to freeze and to the right of the column you want to freeze. Just think of it this way, the worksheet window will freeze on the left-hand side of the selected cell and along the top of it. Last, proceed to the Ribbon and select View/Freeze Panes/Freeze Panes. The worksheet will now stay stationary to the left and top of your selection while you scroll the sheet.
Freeze Row and Column of Worksheet

Freeze Row and Column on Worksheet

To unfreeze a worksheet, proceed to the Ribbon and select View/Freeze Panes/Unfreeze Panes.

Closing All Workbooks at Once

Microsoft Excel Tip

Short and simple tip, if you have 20 workbooks open and you want to close all of them at once, hold down the Shift Key and then click the close button in the upper right-hand side of any Excel window. It will prompt to Save All if required. Do not let up on the Shift key until everything is closed.

Organize Pie Data

All Close

The UFO Pie Chart

Microsoft Excel Tip

In the world of charting in Excel, one of the coolest charts to make is what we call the UFO pie chart. Think flying saucer and you are on the right track. The idea is, if the data is not the best news, at least the charts look cool!

  1. To make the chart, first organize your data on the worksheet with the category labels in the first column of your table and the pie values in the second. Bold your headers to make them different from the rest of the data
Organize Pie Data

Organize Your Data for the Pie Chart

  1. Click anywhere inside of your table, proceed to the Ribbon and select Insert / Pie Chart / 3-D Pie.
Pie Chart Type

Select Insert / Pie Chart / 3-D Pie

  1. Click and hold in the chart's white area, close to the border edge, and move that chart where you want to put it on the worksheet.
Move the Chart

Move the Chart

  1. Next click on the pie chart itself. Be very careful, you want to select the entire pie chart, not a slice. If you click twice over the pie chart, once to select it and once again, you will select an individual slice. If you mess up, click on a worksheet cell, this clears the chart selection, and then reselect.
Select Entire Pie Chart

Select Entire Pie Chart

  1. Right mouse click over the pie chart and select Format Data Series.
Format Data Series

Format Data Series

  1. On the Format Data Series dialog box, click the effects tab, then select 3-D Format. In the bevel boxes, type in 512 in all boxes. You do not have to type pt. These numbers are what give the pie chart the UFO shape. Altering them will change the effect.
Format Data Series

Format Data Series

  1. Now push it over the top. With the pie chart selected, now click on an individual pie slice by clicking on the pie chart again. On the dialog box that is still open, it should now say Format Data Point. Select the Fill & Line tab then the Fill group. In the Fill group, select a solid fill color then make it transparent, 30 to 50% usually suffices. The transparent effect with the bevel usually makes the crowd go wild. Use vivid colors. Have fun.
Set the Transparency

Set the Transparency

To learn more about these type of charts, check out our Microsoft Excel Dashboards Training.

How to Quickly Determine If a Value in One Worksheet Table Exists in Another Worksheet Table

Microsoft Excel Tip

One of the most common tasks you will face in Excel is determining if a value in one worksheet table exists in another worksheet table. While there are many ways to do this, a quick way is to use the COUNTIF worksheet function in a formula. Simply put, the COUNTIF worksheet function counts how many instances of what you are looking for, called the criteria (i.e. logic), exists in a range of data that you specify. The function is easy to use in a formula when looking up values and is very versatile. Remember when checking for the existence of something using logic, the easiest test to do is to count.

  1. To use the COUNTIF function, first open the workbooks containing the tables that you will need to check. The data does not have to be in the same workbook.
Ribbon Data/From Access

Open the Workbooks the Containing Data

  1. Next in the workbook that contains the values you want to find in the other table, designate a column in the table to place the COUNTIF formula in. In our example, we called it Used.
Select Data Source Dialog Box

Create an Extra Column

  1. In the column just created, type a formula using COUNTIF in the first cell starting with =COUNTIF(  . The first argument for the function will be the column range of the other table that you want to look in using COUNTIF, in this example A1:A14. The extra notation in front of A1:A14 is used to reference an external range, a range in another workbook. This was automatically picked up when we selected the range with our mouse. The second argument will be a cell reference to the value to lookup in the table, in this case cell A2. When selecting the two arguments with the mouse, be sure to type a comma after the first argument selection so you can use the mouse to select the second argument. Type a ) and press Enter to enter the formula. Be sure to use an absolute reference to the range of data you are looking in, in our case A1:A14. This means putting dollar signs in the range reference like $A$1:$A$14. That will keep the range reference from moving in the formula when it is dragged and filled down the worksheet. That is what an absolute reference means, it does not change when copied. Sometimes the $ signs are inserted automatically like in an external reference when selecting it.
Select Data Source Dialog Box

Type the COUNTIF Formula

  1. Last, select the cell you just created the formula in, click and hold on the fill handle of the cell (black or green dot, lower right-hand corner of cell), then drag it down the required number of rows and release the left mouse button. You can also double click on the fill handle dot as well to send it down the sheet. Be careful, it may stop early if there is a gap in the data.
Import Data Dialog Box

Drag and Fill Formula Down Sheet

  1. Where you see a number greater than 0, the value exists in the other table. One note here, this type of problem is highly susceptible to rogue data with extra spacing at the beginning and ends and also non-printable ASCIIs on the ends. To counter this problem in the formula, you can use * 's along with the lookup value. If you use *Text, that means ends with the value, if you use Text*, that means begins with the value and if you use *Text*, that means contains the value. You can use the & ampersand operator to concatenate the *'s with the cell value in the formula, for example =COUNTIF( [Book2]Sheet1!$A$1:$A$14, "*" & A2 & "*" ). You will have to type these. Be careful with the contains, *Text*, as that might grab unintentional data. Think this way, if looking for xyz, that might be part of xyzz and so forth, not what you are looking for. Start with the other two cases first and see if they solve the problem as they are more precise. You will know you have extra spaces or ASCII's in a problem like this because you will run COUNTIF and return 0 even though you know the value exists in the other table. Instead of using the *, you could also just clean the lookup data using the TRIM and CLEAN functions first before analyzing with COUNTIF. Bottom line, know your data and never assume it is flawless .

To learn more about looking up data, check out our Intermediate Microsoft Excel Training.

LinkedIn
Facebook
Information
Need Help? Please call us at 1.866.924.6244

Copyright © 2002-2017

EMAGENIT All Rights Reserved