|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.
Ribbon Data/From Access
Select Data Source Dialog Box
Select Table Dialog Box
Import Data Dialog Box
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.
|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.
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 Dialog box
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.
|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 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?
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 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.
|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.
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.
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.
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.
|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 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.
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.
Select the Values Option to Avoid Pasting Formulas.
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.
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.
Use the Format Painter to Quickly Transfer Formats.
To learn more about Paste Special, check out our Intermediate Microsoft Excel Training.
|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.
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.
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 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.
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 the format for the cells that meet the criteria.
Colored cells meet the set condition.
To learn more about Conditional Formatting, check out our Intermediate Microsoft Excel Training.
|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.
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.
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.
Run Remove Duplicates to remove duplicate data.
To learn more about removing duplicates check out our Intermediate Microsoft Excel Training.
|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.
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.
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.
Using the Flash Fill button on the Data Tab to fill in data after typing a line.
|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.
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.
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 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.
|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).
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.
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.
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.
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.
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:
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.
|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
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
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
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.
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.
|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.
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.
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.
In the Project window, click on the worksheet icon that represents your worksheet, proceed to the Properties window, click on Visible and select xlSheetVeryHidden.
|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.
Simple nested formula to be evaluated, switches between two tables using IF, VLOOKUP and TRIM.
Click on a cell containing a nested formula and select Formulas/Evaluate Formula to watch pieces calculate.
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.
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.