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>

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.

Need Help? Please call us at 1.866.924.6244

Copyright © 2002-2017

EMAGENIT All Rights Reserved