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 Use the Formula Auditing Tool to Modify/Debug Your Excel Worksheet

Microsoft Excel Tip

When handed a legacy workbook in Microsoft Excel that you did not build and are now asked to modify/debug, how do you figure out how the workbook is put together with formulas. The answer is to use the Formula Auditing tools. The Formula Auditing tool allows you to see if a cell is used in a formula in another cell (i.e. is it an input) and can also look at a formula and tell you all the cells used as inputs to it. To use this tool, click on the Formulas tab, look to the right on the tab and find the Formula Auditing group.

To find if a cell is being used in another formula, first click on the cell then click Trace Dependents. A blue arrow will point to the cell it is being used in if it is on the same page. If you keep clicking the Trace Dependents button, you can see the flow of that one cell through all the formulas it directly impacts if changed.

Trace Dependents

Click on a cell and select Formulas/Trace Dependents.

If the cell is used in a formula on another worksheet, it will draw a black arrow pointing to a worksheet icon. If you double click on the black line, the GOTO box will pop up showing the cells on all the different worksheets whose formulas that the cell impacts. If you double click on a cell reference in the box, it will jump you to the worksheet/cell.

Goto Dialog Box

Double click on the black dotted line to see cells on other worksheets.

If you click on a cell with a formula and select Formulas/Trace Precedents, then blue arrows will appear showing all the cells that were used in the formula. The arrow will draw itself like with Trace Precedents except blue dots will now be displayed on all cells used in the formula if on the same worksheet. If cells used in the formula were from another worksheet, the black dotted line will also appear again and may be manipulated as previously discussed.

Trace Precedents

Click on a cell and select Formulas/Trace Precedents.

To remove all the arrows on the worksheet, select Formulas/Remove Arrows.

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

How to Create a Drop Down List in a Microsoft Excel Worksheet Cell

Microsoft Excel Tip

When creating an interface that a user will use, they will usually have to type in a specific name like a part, product, y or n...etc. This leads to the next part which is they will mess it up through abbreviations, not knowing the proper names, guessing...whatever strikes their fancy. When creating dashboards, VBA programs, forms that will upload into databases, having inconsistent information is the number one reason why they will fail. To counter this problem, it is easy to create a drop down in a cell that controls what a user inputs into a cell. To create a drop down list, first type a vertical list of names in a column on a worksheet. Next, highlight the range containing the names, proceed to the Name box (direct left of the formula bar, should have a cell reference in it), click in the box and then type a name for the list. Be sure to press Enter or it will not be assigned. Names can be alpha numeric, no spaces, you can use underscores and do not use cell reference names. In general, also stay away from sheet names or anything else you have named.

Validation List

Create a vertical list and name it in the Name box.

Next, select the cell to put the drop down in, proceed to the Ribbon and select Data/Data Validation. On the Data Validation dialog box, select the Settings tab, select List in the Allow drop down, click in the Source box and type =ListName where ListName is the name you created for the range. Click the OK button to finish and when the cell is selected, a drop down arrow will appear. You can manage your cell and range names using Formulas/Name Manager. Do not use the Name box to edit your name once it is created. That is what Name Manager is for, it edits the name and range that was referenced. Hot tip, create a Setup sheet to store these lists keeping them away from users like in this example. Do not put them on the same sheet as the drop down. Stack them across the Setup sheet starting in row 1 keeping a blank column in between the columns with info. And by the way, you can name single cells, large tables this way and use the names directly in your formulas and functions instead of the references. No more Sheet2!...... The only way to build large scale models in Excel.

Validation Dialog Box

Select cell, activate Data Validation, set List option and type =ListName.

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

How to Perform a Math Operation on an Excel Worksheet Column While Pasting

Microsoft Excel Tip

When copying and pasting, many times you would like to quickly perform a simple math operation on a column of numbers without making extra formulas or retyping them. To accomplish this task, first type a list of values to perform the math operation with in a range of cells equal in size to the column of numbers being copied and pasted. Next copy the column of cells to perform the math operation on by pressing Ctrl+C. Right-mouse click over the first destination cell of the numbers you typed and select Paste Special. On the Paste Special dialog box, select the desired paste filter, select the math operation and click OK. If you want to use just the values from the copied column, select the Values option. The math operation will be performed on the values in a one to one fashion. If Values is not selected, this operation will not remove formulas when being performed. This means that if formulas where used in the column being copied, they will appear in the column with the extra math operation tacked on when pasted. Relative references will shift so be aware of that. Formats will transfer as well. That is why the Paste Special dialog box comes in handy here because you can filter these out if unwanted.

Copy Paste Column

Create New Number Column (2016), Copy Original Column (2015), Paste Special on First Destination Cell

Copy Paste Column

Set Paste Filters, Select Math Operation and Click OK

How to Clean Your Microsoft Excel Worksheet Text Data

Microsoft Excel Tip

When comparing text in different tables, looking for headers in a row and various other text tasks in Microsoft Excel, your data has to be pristine (uncorrupted) in order to work with. Microsoft Excel's various worksheet functions like MATCH, VLOOKUP, HLOOKUP, COUNTIF, SUMIF... depend upon your data having consistent characters. Just a tip, consistency in names is the number one killer in databases especially with multiple databases. The three heavy hitters that mess up your data are non-printable ASCIIs, spaces and rogue characters that are not supposed to be there like */().,- and so forth. There are three Microsoft Excel worksheet functions that can be used to deal with these problems. They are CLEAN, TRIM and SUBSITUTE. CLEAN gets rid of the non-printable ASCIIs. Non-printable ASCIIs occur after text in cells when converting text between different computer languages, operating systems and with programs that purposely output corrupted tables so you cannot move their data. They range between 0-31 of the ASCII table, you cannot see them, but they are there. When developing data crunching software for our customers, we encounter them about 60% of the time at some point in the project. The TRIM function removes leading and trailing edge spaces around text and standardizes the spacing between text to one space. Spaces by the way are the number one killer for a data program so beware of them as they effect everything text especially downloaded tables from databases. Substitute will get rid of any character in a piece of text without having to know its location which is a royal pain to figure out. You could use REPLACE but that requires you to know the position and number of characters which again is a royal pain. And if you cannot type the text in SUBSTITUTE to lookup, then use the CHAR function and the appropriate ASCII number for its second argument to generate the character that it needs. For example, you cannot type in " between two "" but you could use CHAR(34) to generate it.  Also with SUBSTITUTE, it is case sensitive so use LOWER to lower case your text being analyzed so you do not miss anything. UPPER and PROPER can be used to return the text back to its original format once the operation is completed. So here they are the way we use them, if you think you have a corrupted column of data or row, insert a blank one right next it, run these functions to clean the data, then do a paste special and copy and paste the values back over the originals and get rid of the extra column you just made. By the way, keep nesting the SUBSITUTE function for each rogue character you want to get rid of. You must know your data in order for you to get rid of the right characters. Also there are many variations of using these functions together.

A B C
Names
2 Tom/ =PROPER( SUBSTITUTE( LOWER( A2 ) , "/" , "" ) )  
3 David   =TRIM(A3)  
4 Mary =TRIM( CLEAN(A4) )  
5 Jim*/ SUBSTITUTE( SUBSTITUTE( A5 , "/" , "" ) ) , "*" , "")  

SUBSTITUTE, TRIM and CLEAN Formula Examples

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

Rotate Text in an Excel Worksheet Cell / Worksheet Cell Drag and Fill

Microsoft Excel Tip

When formatting worksheets, there are two things that we are always asked. First how do you fit a row of headers( dates, text...) on a worksheet so that a good chunk can be seen by your user at any one time and it looks cool. Typing a date, for example, in a cell takes up a lot real estate, especially when typing them horizontally, so the magic question is how to fit them. One effective way to do this is to rotate the text in the cell so it goes vertically oriented. To do this is quite easy, first highlight the cells to rotate, Ctrl+Shift+ Arrow Key (left,right,up,down) allows you to rapidly select a range, next right-mouse click over the selection, select Format Cells then select the Alignment tab on the dialog box. Next in the upper right hand corner of the Alignment tab, find the Orientation controls and click and hold on the line inside the box that looks like Text----------. Drag it to what ever angle you want and the text in the cell will orient itself to that angle. Next highlight the columns on the worksheet that the range occupies and adjust their width by clicking and holding on a border between one of the headers and dragging. To make it look cool, last apply liberal shades of color gradients (Format Cells/Fill/Fill Effects) and gray borders (Format Cells/Borders) and take out the gridlines and headers (Page Layout/Sheet Options/Gridlines) and you start to have something. If it doesn't look cool then why do it right? Also never under estimate the power of formatting and graphics in their ability to buy an extra week or two on a project deadline. These ideas are represented in the illustrations below. The project deadline is the truth.

Rotate Cells

Rotate Cell Text

Header Columns

Select Header Columns and Move Borders

Color Gradients

Apply Liberal Amounts of Color Gradients and Gray Borders

Second is how to rapidly fill down the days of the week on a worksheet so you can group them together like Mon-Fri and leave a blank row between each grouping. First pick where you want to start on the worksheet and type Mon or Monday. Next click on the cell, at the bottom right-hand corner of the cell find the black dot, click and hold on it and drag it down the sheet till you see Fri or Friday. Next highlight the whole range containing Mon-Fri and select an extra blank row for the spacing. Next click and hold on the little black dot in the bottom right-hand corner, hold down the Ctrl key and drag. Next watch the little icon to the right of your mouse cursor as you drag, it will display a name, and stop when you reach Friday keeping tack of how many times you passed Friday for each grouping. Release the mouse button first then the Ctrl key and you will see you have copied the cells over and over instead of filling in sequence like above. This little Ctrl key maneuver works great with numbers, pure dates, anything you want to copy over and over and we cannot tell you how many times we have used it. These concepts in general are presented below.

Drag and Fill

Drag and Fill Holding Down the Ctrl Key

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

How to Transpose an Excel Worksheet Row into a Worksheet Column

Microsoft Excel Tip

Many times when copying and pasting, you would like to take a row of worksheet data and turn it into a column or vice versa. To accomplish this task, copy the row or column on your worksheet by pressing Ctrl+C. Next, proceed to the cell to paste to, right-mouse click and select Paste Special (Ctrl+Alt+V). On the dialog box, check the Transpose box and click OK. Do not forget to select how you want to paste the information like values only, formats and so forth. Very useful when transferring information between different orientation tables. These concepts are illustrated below.

Transpose Data

Paste Special Example

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

Subtotal Worksheet Function

Microsoft Excel Tip

In everyday Microsoft Excel worksheet usage, everybody using worksheet functions has encountered the problems of hidden rows getting in the way of summations, max and min calculations....etc. Microsoft Excel, however, has a worksheet function called SUBTOTAL which has the capability to ignore hidden rows. This function can be utilized for such basic calculations as SUM, MAX, MIN, AVERAGE...etc. This function takes at least two arguments, the first one specifies what type of calculation you want to do and if you want to skip blanks via code numbers, the second one and onward specify the ranges, formulas, numbers... that you want to calculate. For example for the first argument, a 9 would mean SUM and a 109 would mean SUM but skip hidden rows. For the second argument and onward, you could use A1:A10, 1 or A1+A2, basically anything that calculates. The SUBTOTAL function pictured below demonstrates these concepts.

A B C
1 1 4  
2 2 5  
5 3 6  
      = SUBTOTAL( 109, A1:A5, B1:B5 )

SUBTOTAL Formula Example

This function is also of great use when using the Excel data filtering tools. To learn more about how to use functions like these, check out our Intermediate Microsoft Excel workshop.

Where to Learn SQL (Microsoft Excel Data Tools)

Microsoft Excel Tip

SQL is a programming language designed to manage data in a database. It provides the power to process massive amounts of data with just knowing some simple basics. Some of Excel's data tools on the Data tab in the Ribbon like Microsoft Query and the Data Connection wizard (under the From Other Sources button) use SQL to talk to different data sources like workbooks, text files and databases. Think Autofilter on steroids. So in other words, you can query (i.e. filter) a text file or workbook file just like a database. To learn about SQL, go out to Bing and type in SQL clauses. A clause is a command in SQL. Read the links that appear. EMAGENIT recommends the following link www.sqlclauses.com. Basic SQL is not hard to learn and you will soon realize has a lot of uses in Microsoft Excel and beyond. If you have to import a massive number of rows and columns into Excel and filter them, then SQL along with the data tools mentioned above on the data tab are the way to go.

Microsoft Query and Data Connection Launch Example

Microsoft Query and Data

How to Merge Cells on a Microsoft Excel Worksheet (Polished Form Construction)

Microsoft Excel Tip

A form in Microsoft Excel is an organized group of cells that displays information or has information typed in it. On these forms, information rarely fits into a single standard width cell. In other words, it overflows chopping off text and covering up other cells. The common solution to this problem is to make the cell width wider (i.e. the column), but the problem is the width you set for that cell messes up the widths of other cells in the same column making the look of those cells unprofessional. The solution to the problem is to merge two or more cells together to make a larger viewing area. This allows fields of varying widths to be developed that can be lined up allowing for a very polished look. To merge two or more cells together, highlight the cells then right-mouse click over the selection and select Format Cells. On the Format Cells dialog box, select the Alignment tab then check the Merge Cells check box. Note that you can also use the Merge... button on the Home tab of the Ribbon for merging. It is found in the Alignment section of that tab as illustrated below. Remember to also align the merged area's horizontal and vertical alignment, vertical top, horizontal left usually works very nicely. These features can also be accessed on the Alignment tab or on the Home tab of the Ribbon. Also if the merged area is to be used in a formula, the cell reference to the merged area is always the top left hand cell on the merged area. So in other words if you merged cells A1 through B1 the cell reference to the area would be A1. These concepts are illustrated below.

Simple Merged Cells Example

Simple Merged Cells Example

Using an Excel Array Formula to Perform Multiple Calculations at Once

Microsoft Excel Tip

In a Microsoft Excel formula, we are all used to creating basic formulas using single cell references. For example, if you wanted to add the values in cells A1 and B1, you would create a formula in a cell that would look like =A1 + B1 to add those values together. Now lets say you had values in cells A1, A2 and A3 and you would like to multiply those values by the values in cells B1, B2 and B3 and then sum them. You would probably build something like what is pictured below. You would build individual formulas for each row and then sum the result below with the SUM function which takes up real estate on your worksheet.

A B C
1 1 4 =A1 * B1
2 2 5 =A2 * B2
3 3 6 =A3 * B3
      = SUM( C1:C3 )

Simple Worksheet Calculation Example

Microsoft Excel, however, allows you to build formulas that also use multiple values at once. These formulas have a special name, they are called array formulas. Array formulas in Microsoft Excel use ranges among other things as a means to get their multiple values, for example like A1:A3. The array formulas calculate information and return a group of values based upon the configuration of the ranges given it. For example, if given two columns of information to perform an operation on, the result would be another column of equal size. Simple array formulas can be used for a lot of things in calculations but are best used as the guts to worksheet functions that state they take an array as an argument like the SUM function. So the example above could be rewritten as =SUM( A1:A3 * B1:B3 ) as pictured below and it would obtain the same result which is 32.

A B C
1 1 4  
2 2 5  
3 3 6  
    =SUM( A1:A3 * B1:B3 )  

Array Formula Example

There are some special rules to using this type of formula however. First, make sure if you are doing operations with two columns, that the columns have the same number of rows. When working with rows, make sure the rows have the same number of columns. For example, A1:A3 * B1:B3 or A1:D1 * A2:D2.  To explore other configurations and how they calculate, say a row x column, use small ranges, build just the simple array operation like = A1:A3 * B1:D1, and then highlight the formula and hit F9. It will display how the math is performed for the configuration given. If you notice commas in the answer and semi colons, these symbols designate column separators and row separators.
Next to enter an array formula in a cell, you cannot just press Enter, you must press Ctrl+Shift+Enter at once to enter the formula or it will not produce the desired result, maybe even an error. When editing the formula, you must press the same key combination to re-enter the formula when done editing. While the SUM formula just presented returns a single value, array formulas can also produce multi-value answers that can be returned and viewed in multiple worksheet cells. They can also be used in some outrageous logic formulas. That is the subject of next week's article.

Again this is just the tip of the iceberg with these formulas, to learn more about how to use these formulas, check out our Advanced Microsoft Excel workshop of Microsoft Excel Dashboards workshop. You can also use Bing and search the net for Excel Array formulas.

How to Copy and Paste an Excel Worksheet Formula without Changing Its Relative Cell References

Microsoft Excel Tip

Many times you would like to copy a formula from one cell to another without shifting its relative cell references. This generally occurs when you would like to use a piece of it or it just has to be modified slightly for a new purpose. To accomplish this, first select the cell containing the formula and press the F2 key. This will put the formula into Edit mode. Next press the Shift+Home keys to select the entire formula then press Ctrl+C to copy it. After copying the formula hit the Esc key to cancel the formula edit. Last select the cell to copy to and press Ctrl+V.

Checking for the Existence of an Excel Worksheet Cell Value in Another Worksheet

Microsoft Excel Tip

One of the many tasks facing individuals in the workplace today is testing for the existence of an item in a worksheet table that is in another worksheet table. There are a lot of different ways to do this, however, the most straight forward way is to use the COUNTIF worksheet function. In the world of logic, when you want to test for the existence of something say in a program or in a formula, the most effective way to do it is to simply find some way to count what you are after. The COUNTIF function does this very thing by looking for the existence of a criterion you give it in a worksheet range, a name in this instance, then counting how many times it occurs. So for example, you have a master list in one table and you would like to see if the name dropped of in a report table that just arrived. Then you would build the COUNTIF worksheet function to look down the range of the new table that has the names in it using the name from your master table and if it returns >0 you know it exists. This concept is illustrated below using the two simple tables in columns A and D, E. When looking for text be very careful because spaces and unwanted characters in the text can corrupt the result. It is highly suggested before running the comparison that you build an auxiliary column next to the column you want to look down in the report and run the CLEAN and TRIM function to get rid of leading and training edge spaces in the data and to also clean non-printable ASCII characters in the text also. Do not underestimate this problem as it occurs in about 80% of the projects that EMAGENIT builds. The formula would look like this =TRIM(CLEAN(A1)) if the data you were after was in column A. If you have other rogue characters in the data it is also suggested that you use a function like SUBSTITUTE to clean those unwanted characters as well. To get rid all spaces in a piece of data you can use this simple formula SUBSTITUE(A1, " ", "") which will target every space. This is just the tip of the iceberg when processing data but it should set you down the right path so you can investigate it further.

A B C D E
1 Item Existence Test   Item Qty
2 A = COUNTIF( $D$1:$D$100 , A2 )   A 100
3 B     B 200

COUNTIF Function Example

Working with Dates & Times on the Excel Worksheet

Microsoft Excel Tip

Knowing how to work with dates and times in Microsoft Excel is an essential skill to know when trying to analyze data or build dashboards on the worksheet. The first thing to know about dates is that they are not really dates, they are numbers. Excel stores information in worksheet cells two basic ways, as numbers or text. Excel calls the numbers that represent dates serial date numbers. So when you see a date in a cell it is really a format that makes the number look that way. You can see this by right mouse clicking on a date, then clicking on Format Cells then the Number Tab on the dialog box. You will see all different ways to change the look of a number on this tab but changing the look does not change the way the info is stored. So when you type in 7/15/2015 in a cell your are really typing in 42,200, but when you typed in the date format in a cell Excel automatically applied a date format and made the number look that way so it is transparent to you.
So what does the number stand for, well in Windows it stands for you are 42,199 days after 1/1/1900. The time portion of a date is recorded as a decimal number between 0 and .9999 with 0 being 12:00:00 AM, .25 being 6:00:00 AM and so forth. So if you have a number in a cell, 42,200.25, it represents a date/time of 7/15/2015 06:00:00 AM. The reason why this discussion is so vital is because a lot of worksheet functions look at date information on a worksheet as numbers. For example, if looking for a date in VLOOKUP's first argument, you are tempted to write 7/15/2015, but if you did that it would really be 7 divided by 15 divided by 2015, remember there are no dates. The forward slash is division in algebra. If you put quotes around it, "7/15/2015", then you are looking for a piece of text and VLOOKUP would not find the date unless it was stored in the worksheet as a piece of text. Luckily there are functions like DATEVALUE, DATE, TIMEVALUE and TIME that convert text and numbers into the proper serial date number that you then use in these functions. So to lookup a date in VLOOKUP you could use DATEVALUE("7/15/2015") as the first argument to VLOOKUP and it would transform the text into 42200 that VLOOKUP could then use. This concept is illustrated below. There are many ways to use these functions so please study them because they are vital to get to know how to use in dashboards, data processing...etc.

A B C
1     Hours Worked
2     = VLOOKUP( DATEVALUE("7/15/2015") , $E$1:$G$100, 2, FALSE)
3      

DATEVALUE Function Example

How to Rapidly Select a Range (Table) on an Excel Worksheet

Microsoft Excel Tip

If you want to rapidly select a range on a worksheet that is filled with data (i.e. table), click on the top left most cell of the table and press ctrl+shift+down-arrow. If you have blanks keep pressing the keys till the end is reached. If you go past the end, just press Ctrl+Shift+Up-Arrow. Then press ctrl+shift+right-arrow to select the columns in the table. This technique can be used for copying and pasting as well as when you are building formulas.

How to Perform String Concatenation in an Excel Formula

Microsoft Excel Tip

In advanced formula design, string concatenation helps you form everything from worksheet function argument inputs, to dynamic range references to output messages displayed in worksheet cells and drawing shapes. It has so many uses, you really can not describe all of them. It is one of the most important building blocks to know when trying to move up the ladder in formula design. Lets look at a common use of string concatenation that will help you right away. Take for example the VLOOKUP function and its first argument, this is the argument that is looked up in the first column of a table in a worksheet range. Suppose the value you want to use for this argument is actually split in two, part of it in cell A2 and part in cell B2. How would you combine the two to form the text to lookup. The answer is string concatenation. To actually build a concatenation in a formula is dirt simple. You use the & (Shift + 7 on your keyboard) symbol in your formula to splice the desired elements together. So the problem just discussed would look like this in a formula: = VLOOKUP(A2 & "-" & B2 & "*" , $E$1:$G$100, 2, FALSE)  where $E$1:$G$100 is the worksheet range you are looking in. It is pictured below. The next question is what are the quotes used for in the formula?. Quotes are used in a concatenation formula when you want to combine static text with other components that calculate. Both A2 and B2 will calculate producing their result and then get combined with the - and *. In many Excel worksheet functions like VLOOKUP, MATCH, SUMIF... if you include an asterisk in the text you are using, the result can be these three outcomes: Text* means begins with; *Text means ends with and *Text* means contains. That means if "112-ACE*" is used in the VLOOKUP problem, then you will return the first row that begins with the text 112-ACE. Our discussion here is only the tip of the iceberg with concatenation being used all over the place in Microsoft Excel formula design. Remember every time you see an & in a formula, they are using string concatenation to splice together something they are using. Our Intermediate Microsoft Excel, Advanced Microsoft Excel and Microsoft Excel Dashboards training can show you all the tricks for using string concatenation and more cool stuff.

A B C
1 Part ID Part Man Cost
2 112 ACE = VLOOKUP(A2 & "-" & B2 & "*" , $E$1:$G$100, 2, FALSE)
3      

String Concatenation Example

How to Control What a User Types in a Worksheet Cell

MicrosoftExcel Tip

In Microsoft Excel, the front line defense against your worksheet having errors in it is to control what the user types in it. In a program always remember, it is always better to trap the user on the front end while they are typing before anything has run then trying to clean up their mess after. To control how a user types in a cell, proceed to the Ribbon, click on the Data tab and select Data Validation. The button should be positioned on the right-hand side of the tab groupings on the Data tab. When the dialog box appears, click on the Settings tab and select what information the cell will contain in the Allow box. In the boxes that appear below, select the logic and type the limits that the cell will obey. These boxes will fluctuate in number based on what you select in the Allow box. The other two tabs specify the messages on the message boxes that pop up when the user clicks on the cell and/or makes a typing error. Always fill them in. Giving users tips when they click on a cell and/or error out is far more effective then providing them with an instruction manual. The Data Validation tool is not only useful in controlling worksheets with formulas, it is also fantastic at controlling what information goes into forms especially when Excel macros or other programs will be reading these forms. Remember what kills any data system, inconsistent data entry and people making up there own rules as they type. Our Intermediate Microsoft Excel, Advanced Microsoft Excel and Microsoft Excel Dashboards training can show you all the tricks to controlling this tool and more cool stuff.

How to Debug a Formula in a Microsoft Excel Worksheet Cell

Microsoft Excel Tip

When creating formulas, the subject matter is often overlooked of how to debug one. This is actually one of the most important skills to acquire because as your formulas become more complex, if you do not know how to debug them, then you cannot assemble them correctly. The primary debug skill to learn is to how to determine what parts of your formula are malfunctioning. Simply getting a #DIV/0! displayed in a cell is not much help in determining what went wrong. To determine what part of your formula is malfunctioning, first select the cell with the formula and press the F2 key. This will display the formula in the cell. Note that you can also view the formula in the formula bar directly above the worksheet without pressing F2 if using this method, click in the formula bar. Next highlight the part of the formula that you wan to observe. For example, you might highlight, B1 or B1+B2 or Sum(Z1:Z10), the trick is to highlight a complete mathematical expression which means you do not highlight just B1+ for example. Next press the F9 key which will calculate the highlighted part displaying the answer it generates. Once you are done viewing the part, press Ctrl+Z to get the original formula syntax back. Do not press Enter or you will erase part of the formula which is a very bad idea. Note that if you highlight more than one part of the formula and press F9 every time, only the very last operation can be reversed with Ctrl+Z. If this happens just press Esc to get out of edit mode for the formula without saving anything. If you do press Enter and the formula has been modified with F9 then hit the Ctrl+Z key to reverse the Enter. If you are 10 minutes past and you just realized your error after doing other things, say some choice words and dump the workbook without saving it. Trust me, you do not want to try to correct flaws where you can not quite remember what you did. Better to take the hit in time.  Our Intermediate Microsoft Excel, Advanced Microsoft Excel and Microsoft Excel Dashboards t training can show you all the tricks in formula construction and more cool stuff.

A B C
1 Price $19.95
2 Quantity 20  
3      
4 Cost = B1 + B2  
5      
6      

Highlight Part of the Formula and Press F9

Microsoft Excel Shortcut Keys

Microsoft Excel Topic

Shortcut keys (Ctrl+Z) are used in Microsoft Excel along with the Function keys (F2) to rapidly gain access to program features and tasks without having to use your mouse. Some businesses expect their employees to know these keys before getting a job with them. For your convenience, we have compiled a list of these shortcut keys for Microsoft Excel 2007, 2010 and 2013 in a friendly format. Best thing of all is they can be viewed on your phone. Click here to view them. Enjoy.

Proper Table Design on the Excel Worksheet

Microsoft Excel Tip

When building any type of Microsoft Excel project that will use data, the first thing to start with are the worksheet tables that will store the data. It is amazing how many times we have seen this overlooked in a project or even completely ignored. You do not proceed in a project period until you have designed all the data tables that the Excel project will use or know their configuration. Then you proceed with building your formulas and your interfaces that will read the tables and possibly control them using something like VBA. You want to spend a lot of time brainstorming what will be stored in the tables. You do not want to be half way through a project and realize you left something out. The redesign at that point is sure to impact a great deal of the project that has already been put together.

So how to layout a table on a Microsoft Excel worksheet. First start with the headers that your table will use. Every table that you make in Excel has to have headers even simple tables with one column of data. Excel's data tools and the formulas you write will use these headers. Brainstorming the headers gives you insight into what you will need in your project and impact dashboard design directly. Next make each header unique, no repeat names and make them a different format from the data like bold them, color their cells, color their font any one will do. Excel's data tools and many other programs that read Excel worksheets look for this distinction. Next make sure all headers go in a single cell along the top of the table. Never use merged cells in a table period and no infamous double row of headers. You are following database design rules here and they would never allow any of that. Next make sure under each header you put the same type of data, no mixing 1/1/2015 with See Dave* or N/A. Databases do not allow you to mix data types and you should not do it in your worksheet tables either. If you have to make a comment, make a new header and column for it. Next no complete blank rows or columns in your table. You can have blanks in the columns just not completely across or down. Many of Excel's data tools stop at breaks and if you plan on using VBA to control your tables, there is whole world of frustration waiting for you if you lace your tables with blank rows or columns. Note that if a header is at the top of your table and nothing is in it's column, it is still not blank because of the header so you are safe. Last, place only one table starting at cell A1 on the worksheet.  Do not stack and pack data tables on a worksheet. Your formulas will find this impossible to use and even VBA code is highly complex to write when you have this type of arrangement. If you have 30 data tables you need to put on worksheets then you will have 30 worksheets with data.

That is about it. If you follow these rules then you can start with a workbook as your data storage and then easily export it up to Microsoft Access, SQL Server... with the click of a button or a small VBA program. Our company uses Excel workbooks all the time to store data for companies and they operate perfectly well for years. A database is our last resort to apply to a problem because of the complexity and cost for our customers. We follow the K.I.S.S rule of design that has been proven time and time again to work very well. That said we do use databases a lot in our design work when necessary but every one we design is prototyped in an Excel workbook first. And by the way, if design a table on a worksheet with these rules, it can be queried like a database with SQL using a variety of different tools just like a database.

A B C
1 Region Quantity Sale Date
2 A 100 2/1/2015
3 B 200 3/1/2015
4 C 300 4/1/2015
5 D 400 5/1/2015
6 A 500 5/15/2015

Proper Table Design

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