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 Quickly Link to a Microsoft Access Table from Excel

Microsoft Excel Tip

Many times when accessing data in Excel, your data will be stored in an Access database table. You could open the Access database and export the table to an Excel worksheet every time you wanted to view the data, but this would be tiresome and cumbersome to say the least. A better way to access the table is to use the Data Connection Wizard. Using this wizard, you can dynamically connect the Access table to a worksheet and be able to refresh it at will from Excel. You do not have to have Access on your computer to accomplish this task. When following the instructions below, please note that the screens will vary a little in capability depending upon what version of Excel you are in.

  1. Make the workbook active in Excel that you wish to link to Access. Next proceed to the Ribbon and select Data/From Access.
Ribbon Data/From Access

Ribbon Data/From Access

  1. On the Select Data Source dialog box, select the Access file and then click the Open button. This file can be on your computer or in a network folder. Note that you may also be prompted for a password after clicking open.
Select Data Source Dialog Box

Select Data Source Dialog Box

  1. If the database has multiple tables, the Select Tables dialog box will appear. On this dialog box, choose the table that you want to connect to. In Excel 2013 and above, you can make multiple table selections at once by checking the Enable... check box. If you do this, Excel will automatically create the tables on separate worksheets when the entire process is completed. Click the OK button.
Select Data Source Dialog Box

Select Table Dialog Box

  1. On the Import Data dialog box, select the Table option. On the dialog box, you can also click the Properties button which will give you a dialog box that will control refresh options and other controls. Down at the bottom of the Import Data dialog box, you can select to place the table on an existing worksheet or in a new one. If importing multiple tables, these will be greyed out. If outputting to an existing worksheet, be sure to click in the box under the Existing... option then select the worksheet and cell you want to output to. Click the OK button.
Import Data Dialog Box

Import Data Dialog Box

  1. Once completed, Excel will create an external data range as an Excel table on the worksheet. This table will automatically expand and contract with data by default. If you want to use the table in calculations, read up on Excel tables as they use a special range notation called structured references. This notation can be seen in the formula below. What makes these range references cool is that they expand and contract with the data so no adjusting the ranges when the table changes. To make adjustments to the Excel table, click in the table area, proceed to the Ribbon and select the Table Tools Design tab. To change how the external data range operates, how it refreshes, how it brings in the data, on the Table Tools Design tab click either Properties or click on the arrow under Refresh and go to Connection Properties. To refresh the table manually if the data changes, just click the Refresh button on the Table Tools Design tab. This can also be done from the Data tab or by right mouse clicking over the table.
Excel Table Linked to Access Table

Excel Table Linked to Access Table

A couple of things to be aware of when doing this. First, know the table that you are trying to access and do not mess with anybody's database without permission. Second, 10,000-50,000 records (rows) in the table, good to go. 200,000 records and above, maybe you might want to try querying the database table using Microsoft Query in Excel. This tool will allow you to create a query that trims the table down to what you want. Pulling that much data across a network may take a bit of time and zap resources.

To learn more about advanced Excel operations, check out our Advanced Microsoft Excel Training.

How to Efficiently Arrange Your Excel Workbook Windows for Viewing

Microsoft Excel Tip

When copying and pasting, reviewing data, constructing models... in Excel, you may need to view two or more workbook windows to complete your operations. The first way to view the separate windows, is to toggle between them by pressing the Ctrl + Tab keys which allows you to rapidly scroll through all open workbooks. You can also proceed to the Ribbon and select View / Switch Windows. While these techniques work, they will also slow you down if you have to keep constantly shifting between workbooks. A better way is to arrange the workbook windows on the screen at once so you can view everything you need in one glance (it also helps to have a huge monitor at this point). To accomplish this automatically, first proceed to the Ribbon and select View / Arrange All.

Arrange All

Select View / Arrange All.

When the Arrange Windows dialog box appears, select which configuration you would like to see the workbook windows automatically arranged in for viewing. Note have two or more workbooks open at this point or you will not see anything really happen.

Arrange Windows

Arrange Windows Dialog box

Tiled

Tiled Arrangement

Horizontal

Horizontal Arrangement

Vertical

Vertical Arrangement

Vertical

Cascade Arrangement

The Tiled option places the workbook windows in a tile layout of equal size when possible, generally even # of workbooks will do this. The Horizontal option stacks the workbooks on top of each other in equal sizes. The Vertical option stacks the workbooks side by side in equal sizes. The Cascade option arranges the workbooks in a folder like paper layout, think of thumbing through papers. You will find the first three options the most useful. A couple of tips, first if you are limited in screen real estate, you may want to minimize the Ribbon for extra viewing area. To do this rapidly, just double click on any Ribbon tab. To get the Ribbon back permanently again, double click on any tab again. Also, the active workbook is the one that you can see the selected cell in. To switch between workbook windows while in this style mode if you are copying and pasting, building formulas..., you will first have to click on the desired workbook window, then click again on the desired cell.

To learn more about basic Excel operations, check out our Beginning Microsoft Excel Training.

How to Link a Picture to a Worksheet Cell or Range for Advanced Displays

Microsoft Excel Tip

When creating advanced visual displays in Excel, you will sometimes want to project a cell or range and their features over maps, charts, diagrams...etc. Usually these elements float over a range obscuring it. If the cell or range and their features change, you would like the projection to update when the cell  or reference does. A simple way to create this projection is to copy and paste a cell or range as a linked picture. To accomplish this task, first select the cell or range to copy, then press Ctr + C to copy. Note in the range being copied in the picture below, conditional formatting has been applied to color any Qty under 50 red.

Copy Picture to be Linked

Copy the range or cell to be linked.

Next, proceed to the Ribbon and select Home/Paste/Linked Picture. A picture of the cell or range copied will now appear floating over the worksheet where it was pasted. You can arrange it and resize it in combination with other graphics. Anytime the cell or range that is linked to the picture is changed, the picture will update. That includes elements floating over the cell or range as well like charts, hmmm?. Somehow toggle the range, toggle the picture?

Paste Copied Cell or Range

Select Home/Paste/Linked Picture to paste a copied cell or range as a linked picture on a worksheet.

As just discussed, once pasted, the linked picture can be moved Forward/Backward by right mouse clicking over it and selecting those commands from the shortcut menu. When clicking on the picture, observe the formula bar and you will see its formula. This formula obeys the same basic rules as those used for building range references in formulas like sheet name, workbook name, ! and so forth. You can also use a cell or range name instead of a cell or range reference? Also the picture can be resized by clicking and holding on its white sizing handles on the border and dragging them. Note that the corner handles will keep the aspect ratio when sizing.

Move/Resize Linked Picture

Move the picture Forward/Backward to adjust viewing and modify the formula in the formula bar using the rules followed for cell and range references.

To learn more about advanced visual displays, check out our Microsoft Excel Dashboard Training.

How to Link an Excel Worksheet Cell Value to a Drawing Shape for Advanced Displays

Microsoft Excel Tip

When creating advanced visual displays in Excel, you will want to display your cell values over maps, charts, diagrams...etc. More over if the cell values change, you would like these display values to update when the cell does as well. One simple solution is to take a drawing shape and link it to a cell to display its value. To accomplish this, first go to the Ribbon and select Insert / Shapes and then select a drawing shape that can be filled like a rectangle. Next click the mouse on the worksheet to draw the shape on the worksheet. The squares around the edges adjust the size of the shape and if you click and hold on a border (not a square) you can drag the shape around the screen. To delete press the Delete key.

Draw Shape

Create a shape on the worksheet that has a fill to it like a rectangle.

Next, you can link the drawing shape to a cell by cell reference or via cell name. Naming a cell is next week's discussion (hint for now: Click on the cell, find the box to the left of the formula bar that has the cell reference like A1 below, click in the box and type the name. The naming rules are alpha numeric, do not replicate cell references, no spaces, just alphabetical characters A-Z, numbers and underscores, do not use periods (VBA thing). Formulas / Name Manager controls the name editing and deletion. So click on the drawing shape, click in the formula bar, type an equal sign and either click on the cell to be referenced or type the name of the cell being referenced. The value of the cell should appear in the shape when Enter is pressed.

Link Shape and Cell

Click on the drawing shape, click in the formula bar and create the reference to the cell to link and display its value.

Now format the shape by right mouse clicking over it and selecting Format Shape. Remember you can make it invisible by saying no fill and no line. That allows you to float the shape over a map lets say and all you see is the value. If the shape is behind a graphic, right mouse click on it and use Bring to Front or Send to Back. You can also apply a simple format to the cell value like $0.00 and it will show up in the shape. And no the red text format does not show up and other more advanced formats as well so do not even think about it. If you want that level, that is when you copy the cell and paste link it as a picture, Conditional Formatting shows up then as well. All this is just the tip of the iceberg in assembling advanced graphics in Excel.

Format Drawing Shape

Click on the drawing shape and format it, you can also apply a number format to the cell.

To learn more about advanced visual displays, check out our Microsoft Excel Dashboard Training.

How to Paste Different Parts of an Excel Range (Paste Special)

Microsoft Excel Tip

Many times when copying and pasting cells and ranges on the worksheet, the need will arise to paste only certain parts of what you copied. To accomplish this, you use Paste Special when copying. To Paste Special, first select the cell or range that you wish to copy and then copy it by pressing Ctrl+C.

Copy Data

Copy the Cells or Range to Paste by Selecting and Pressing Ctrl+C.

Next select the destination cell, right-mouse click and select the Paste Special word from the shortcut menu. Note that when you pass your cursor over Paste Special, you will see icons appear representing all the different paste special options. Instead of trying to memorize these icons, it is much easier to use the screen we are about to use. Later you can learn the icons. Once the name is clicked, the Paste Special screen will appear.

Selecting Paste Special

Right Mouse Click Over the Destination Cell and Click the Paste Special Name.

On the screen, in the Paste group section, select the type of paste you want to perform. This is where you get to choose what you are going to paste. A common task to do is when you are pasting a range with formulas, you will want to remove the formulas and just keep the values because when you paste cells with formulas to different workbooks, it creates a whole host of problems like formulas not working right, external references... and so forth. To accomplish this task, just click the Values option and click OK. Only the values will paste from the selected cell/range. What you do not tend to think about is that you can do a paste in phases. So after pasting the values you can right-mouse click again over the start cell or highlighted paste area, activate Paste Special again and paste the cell formats then the column widths and so forth. You can keep going and only paste the particular elements you want excluding others.

Paste Special Values Option

Select the Values Option to Avoid Pasting Formulas.

Paste Special Different Parts

You Can Keep Pasting Different Parts Using Paste Special.

In addition to copying data, you can also perform mathematical operations while using paste special (discussed here), but one of the coolest things to do and a real time saver is that you can transpose information. That means you can copy a table, row or column and make columns into rows and rows into columns. To use this feature, you just check the Transpose box at the bottom of the screen.

Transpose Data

Use the Transpose option to Transpose Data.

In conclusion, learning how to take advantage of this feature can really make life easier when pasting information within Excel. If you are looking to automate Excel using VBA and build data processing systems, it is a must to know. One last tip, if you are only looking to copy formats only fast, you can click on the cell or range to copy the formats from, proceed to the Home tab and click the Format Painter button (Paint Brush) under Copy on the left-hand side. Next click or highlight over the cell or cells that you want to paste the formats to and the format will be pasted. The Format Painter has instructions when hovering over the button so be sure to read them.

Format Painter

Use the Format Painter to Quickly Transfer Formats.

To learn more about Paste Special, check out our Intermediate Microsoft Excel Training.

How to Use Conditional Formatting on the Excel Worksheet to Flag Data in a Cell

Microsoft Excel Tip

Many times when analyzing numerical data on the Excel worksheet, you want to visually flag data that meets some criteria that you have. Microsoft Excel has a built-in tool called Conditional Formatting that can help you accomplish such a task. To use Conditional Formatting, first organize your data on a worksheet.

Data for Conditional Formatting

Organized data to be flagged with Conditional Formatting.

Next select the data that will be flagged by highlighting it or if you are unsure of the size of the data set, just select the whole column by clicking on the column letter like B.

Selecting Data for Conditional Formatting

Cleaning table data so it can be filtered.

Next proceed to the Ribbon and select Home / Conditional Formatting / New Rule and the New Formatting Rule dialog box will appear. There area a lot of rules to use on this dialog box (from 1 at the top to 6 at the bottom) but the 2nd and 6th rule are the most useful when building interfaces that light up cells. The difference between them is that rule #2 looks at the actual selected cell value and uses logic to color it while the #6 rule can look at other cell values around it using logic formulas and color the selected cell based on their values. So on the dialog box, click the #2 rule (2nd row from the top).

Select Rule #2 on New Rule Dialog Box

Select rule #2 on New Formatting Rule dialog box.

On the dialog box screen, in the first drop down make sure Cell Value is selected, that is the default. That means you are going to look at the cell value directly in your criteria. In the second drop down from the left, select greater than in the drop down and in the last box type in the value to compare against like 2.00.

Select Rule #2 on New Rule Dialog Box

Fill out logic for #2 rule.

Once the logic is filled out, click the Format button and this button will pull up the Format Cells dialog box. This is where you will set the look of the cells to be flagged that meet the logic criteria you just set for the rule. For a quick example, click the Fill tab and set the color to Red and click the OK button. Now click the OK button on the New Formatting Rule dialog box and the cell values that are > 2 will color red. Note that if you selected the header cell in you selection and it colors by mistake, select the cell, go to the Ribbon and select Home / Conditional Formatting / Clear Rules / Clear Rules from Selected Cells to clear the Conditional Formatting.

Set Cell Format

Set the format for the cells that meet the criteria.

Colored Cells Meet Condition

Colored cells meet the set condition.

To learn more about Conditional Formatting, check out our Intermediate Microsoft Excel Training.

How to Remove Duplicate Data from an Excel Worksheet Range

Microsoft Excel Tip

Many times when analyzing data on the Excel worksheet, you have to develop a unique list of information from repetitive groups of information found in your data. For example, you have a list of part numbers and would like to see a list of the ones ordered or you have a list of client names and would like to see all the clients you dealt with.

Repeating Table Values

Table containing repetitive data.

To accomplish this task you can use the Remove Duplicates feature in Microsoft Excel. This feature can be used on a single column of data or on a table with multiple columns of data. If used on a table, Excel will look for the unique entries in each column in a row and will thus keep all the unique rows in the table. To use this feature, first copy the column or table you wish to use on to a clean worksheet. You can filter it in place if you want, but it will remove the data from the source table so that may not be desirable. You can include the table headers in the copy, just make sure they are distinct from the rest of the data and in one row. You can accomplish this by making them bold. Next make sure the text data in the columns have no imperfections like extra spaces after names, non-printable ascii characters and so forth. If the text data does include these imperfections then each variation of the name will be considered to be a unique instance by Remove Duplicates and will not be filtered. To clean your data, make an extra column next your text data and use worksheet functions like CLEAN, TRIM and SUBSTITUTE to remove unwanted characters from the text. When done cleaning the data, copy/paste it back over the original data using PasteSpecial/Values. Remember you have to know the text data to understand what needs to be removed.

Clean Table Data

Cleaning table data so it can be filtered.

After cleaning the data, now highlight it and proceed to the Ribbon and select Data/Remove Duplicates. On the Remove Duplicates screen, it should detect your headers if you have them. If not check the My data has headers check box. In the Columns area, select the columns that you want to look at to remove duplicates. Remember if you have a table, you do not have to scan all the columns for duplicates. Next click the OK button and the data should be filtered to unique values only. Remember if you have blanks in the table, the blanks may count as unique values. You may want to exclude that column from filtering by unchecking the column or further attention may be necessary for blanks before removing duplicates.

Remove Duplicates

Run Remove Duplicates to remove duplicate data.

Filtered Data

Filtered table.

To learn more about removing duplicates check out our Intermediate Microsoft Excel Training.

How to Use the Microsoft Excel Flash Fill Feature to Fill in Data in a Range (Table)

Microsoft Excel Tip

Excel 2013 and above has a new feature that allows Microsoft Excel to look around a table and take an intelligent guess as to how that information is being used in what you are currently typing in a cell. It can then automatically fill in the information in the table. This Microsoft Excel feature is called Flash Fill.

Flash Fill Overview

Flash fill overview.

To use the Flash Fill feature, first your Excel data must be organized in a clean tabular format. What this generally means is you have constructed a table on the worksheet with one row of unique headers, the same data types in each column of the table (means do not mix numbers and text for example) and no complete blank rows or columns in the table. Good design rules to follow regardless. The information you are typing must be next to the data in the table and must match at least part of that data. When typing, Microsoft Excel will look at the existing data around where you are typing and what you are typing and either figure out a way to splice it or separate it. So again good table structure and organization is key to this feature working. After typing a couple of lines, Excel may fill in its guess for you while you are typing. You will see Excel's guess as greyed out data. If you press Enter it will fill in the rest.

Flash Fill Filling Data

Microsoft Excel guessing what number of part name to fill in next.

If Microsoft Excel does not provide a preview, type in a line or two, highlight the range it goes into, proceed to the Ribbon and select Data/Flash Fill. This will fill the information into the remaining rows of the table. The example in the picture below merged the part and number. Alpha 3001 was typed first then the range highlighted and Data/Flash Fill clicked. This Microsoft Excel feature is very convenient when breaking apart or merging data in well behaved tables and requires no formulas keeping your data clean.

Flash Fill Using Data Tab

Using the Flash Fill button on the Data Tab to fill in data after typing a line.

To learn more about Flash Fill check out our Intermediate Microsoft Excel Training and Advanced Microsoft Excel Training.

How to Use Mixed References to Build Excel Worksheet Formulas

Microsoft Excel Tip

If you have been constructing basic formulas on the worksheet, you have been exposed to the concept of relative and absolute cell range references. Relative references are represented by a cell or range reference without dollar signs in their notation (A1) and absolute references are represented by two dollar signs in their notation wrapping the column letter ($A$1). When relative references are copied or filled down a page by dragging (autofill) the reference will change relative to where the reference is dragged or copied to. An absolute reference never changes when it is copied or dragged. This fundamental capability is what makes building models and data processing tools in Excel so easy.

Drag and fill using relative references

Relative reference formula being filled down a worksheet.

When dragging a formula on the worksheet, sometimes you want it to move in one fill direction and not the other. This comes into play for example when your formulas need to use a top row of values as they are dragged to the right but the row reference should not move as you fill vertically. To accomplish this feat you should use what is called a mixed reference. A mixed reference will move reference in one direction when copied/filled but no in the other. The notation is simple, if the dollar sign is on the right-hand side on the column letter (A$1), then the reference will change when it is copied/filled to the right or left on a worksheet but will not move vertically. If the dollar sign is on the left-hand side on the column letter ($A1), then the reference will move vertically when copied/filled but not left or right.

Constraining cell reference movement, mixed references

Depending upon where the $ dollar sign is placed on the column letter, the range/cell reference will only change when copied/filled in a certain direction. A$3 changes only in the vertical direction  when filled and B$2 changes to C$2 and D$2 when its formula is dragged horizontally.

In addition to straight formula construction on the worksheet, features like the Conditional Formatting can use formulas to build its conditions when coloring cells on the worksheet. The mixed reference factors heavily into the construction of these formulas because one can select an entire range to apply conditional formatting to at once. Conditional Formatting will fill the formula into this range once OK is clicked just like dragging a formula. By using mixed references, one can constrain how the formula will move reference as Conditional Formatting fills it into the selected range.

Conditional Formatting using mixed references

Conditional Formatting rule #6 using mixed references to read a row a of values and have all the cells in that range color themselves in based on their column value.

To learn more mixed references check out our Intermediate Microsoft Excel Training workshop.

How to Write a VBA Sub Procedure (Macro) That Controls Excel

Microsoft VBA Excel Tip

Microsoft Excel comes standard with a programming language called VBA or Visual Basic for Applications. This programming language, commonly referred to as macros, is what you use to automate Microsoft Excel with. Individuals will also call this language VB script, VB and VBA macros to name a few. If you here the words VB.Net, that is VBA's big brother. The power of coupling VBA and Excel together can really not be overstated. Constantly when EMAGENIT puts the two together, they regularly out perform tools costing hundreds of thousands of dollars to buy and maintain. Add to that the fact that Excel has already been developed and you can tap into that pre-built engine and graphics, drastically reduces project build time and cost. And flat out the Excel-VBA combination can do things that you just can not do in .Net (VB, C Sharp...) and if you can, the development time is horrendous. All that said, EMAGENIT uses all the tools just mentioned to develop with and they are all excellent in their own areas, we just find a lot of times that the Excel VBA combination for data processing and dashboard design is hard to beat and comes through again and again. And if you do need the capabilities of the .Net environments, you can just run them from VBA (topic for much, much, much later down the road but now at least you know it can be done so you can plan).

So after that introduction, how do you write a VBA procedure that commands Excel. Well first have only one workbook open in Excel, having multiple workbooks open when learning how to use VBA can be quite confusing. Next press Alt+F11 to activate the Visual Basic Editor. In the Visual Basic Editor, proceed to the View menu and select View/Project Explorer . The Project Explorer window will appear by default on the left-hand side of the VBA Editor window.

Calculation Mode

In the VBA Editor, proceed to the View menu and select View/Project Explorer.

In the Explorer window, find your workbook's VBA project, look in the ()'s for the name of your workbook, and click within the project's tree elements. This step identifies the project where you want to insert the module we will insert and is necessary if you have multiple workbooks open. Just a note here, when you create a workbook, the VBA project is already part of the workbook file. It is made for you automatically. For any one coming from another programming environment let me save you the time, Excel VBA does not allow you to create any other type of project but the one seen. Next proceed to the Editor menu and select Insert/Module. This will insert a module in the Editor window on the right-hand side of the Project Explorer window. The module you just inserted is where you will type your VBA code. Think of it as a Word document and you will use the same basic typing skills to type in it that you do in Microsoft Word. There are two other types of modules in Excel VBA to use, but do not worry about those right now as you will not need them for a long time. The module you just inserted, think of it as the town square where everybody can easily communicate without any hassles. This type module will be your workhorse in Excel VBA.

Calculation Mode

In the Explorer window, find your project and click anywhere within its tree elements. Next proceed to the Editor menu and select Insert/Module.

The inserted module will appear in the Explorer tree under the Modules folder. If you do not see the folder, click the orange/yellow Folder button at the top of the Explorer window. To control the module, double click on its module icon in the Explorer window to view it and click on its (x) close button in the upper right hand corner of its window to close it. You always want your module window maximized for viewing purposes so if you see it in a window format, double click on its caption bar to maximize it. You can also click its window maximize button also. One last thing, when you save the workbook, you save the module code. When you open the workbook, the code is opened. To save your workbook, go back to Excel and save the workbook as an Excel Macro-Enabled Workbook file type or you will strip the code out of the workbook when it is closed. You do not have to save a module before closing it as it does not effect the code. To toggle between Excel and the Editor, use the buttons on the Windows task bar at the bottom of Windows. One other thing, when you develop a VBA program in Excel, you pick a workbook to store all the code in you will use. The code does not necessarily have to be in the same workbook as information you will be accessing. In our example here, the data we will use will be in the same workbook as the data.

Calculation Mode

To activate a module, double click on its module icon in the Editor window and close it by clicking its Close button.

Next click in the module window. You must create a procedure, commonly referred to as a macro, to command Excel. All of the code you type will go inside of a procedure. The basic exceptions are what they call module level declarations and comments. You will not be worrying about module declarations right now.  Comments are notes to yourself about what your code is doing, you will learn to document your code using comments as they help a great deal in figuring out what your code is doing when revisiting it 3 months down the road. They always begin with an ' apostrophe and turn green when done typing.

There are two basic types of procedures you will worry about at this stage, Sub procedures and Function procedures. Sub procedures just run and do a series of tasks and calculations then end while Function procedures are designed to do a series of tasks and calculations and return a value back. Just think of a worksheet function and you have the general principle down. As a general rule when first starting out, you will write a Sub procedure to command Excel and then branch out from there. So in the module type: Sub copypaste and press Enter. A pair of ()'s will appear after copypaste and End Sub will also appear. Click before End Sub and hit Enter about 5 times and give yourself some room to type. The name copypaste is the name of the procedure, that is how they are tracked, and as a general rule, do not repeat procedure names in your project. The names you type in a program can basically contain alphanumeric characters and an _ underscore and that is about it. Do not put spaces in your names and they can be not be longer than 255 characters. As you type, you will notice words that turn blue. These are reserved keywords in VBA and do not use them for names. VBA is not a case sensitive language when typing names although the logic structures your write in it are when comparing text.

Calculation Mode

Type Sub copypaste and press Enter.

When commanding any program, they always have a hierarchy to them. To visualize this, think of a program as a building with floor levels and a stair case. Your job is to find what floor level does the job for you in the building, walk down the staircase to it, stop, and get somebody on the floor to do your task. To command a program in general, you always start at the top floor level of the building and go down. This level structure concept in your code is represented by a series of commands separated periods. Each command walks down to a new level of the program till you reach your target level, then the last command typed is what you are doing. So the first thing you have to figure out when commanding a program is what level controls what you want to do. The next task is to figure out is how to get there. So in our problem here, we are going to copy and paste a value from one worksheet to another. So in our case the first thing we figure out is what Excel program level controls this functionality which is the Range level. Note that the levels you go through in a program will be tracked mostly by name so be sure to insert two worksheets in your workbook and name them Data and Output. Data will contain the data to copy and output is where we will paste it to. Next on the Data sheet type 100 in cell A1 and press Enter.

Calculation Mode

Make two worksheets and name them Data and Output. On the Data sheet in cell A1 type 100.

Proceed back in the Editor (Windows task bar or Alt+F11 again) and between the Sub and End Sub type:

Application.ThisWorkbook.Worksheets.Item("Data").Range("A1").Copy
Application.ThisWorkbook.Worksheets.Item("Output").Range("B5").PasteSpecial

You can see the code stepping down through the structure of the program, Application is the top level of the program, Range is our target and after the last period is what we are doing, copying and pasting. Generally one line of code represents an operation in Excel so in order to copy and paste we needed two lines of code. They tend to call the Command.Command.Command leading down to the Copy and PasteSpecial the "path" or hierarchy. As stated earlier, your VBA code will generally not be housed in the same workbook as your data. As matter of fact, the workbook that houses the VBA code is really not important in theory. What is important are the paths you create and how they target the different parts of Excel. It is all in the path so remember that one key rule. Also when creating complex data systems like most of you want to create, you are going to be opening and closing hundreds of workbooks, generating new report workbooks, charts... so you will always lock on to your program elements you want to command by using full paths. You will read that you can use sometimes drop part of the path due to some Excel element being active, this is the kiss of death to a data processing system as you will loss track of what you are trying to command very quickly. And remember there are techniques that you can use that scan for data workbooks that you do not know the name of and techniques that can rip through thousands of workbook files in folders. Learn first how to command things that are in the workbook that house your code, then learn the techniques to lock on to information external to the code workbook.

Now to run the code, click anywhere inside the boundaries of the procedure, press the F8 key and keep pressing it till the yellow line disappears after End Sub. This is called Break Mode and helps you step through the code line by line and debug it. If you press the F5 key, it will just run the code without stepping through it. On the Debug menu, you can see the debugging commands and the shortcut keys that activate them. You will need to learn how to debug before writing any programs. It is the most important skill to learn when programming. One note, you cannot run any code till you correct the code lines that are red. These are called syntax errors and you must correct them. Generally what happens is you left off a . or , or " or (. If you are pressing F8 and a dialog box appears, this is called a run time error and can mean a couple of basic things. First a command is out of sequence in the ...., the name inside the ""' is wrong, you misspelled the command or it try's to do something that cannot be done. The last type of error encountered is called a logical error. It means the program is doing something, but not what you want it do. These are the hardest to track. Therefore write your code in small blocks, test and debug constantly and be patient.

Calculation Mode

Type the code in between the Sub and End Sub, click inside the procedure and press the F8 key to step through the code running it and debugging it.

Now the terminology transfer. When reading about controlling programs, most modern ones are developed as a series of Class modules. These classes contain procedures in them just like what we typed. When the program powers these classes up, they create what are called objects (what we called the levels of the program earlier). The classes are their blueprints. So when you hear the word object think Class module. If you know the class and its procedures, you know how to control the objects made from them. Also the words properties and methods, they are categories for the procedures in the classes. They also call class procedures members. The commands we just typed in the Command.Command.Command structure are actually procedures from these classes. Typing code boils down to knowing the syntax, the grammar of what to type. This where you will spend your time and lots of it. Your computer will develop nick names during this time period that cannot be stated on this website without parental warning. Just make sure nobody hears you when scolding your computer about how it has let you down yet again. Just joking. Anyways if you know what type of procedure in a class you are going after, you know how to type the code because there are rules for typing each procedure type in your code. Learn these rules and you will know how to type the code that controls the program. The classes and their procedures that make up Excel are found under View/Object Browser.

Last you will also want to learn about variables, logic and loops. These are the essentials of a program and allow it to operate correctly and perform amazing tasks. Logic provides the brains for the program and give the ability to make decisions. Variables are used to store/retrieve information in the program while it is running. Loops are the engine of your code that you write whenever you have more than one item to go through. For example, if you have 500 files to go through in a folder, it will be a loop. If you have to process 50,000 lines of worksheet rows and cells, it will be a loop. Learn these structures well as they form the core of advanced programs.

Last to make a button to run your Sub, proceed to a worksheet, right mouse click on a picture or drawing shape and select Assign Macro... On the Macros dialog box that appears, select the Sub procedure name to assign in the center box and click OK. Click off the picture or shape to activate the button and click back on it again to run the procedure. To edit the button, right mouse click over the shape to deactivate the procedure run and then edit. Phew signing off!!!

To learn more about Excel VBA, check out our Beginning Excel VBA for (Engineering and Science, Business) workshops.

How to Turn Off/On Microsoft Excel's Formula Calculation Mode

Microsoft Excel Tip

When using very large Microsoft Excel workbooks that contain thousands of formulas, it will sometimes take these formulas a long time to calculate when their values are changed. Also very large formulas containing lots of nested levels will also take a long time to calculate when their information is changed as well. To prevent a time lag every time you change the worksheet values that these formulas use, you can turn set the calculation mode of Excel to off so formulas will not update every time you punch in data or change their information. At the very end of your editing tasks, you can then force Microsoft Excel to calculate in one big calculation sequence thus saving you time.

To turn Excel's calculation mode off, proceed to the Ribbon and select Formulas/Calculation Options/Manual. This will set Excel's calculation mode to off.

Calculation Mode

In the Ribbon, select Formulas/Calculation Options/Manual to set Excel's calculation mode to off.

Once shut down, if you change information that effects a formula, you will be flagged in the bottom left-hand corner of the Excel window (called the Status bar) with a Calculate button. This will indicate to you that you need to force Excel to calculate the workbook once you are done typing. To force Excel to recalculate the workbook, you can click this button or press the F9 key. You can also use the Calculate Now button on the Formulas tab in the Ribbon to accomplish the same thing. Note that if you wish to calculate only the worksheet you are on and not the rest of the workbook, you can press Shift+F9.

Recalculate

When the Calculate button appears in the Status bar, click it or press F9 to force Excel to recalculate the workbook.

To turn Excel's calculation mode back on, proceed to the Ribbon and select Formulas/Calculation Options/Automatic. To read more about Excel's calculation modes, consult online help by pressing the F1 key.

To learn more about the features like this, check out our Intermediate Microsoft Excel workshop.

The Best Way to Hide a Microsoft Excel Worksheet (Better Than Hide)

Microsoft Excel VBA Topic

Sometimes the need arises to really hide a Microsoft Excel worksheet from the view of a pesky user. Anybody who spends time in Microsoft Excel already knows about right-mouse clicking over a worksheet tab and selecting Hide. To unhide, you just right-mouse click over a tab, select Unhide then the desired worksheet in the dialog box. This feature went out about 20 seconds after it was made. To really hide a worksheet, so it cannot be unhidden through that box or even show up in that box, you can use the VBA Editor. That's right the place where you type Excel VBA code and strikes fear in the hearts of mortals, but you will not be typing any code, just using a real simple feature in it. To proceed, first make sure the worksheet you want to hide is completed, it is a pain in the neck to keep unhiding and hiding again using this method. Next press the Alt+F11 keys to display the Microsoft Excel VBA Editor. This is where you view the Excel VBA code (also called macros) that people write. Press both keys in succession quickly because if you hold down the Alt key too long before pressing F11, it will just show the shortcut keys for the Ribbon in little black boxes. It they appear, just click on an Excel cell with your mouse and do it again.

When the Editor appears, first find the View menu and click it, then select the Project Explorer and Properties Window items on the menu.

Visual Basic Editor View Menu

In the VBA Editor window, select the View menu then Project Explorer and Properties Window items.

The selected windows should appear on the left-hand side of the Editor stacked on top of each other. Find the Project window (looks like a tree) and at the top click the yellow/orange Folder button so it is pressed in. This places that window in a folder layout, easier to navigate this way. Next in the Project window find the project that belongs to your workbook. This is easily done, look for the word VBAProject in the window and look in the ()'s next to it. In the ()'s will be the name of your workbook like VBAProject(Book1). If the project is not expanded, click the + symbols next to the project to expand it, it works like Windows explorer.

Project Explorer Window

In the VBA Editor window, click the orange Folder button at the top of the window to see the folders for the project. Find the project associated with your workbook and expand its tree.

In the Project window, look for your worksheet under the Microsoft Excel Objects folder. Again you will be looking for the name inside the ()'s. For example, Sheet2(Sheet2) where Sheet2 inside the ()'s is the name of the sheet are looking for. If you have 50 sheets, scan the list carefully and be patient. Once found, click on the sheet icon, it will turn blue then look in the Properties window, the grid looking window below the Project window, and proceed to the very bottom of it and find Visible. Click on the box and select xlSheetVeryHidden. Don't ask me about the name, I only work here. It is kind of like going to 11 on your guitar amp when everybody else has 10 (quote from Spinal Tap). The worksheet is now hidden from the 95% of Excel users who do not know this trick. It will not show up in the Unhide box and the workbook scans under File cannot unhide it or delete it. A point, you must have more than one sheet in the workbook to hide a sheet. To unhide, just repeat the procedure and select xlSheetVisible from the drop down.

Properties Window

In the Project window, click on the worksheet icon that represents your worksheet, proceed to the Properties window, click on Visible and select xlSheetVeryHidden.

To learn more about Excel VBA, check out our Beginning Excel VBA workshops for Business or Engineers and Scientists

How to Debug and Error Check an Excel Nested Formula One Step at a Time

Microsoft Excel Tip

Microsoft Excel formulas involving nested calculations are generally very difficult to debug due to their confusing structure. All those ()'s. The rule of thumb is the inner calculations of a nested formula calculate first then the outer ones, but this calculation sequence is not always apparent especially when dealing with nested function structures that reach 3 or more levels. Microsoft Excel has a tool, however, that lets you evaluate a nested formula one part at a time. It is called the Evaluate Function tool. Using this tool, you can see the different parts of a nested formula calculate in the order it calculates which greatly aides in the debug process.

To use this tool, select the cell with the nested formula, proceed to the Ribbon and select Formula/Evaluate Formula (in the Formula Auditing group on the right-hand side). When the tool appears, the underlined calculation in the Evaluation box is the next calculation to be evaluated. When the Evaluate button is clicked, that part will calculate and display a result. Keep clicking Evaluate and watch the box and the underlined sections. Note that clicking the Step Into button just gives a more detailed account of how the underlined section calculates (when it is enabled). If clicking this button, be sure click Step Out to get back to the main screen. Overall a very useful tool when calculating nested formula structures.

Nested Formula

Simple nested formula to be evaluated, switches between two tables using IF, VLOOKUP and TRIM.

Evaluate Formula

Click on a cell containing a nested formula and select Formulas/Evaluate Formula to watch pieces calculate.

Evaluate Formula Dialog Box

Click Evaluate or Step Into (when enabled) and watch the underlined calculation in the Evaluation box calculate.

If you click on the Error Checking button in the Ribbon, it is the same basic tool as just discussed but will lock onto all cells containing errors in their formulas and will show you the part that errors out. Just click the Show Calculation Steps button to see the formula calculate. You will click the Next button to move to the next formula with an error. Very cool for large worksheets with thousands of rows and errors you might not know about. Good quick way to see worksheet is calculating correctly. No cell selection necessary before using the tool. Note that if you click the Ignore Error button and wish to error check the formula again later, proceed to the Excel Options screen, generally File/Options, and on the Formula tab click Reset Ignored Errors to debug.

Error Checking

Click the Show Calculation Steps to watch the formula error in the Evaluation Formula tool. Click the Next button to move on to the next error.

To learn more about capabilities like these, check out our Intermediate Microsoft Excel workshop.

LinkedIn
Facebook
Information
Need Help? Please call us at 1.866.924.6244

Copyright © 2002-2017

EMAGENIT All Rights Reserved