# Microsoft Excel Tip of the Week Log - Page 2

Business, Engineering and Science

Business, Engineering and Science

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. They are updated every week so check back often.

Navigation:
[Page 1] [Page 2]

General Microsoft Excel Topic |
---|

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.
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.
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.
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. |

General Microsoft Excel Topic |
---|

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.
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, selectList 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.
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. |

General Microsoft Excel Topic |
---|

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.
Create New Number Column (2016), Copy Original Column (2015), Paste Special on First Destination Cell
Set Paste Filters, Select Math Operation and Click OK |

General Microsoft Excel Topic | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

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.
SUBSTITUTE, TRIM and CLEAN Formula Examples To learn more about capabilities like these, check out our Intermediate Microsoft Excel workshop. |

General Microsoft Excel Topic |
---|

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 Cell Text
Select Header Columns and Move Borders
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 Holding Down the Ctrl Key To learn more about capabilities like these, check out our Intermediate Microsoft Excel workshop. For our past Microsoft Excel Tips click here. For more free help visit our Microsoft Excel Help Topics page. |

General Microsoft Excel Topic |
---|

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.
Paste Special Example To learn more about capabilities like these, check out our Intermediate Microsoft Excel workshop. |

General Microsoft Excel Topic | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

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.
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. |

General Microsoft Excel Topic |
---|

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 |

General Microsoft Excel Topic |
---|

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 |

General Microsoft Excel Topic | ||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

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.
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.
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. |

General Microsoft Excel Topic |
---|

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. |

General Microsoft Excel Topic | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

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.
COUNTIF Function Example |

General Microsoft Excel Topic | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

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.
DATEVALUE Function Example |

General Microsoft Excel Topic |
---|

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 |

General Microsoft Excel Topic | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

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.
String Concatenation Example For our past Microsoft Excel Tips click here. |

General Microsoft Excel Topic |
---|

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. |

General Microsoft Excel Topic | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

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.
Highlight Part of the Formula and Press F9 |

General 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. |

General Microsoft Excel Topic | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

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.
Proper Table Design |

Copyright © 2002-2017

EMAGENIT All Rights Reserved