Excel Tips & Tricks



Excel Tips and MacrosA compilation of Excel Tips over a period of 6-8 years. This is not an exhaustive list, just one that I have built over time. When I learn a new tip or process, I try to record that in this document.I hope this helps other people as it has helped me over the years.As always, any contributions are welcome!Table of Contents TOC \o "1-3" \h \z \u Excel Tips PAGEREF _Toc455480179 \h 4Find Specific Text in a Cell PAGEREF _Toc455480180 \h 4How to Extract the First Word of a String in Microsoft Excel PAGEREF _Toc455480181 \h 4How to Extract the Last Word of a String in Microsoft Excel PAGEREF _Toc455480182 \h 4How to Extract the First 2 Words of a String in Microsoft Excel PAGEREF _Toc455480183 \h 4Remove First Word in a String PAGEREF _Toc455480184 \h 4Remove Last Word in a String PAGEREF _Toc455480185 \h 4Pull String before a Certain Character PAGEREF _Toc455480186 \h 4Using a Formula to Fill Blank Cells with Value Above PAGEREF _Toc455480187 \h 5Add Hyperlink to Text Only and Not the Entire Cell in Excel 2010 PAGEREF _Toc455480188 \h 5How to Create a Hyperlink Inside of Excel Using the Value in another Cell PAGEREF _Toc455480189 \h 5How to Extract a File Name from a Path in Microsoft Excel PAGEREF _Toc455480190 \h 6Count the Number of Words in a Cell Or a Range Cells in Excel PAGEREF _Toc455480191 \h 6Counting the number of occurrences of a character in one cell PAGEREF _Toc455480192 \h 6Count the number of cells that contain text. PAGEREF _Toc455480193 \h 6Count the number of cells that contain star in any way. No matter what is before or after star, this function finds all the cells that contain star in any way. PAGEREF _Toc455480194 \h 6Count the number of cells that contain exactly star + a series of zero or more characters. An asterisk (*) matches a series of zero or more characters. PAGEREF _Toc455480195 \h 6Count the number of cells that contain exactly star + 1 character. A question mark (?) matches exactly one character. PAGEREF _Toc455480196 \h 6Count the number of cells that contain exactly star. PAGEREF _Toc455480197 \h 6How to capitalize the first letter in a cell in Excel PAGEREF _Toc455480198 \h 6Retrieve weekday from date PAGEREF _Toc455480199 \h 7Sort cells by font color in Excel PAGEREF _Toc455480200 \h 7How to Color Alternate Rows in a MS Excel 2010 Table PAGEREF _Toc455480201 \h 8Calculate the number of days between two dates PAGEREF _Toc455480202 \h 12Days between the two dates: PAGEREF _Toc455480203 \h 12Weekdays between the two dates: PAGEREF _Toc455480204 \h 12Excel Options PAGEREF _Toc455480205 \h 13Custom Lists PAGEREF _Toc455480206 \h 13Macros PAGEREF _Toc455480207 \h 14HyperAdd PAGEREF _Toc455480208 \h 14HyperAdd Hotkey Solution PAGEREF _Toc455480209 \h 14FormatTL PAGEREF _Toc455480210 \h 15FormatWrapTxt PAGEREF _Toc455480211 \h 16How to Use the Vlookup Function in Excel PAGEREF _Toc455480212 \h 17VLOOKUP PAGEREF _Toc455480213 \h 17LOOKUP_VALUE PAGEREF _Toc455480214 \h 17TABLE_ARRAY PAGEREF _Toc455480215 \h 17COL_INDEX_NUM PAGEREF _Toc455480216 \h 17[Range_Lookup]) PAGEREF _Toc455480217 \h 18Count the Number of Cells with Text Formatted Italic PAGEREF _Toc455480218 \h 19Glossary PAGEREF _Toc455480219 \h 20Search & Find PAGEREF _Toc455480220 \h 20Left, Right, Mid PAGEREF _Toc455480221 \h 20Custom Number Format PAGEREF _Toc455480222 \h 21Leading Zeros PAGEREF _Toc455480223 \h 21Decimal Places PAGEREF _Toc455480224 \h 22Add Text PAGEREF _Toc455480225 \h 22Large Numbers PAGEREF _Toc455480226 \h 23Repeat Characters PAGEREF _Toc455480227 \h 23Colors PAGEREF _Toc455480228 \h 24Dates and Times PAGEREF _Toc455480229 \h 25How to Export Comments in Excel to a Word Doc PAGEREF _Toc455480230 \h 27Automated File Names PAGEREF _Toc455480231 \h 28Question: PAGEREF _Toc455480232 \h 28Advice PAGEREF _Toc455480233 \h 28Padded CELL PAGEREF _Toc455480234 \h 28Step 1: FINDing the Beginning and the End PAGEREF _Toc455480235 \h 29Step 2: LEFT a bit, RIGHT a bit, Aim for the MID Section PAGEREF _Toc455480236 \h 29Step 3: Error Trapping PAGEREF _Toc455480237 \h 30Final Note PAGEREF _Toc455480238 \h 30Vlookup Multiple Criteria Tutorial in Excel 2013 PAGEREF _Toc455480239 \h 31Understanding the Vlookup Function PAGEREF _Toc455480240 \h 31Concatenate to Create Unique Identifier PAGEREF _Toc455480241 \h 32Adding the Vlookup Formula PAGEREF _Toc455480242 \h 33Add the Lookup Value PAGEREF _Toc455480243 \h 34Multiple String Search Criteria PAGEREF _Toc455480244 \h 35Macros – Creating Macros PAGEREF _Toc455480245 \h 36Record a Macro PAGEREF _Toc455480246 \h 36Create a Macro Using Microsoft Visual Basic PAGEREF _Toc455480247 \h 37Create a Startup Macro PAGEREF _Toc455480248 \h 37Copy Part of a Macro to Create another Macro PAGEREF _Toc455480249 \h 37Troubleshoot Macros PAGEREF _Toc455480250 \h 38When I click the Refresh button, a message tells me that my macro changes will be lost. PAGEREF _Toc455480251 \h 38While recording a macro, I recorded an action I didn't want. PAGEREF _Toc455480252 \h 38A macro I recorded sometimes produces an error message. PAGEREF _Toc455480253 \h 38Excel TipsFind Specific Text in a Cell=ISNUMBER(SEARCH("clarity",E2))How to Extract the First Word of a String in Microsoft Excel=LEFT(C2,FIND(" ",C2)-1)How to Extract the Last Word of a String in Microsoft Excel=RIGHT(C2,LEN(C2)-FIND("*",SUBSTITUTE(C2," ","*",LEN(C2)-LEN(SUBSTITUTE(C2," ","")))))=RIGHT(C2,LEN(C2)-FIND("*",SUBSTITUTE(C2," ","*",LEN(C2)-LEN(SUBSTITUTE(C2," ","")))))How to Extract the First 2 Words of a String in Microsoft Excel=LEFT(C2,FIND(" ",C2,FIND(" ",C2,1)+1)-1)Remove First Word in a String=RIGHT(A1,LEN(A1)-FIND(" ",A1))Remove Last Word in a String=LEFT(M2,LEN(M2)-FIND(" ",M2))Pull String before a Certain CharacterThe following formula will perform the extract you need:=LEFT(F2,FIND(";",F2,1)-1)Using a Formula to Fill Blank Cells with Value AboveWith this method, please do as the following steps:Select the range that contains blank cells you need to fill.Click?Home?>?Find & Select?>?Go To Special…, and a?Go To Special?dialog box will appear, then check?Blanks option. See screenshot:Click?OK, and all of the blank cells have been selected. Then input the formula “=A2” into active cell A3 without changing the selection. This cell reference can be changed as you need. Press?Ctrl + Enter, Excel will copy the respective formula to all blank cells. At this point, the filled contents are formulas, and we need to convert the formals to values. Then select the whole range, right-click to choose?Copy, and then press?Ctrl + Alt + V?to active the?Paste Special…?dialog box. And select Values?option from?Paste, and select?None?option from?Operation. Then click?OK. And all of the formulas have been converted to values.Add Hyperlink to Text Only and Not the Entire Cell in Excel 2010Adding a hyper reference (although you can still select the whitespace of a cell (after the text has finished) and it won't follow the link)=CONCATENATE("This is your ", HYPERLINK("","Google"), " link")FYI, you can hold ALT key and click on a cell without it opening the URLHow to Create a Hyperlink Inside of Excel Using the Value in another CellHow to create a hyperlink inside of excel using the value in another cell to as part of the link. The Formula looks like this:HYPERLINK(link_location,friendly_name)…Where?link_location?is where you want the hyperlink to go to and?friendly_name?is how the link will appear on your sheet.For example, if you have a file on your desktop it would look something like this:Hyperlink(“C:\Documents and Settings\Admin1\Desktop\Example.xls”,”Example Link”)You would just need to change the exact path to the file on your computer. You can also make the file name a cell reference that contains the name of the file you want to point to, as in this example:Hyperlink(“C:\Documents and Settings\Admin1\Desktop\” & A1 & “.xls”,A1)…In this example, A1 could equal “Example” and “Example will also be displayed for the hyperlink text.How to Extract a File Name from a Path in Microsoft ExcelPath & File name: c:\My Documents\ExcelTip.xlsThe Function:=MID(A25,FIND(“*”,SUBSTITUTE(A25,”\”,”*”,LEN(A25)-LEN(SUBSTITUTE(A25,”\”,”"))))+1,LEN(A25))Result: ExcelTip.xlsCount the Number of Words in a Cell Or a Range Cells in Excel=IF(LEN(TRIM(C2))=0,0,LEN(TRIM(C2))-LEN(SUBSTITUTE(C2," ",""))+1)Counting the number of occurrences of a character in one cell=LEN(E2)-LEN(SUBSTITUTE(E2,";",""))Count the number of cells that contain text.=COUNTIF(A1:A7,"*")Count the number of cells that contain star in any way. No matter what is before or after star, this function finds all the cells that contain star in any way.=COUNTIF(F:F,"*Title*")Count the number of cells that contain exactly star + a series of zero or more characters. An asterisk (*) matches a series of zero or more characters.=COUNTIF(A1:A7,"star*")Count the number of cells that contain exactly star + 1 character. A question mark (?) matches exactly one character.=COUNTIF(A1:A7,"star?")Count the number of cells that contain exactly star.=COUNTIF(A1:A7,"star")How to capitalize the first letter in a cell in ExcelTo capitalize “How” in cell “A1″, use the following formula:=REPLACE(A1,1,1,UPPER(LEFT(A1,1)))Retrieve weekday from date =TEXT(B2,"dddd")Sort cells by font color in ExcelIn fact, sorting by font colour in Excel is absolutely the same as?sorting by background color. You use the?Custom Sort?feature again (Home?>?Sort & Filter?>?Custom Sort...),?but this time chooseFont Color?under "Sort on", as shown in the screenshot below.If you want to sort by just one font color, then Excel's AutoFilter option will work for you too:Apart from arranging your cells by background color and font color, there may a few more scenarios when sorting by color comes in very handy.How to Color Alternate Rows in a MS Excel 2010 TableIf you used to know how to color the alternate table rows in earlier versions of MS Excel you need to learn this useful skill again because the method has changed.There are several methods to do this. Here I’ll cover one such method.Here is our?sample table, without any colors at all:(1) Select the cells?you’d like to fill with alternating bands of color:(2)?Go to the?HOME?tab on your ribbon.(3)?Select?Conditional Formatting > New Rule?to display the?New?Formatting?Rule?dialog box.(4)?Select “Use a formula to determine?which?cells to format” for?Rule Type.(5)?Enter this formula for?Rule Description:?=MOD(ROW(),2)=0(6)?Click?Format?to display the?Format Cells?dialog box. Select the?Fill?tab:(7)?Select the color you like and click?OK. Back at the?New?Formatting?Rule?dialog box, click?OK?again.Now the 1st, 3rd, and all the other?ODD NUMBERED alternating rows?of your table are colored:(8)?If in the formula you change 0 with 1 (=MOD(ROW(),2)=1), EVEN NUMBERED alternating rows?will be colored:(9)?If you’d like to have?TWO DIFFERENT COLORS alternating, first color the whole background solid in the color you like and then follow all the above steps:By applying both methods?to color?alternate rows?and?alternate columns, you can create unique and interesting color schemes for your table:Calculate the number of days between two datesTo do this task, use the subtraction (-) operator or the?NETWORKDAYS?function as shown in the following example.A26/8/2007A36/20/2007Days between the two dates:=A3-A2Weekdays between the two dates:=NETWORKDAYS(A2,A3)Note: use DATE(2012,5,23) for the 23rd day of May, 2012Excel OptionsCustom ListsGo to Excel Options > Advanced under the “General” sectionMacrosHyperAddOn the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Copy and paste this code into the code window of the module. It will automatically name itself HyperAdd.Sub HyperAdd() 'Converts each text hyperlink selected into a working hyperlink For Each xCell In Selection ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula Next xCellEnd SubWhen you're finished pasting your macro, click Close and Return to Microsoft Excel on the File menu.Then select the required cells and click macro and click run.NOTE?Do NOT select the whole column! Select ONLY the cells you wish to be changed to clickable links else you will end up in a neverending loop and have to restart Excel! Done!HyperAdd Hotkey SolutionHere’s a hotkey solution, but I just can't figure out how to get Excel to evaluate a column of URLs as hyperlinks in bulk.Create a formula,?="=hyperlink(""" & A1 & """)"Drag downCopy new formula columnPaste Special Values-only over the original columnHighlight column, click?Ctrl-H?(to replace), finding and replacing?=?with?=?(somehow forces re-evaluation of cells).Cells should now be clickable as hyperlinks. If you want the blue/underline style, then just highlight all cells and choose the Hyperlink style.The hyperlink style alone won't convert to clickable links, and the "Insert Hyperlink" dialog can't seem to use the text as the address for a bunch of cells in bulk. Aside from that,?F2?and?Enter?through all cells would do it, but that's tedious for a lot of cells.FormatTLSub FormatTL()'' FormatTL Macro'' Keyboard Shortcut: Ctrl+u' Cells.Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Range("G4").SelectEnd SubFormatWrapTxtSub FormatWrapText()'' FormatWrapText Macro'' Keyboard Shortcut: Ctrl+i' With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End WithEnd SubHow to Use the Vlookup Function in ExcelVLOOKUPThe Vlookup function for Excel is a great function that allows you to search through a list of data within Excel.? This allows you to do things like use the ID number of an item to locate other data for that item without having to manually go through a list of data.Though the function seems complex at first, it is quite easy to use once you become comfortable with the functions arguments.Here is the function listed with all of its arguments:=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Below, each argument for this function is explained.LOOKUP_VALUEThis is the actual thing/item/value/etc. that you want to use to locate the desired data contained within your Excel spreadsheet.? This could be a unique ID number that attaches to a specific record; it could be a name; it could be a number that is contained within a range (explained in the range_lookup argument).? Simply put, this is the value that is used to locate the data that you want to return.? Once the Vlookup function finds this value, it will return data that is contained within the exact same row as where this value is found.? The col_index_num argument below is where you tell the Vlookup function from which column you would like to return the data.Note that this value is searched for within the left-most column of data that is contained within the reference stated in the next argument, table_array.? Also, you may use a cell reference for the lookup_value instead of an actual value; this is a common technique that is used so that you can return different data without having to change the actual Vlookup function.TABLE_ARRAYThis argument tells the Vlookup function from which table of data you need to retrieve your values.? For this argument, you will select a range of cells, for instance: A1:D10.? In this example, the left most column is A and this is where the Vlookup function will try to find the lookup_value argument in order to return data from the corresponding row in which it is found.Make sure that you select a table_array that contains all of the data that you would like to return using the Vlookup function because you cannot return any data that is outside of this table array.COL_INDEX_NUMThis is where you tell the Vlookup function which column contains the data that you would like to return.? Following the example used in the table_array argument explanation above, if you want to return data from column C then you would put 3 for this argument.? The columns are numbered starting with 1 for the left-most column referenced in the table_array argument.[Range_Lookup])This is the only optional argument and its value can only be either TRUE or FALSE.? This argument tells the Vlookup function if you want it to find an exact or approximate match.? If you input FALSE, which is the most commonly used value for this argument, Excel will only return an exact match where the lookup_value exactly equals a value found in the left-most column of the table stated in the table_array argument.? If this value is not found, it will return an error.? If multiple values are found, the Vlookup function will return the first value that it finds.If you leave the value for this argument empty, since it is an optional argument, it will default to TRUE, which you can also manually enter yourself.? In this case, the Vlookup function will return an exact match if one is found, BUT, if an exact match is not found, it will return the next largest value that is less than the lookup_value.? Also, in order for this to work, the first column of the table_array needs to be sorted in ascending order (the one that will be used to find the lookup_value).This TRUE value for the range_lookup argument is most often used when you want to return something that can be within a range, such as school grades, where an A or a B is not just a single number but, instead, can be achieved by having a grade that is within a range of numbers such as 80-89 for a B and 90-100 for an A.The Vlookup function is one of the most helpful functions that you will use within Excel.? Though it may seem scary and confusing at first, just stick with it and you will be able to input this function in seconds and save yourself hours of sifting through data.Count the Number of Cells with Text Formatted ItalicThe only way you can do this is by creating a UDF:Alt+F11 to get to the VBA editorInsert a module (right click on your sheet name and choose <Insert><Module>)Paste this code:Function CountItalic(rng As Range)Count = 0For Each rng In rngIf rng.Font.Italic = True ThenCount = Count + 1End IfNextCountItalic = CountEnd FunctionThen you will be able to use the function CountItalic() in your worksheet.To make sure I did not count blank italic-formatted cells, I did have to add a nested if with the resultant formula: Function CountItalic(rng As Range)Count = 0For Each rng In rngIf rng.Font.Italic = True ThenIf rng.Value <> "" ThenCount = Count + 1End IfEnd IfNextCountItalic = CountEnd FunctionGlossarySearch & FindSEARCH -?Returns the number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive).FIND -?Returns the starting position of one text string within another text string. FIND is case-sensitive.Left, Right, MidLEFT:?Returns the specific number of characters from the start of a text string.RIGHT:?Returns the specific number of characters from the end of a text string.MID:?Returns the characters from the middle of a text string, given a starting position and length.Syntax:LEFT(text,num_chars)RIGHT(text,num_chars)MID(text,start_num,num_chars)Custom Number FormatLeading Zeros | Decimal Places | Add Text | Large Numbers | Repeat Characters | Colors | Dates & TimesExcel?has many built-in formats that you can use: Currency, Accounting, Date, Time, Percentage, etc. If you cannot find the right format, you can create a?custom number format.Leading ZerosFor example, you might have codes that consist of 5 numbers. Instead of typing 00041, simply type 41 and let Excel add the leading zeros.1. Enter the value 41 into cell A1.2. Select cell A1, right click, and then click Format Cells.3. Select Custom.4. Type the following?number format code:?000005. Click OK.Note: Excel gives you a life preview of how the number will be?formatted?(under Sample).Result:Note: cell A1 still contains the number 41. We only changed the appearance of this number, not the number itself.Decimal PlacesYou can also control the number of decimal places. Use 0 to display the nearest integer value. Use 0.0 for one decimal place. Use 0.00 for two decimal places, etc.1. Enter the value 839.1274 into cell A1.2. Use the following number format code:?0.00Add TextYou can also add text to your numbers. For example, add "ft".1. Enter the value 839.1274 into cell A1.2. Use the following number format code:?0.0 "ft"Note: remember, we only changed the appearance of this number, not the number itself. You can still use this number in your calculations.Large NumbersYou can also control large numbers. Use one comma (,) to display thousands and use two commas (,,) to display millions.1. Enter the following values in cells A1, B1, C1 and D1: 1000000, 2500000, 81000000 and 700000.2. Use the following number format code:?0.0,, "M"Note: we used 0.0 for one decimal place and "M" to add the letter M.Repeat CharactersUse the asterisk (*) followed with a character to fill a cell with that character.1. Type Hi into cell A1.2. Use the following number format code:?@ *-Note: the @ symbol is used to get the text input.ColorsYou can control positive numbers, negative numbers, zero values and text all at the same time! Each part is separated with a semicolon (;) in your number format code.1. Enter the following values in cells A1, B1, C1 and A2: 5000000, 0, Hi and -5.89.2. Use the following number format code:?[Green]$#,##0_);[Red]$(#,##0);"zero";[Blue]"Text:" @Note: #,## is used to add comma's to large numbers. To add a space, use the underscore "_" followed by a character. The length of the space will be the length of this character. In our example, we added a parentheses ")". As a result, the positive number lines up correctly with the negative number enclosed in parentheses. Use two parts separated with a semicolon (;) to control positive and negative numbers only. Use three parts separated with a semicolon (;) to control positive numbers, negative numbers and zero values only.Dates and TimesYou can also control dates and times. Use one of the existing Date or Time formats as a starting point.1. Enter the value 41674 into cell A1.2. Select cell A1, right click, and then click Format Cells.3. Select Date and select the Long Date.Note: Excel gives you a life preview of how the number will be formatted (under Sample).4. Select Custom.5. Slightly change the number format code to:?mm/dd/yyyy, dddd6. Click OK.Result:General note: a custom number format is stored in the workbook where you create it. If you copy a value with a custom number format to another workbook, it will also be available in that workbook.How to Export Comments in Excel to a Word DocAn Excel workbook may contain dozens of comments from its different editors. These comments remain most relevant when attached to their associated cells, but they can also form a story of their own. For example, if you and a coworker edit a sales projection sheet together, the sheet's comments will track your conversation about the sheet's formulas. One way to isolate these comments is to write a Visual Basic macro that sends them to a Word document.Step?1Open the Excel workbook, then press "Alt-F11" to open Excel's Visual Basic editor.Step?2Double-click the "NewMacros" module in the Project window's Modules folder, if Word does not open it automatically.Step?3Type the following to open a new macro:Sub mcrComment()Step?4Type the following to declare the macro's variables:Dim excelComment As Comment Dim wordProgram As ObjectStep?5Type the following to create a Word document:Set wordProgram = CreateObject("Word.Application")Step?6Type the following to open a loop that acts on the Word object:With wordProgramStep?7Type the following to open and select the Word document:.Visible = True .Documents.Add DocumentType:=0Step?8Type the following to open a loop that acts on each comment:For Each excelComment In mentsStep?9Type the following to print each comment's author and cell address to the Word document:.Selection.TypeText excelComment.Parent.Address & vbTab & excelComment.TextStep?10Type the following to print each comment to the Word document:.Selection.TypeParagraphStep?11Type the following to close the For-Next loop:NextStep?12Type the following to close the With loop:End WithStep?13Type the following to close the macro:End SubStep?14Press "F5" to run the macro and export the comments to a Word document.Automated File NamesQuestion: For version control purposes, I want to display the current filename of my Workbook within one or more worksheets. Is there a way to do this short of typing it in manually??AdviceWhere there’s a will there’s a way, and I use the following formula:=IF(ISERROR(OR(FIND(“[",CELL("filename",A1)),FIND("]“,CELL(“filename”,A1)))),”",MID(CELL(“filename”,A1),FIND([",CELL("filename",A1))+1,FIND("]“,CELL(“filename”,A1))-FIND(“[",CELL("filename",A1))-1))Professional modelers often have a “rule of thumb” when writing a formula. Literally. In general, for formulae to be transparent, they should appear no longer than the length of your thumb in the formula bar. Of course, there are exceptions to many great rules, and alas, this is one of them.Therefore, I think the above formula needs explanation.?Padded CELLCELL(Info_Type,[Reference])?returns information about the formatting, location, or contents of the upper-left cell in a reference (in our example, we will be using cell A1 as our reference in the active worksheet, but this selection is entirely arbitrary).Info_Type returns various information depeding upon what has been selected:“address”?– Reference of the first cell in reference, as text;“col”?– Column number of the cell in reference;“color”?– 1 if the cell is formatted in colour for negative values; otherwise returns 0 (zero);“contents”?– Value of the upper-left cell in reference; not a formula;“filename”?– Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved;“format”?– Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns “-” at the end of the text value if the cell is formatted in colour for negative values. Returns “()” at the end of the text value if the cell is formatted with parentheses for positive or all values;“parentheses”?– 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0;“prefix”?– Text value corresponding to the “label prefix” of the cell. Returns single quotation mark (‘) if the cell contains left-aligned text, double quotation mark (“) if the cell contains right-aligned text, caret (^) if the cell contains centred text, backslash (\) if the cell contains fill-aligned text, and empty text (“”) if the cell contains anything else;“protect”?– 0 if the cell is not locked, and 1 if the cell is locked;“row”?– Row number of the cell in reference;“type”?– Text value corresponding to the type of data in the cell. Returns “b” for blank if the cell is empty, “l” for label if the cell contains a text constant, and “v” for value if the cell contains anything else; and“width”?– Column width of the cell rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.We therefore use the syntax?=CELL(“filename”,A1). An example of a returned filename might be:C:\Documents and Settings\Liam\My Documents\Spreadsheet Doctor\Doctor 30 – Automated Filename\[Example Workbook.xls]Sheet1This is not what is required, there’s ‘padding’. All we want is the actual filename, in this case ‘Example Workbook.xls’. Therefore, we need to extract the filename from this worksheet directory path.This will be a three-step process.?Step 1: FINDing the Beginning and the EndThe directory path will vary for each file, so we need to spot a foolproof method of finding the beginning and the end of the workbook name. Fortunately, Excel assists us here. ‘[’ and ‘]’ are reserved characters in Excel’s syntax and denote the beginning and the end of the workbook name.The example returned filename above is 122 characters long. If we can find the position of the ‘[’ and ‘]’ we will be on our way.FIND(find_text,within_text,start_num)?is the function we need, where:find_text?is the text you want to find;within_text?is the text containing the text you want to find; andstart_num?(which is optional) specifies the character at which to start the search. The first character in?within_text?is character number 1. If you omit start_num, it is assumed to be 1.So, in our example,?=FIND(“[",CELL("filename",A1))?returns the value 95 and the formula=FIND("]“,CELL(“filename”,A1))?returns the value 116. In other words, for our illustration, if we can get Excel to return the character string in positions 96 to 115 inclusive (i.e. between the square brackets) we will have our workbook name.?Step 2: LEFT a bit, RIGHT a bit, Aim for the MID SectionThere are various functions in Excel that will return part of a character string:LEFT(text,num_characters)?returns the first few characters of a string depending upon the number specified (num_characters). This is not useful here as we do not want the first few characters of our text string;RIGHT(text,num_characters)?returns the last few characters of a string depending upon the number specified (num_characters). This is not useful here either as we do not want the last few characters of our text string; andMID(text,start_num,num_characters)?returns a specific number of characters from a text string, starting at the position specified, based on the number of characters chosen.Therefore, we should use the MID function here. In hard code form, our formula would be:=MID(CELL(“filename”,A1),96,20)where:96 = position one character to the right of ‘[’ (95 + 1); and20 which is the length of the filename string, being the position of ‘]’ less the position of ‘[’ less 1, i.e. 116 – 95 – 1 = 20.This gives us our filename ‘Example Workbook.xls’.The problem is we don’t want hard code: a flexible formula is required. Using the concepts explained above, we derive:=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]“,CELL(“filename”,A1))-FIND(“[",CELL("filename",A1))-1)And so we are done. Except we aren’t…?Step 3: Error TrappingA good modeler will always ensure that a formula will work in all foreseeable circumstances. The above formula will only work if the file has been named and saved. Otherwise,?CELL("filename",A1)?will return empty text (“”), which will cause the embedded FIND formulae to return #VALUE! errors, and hence the overall formula will also return the #VALUE! error.We therefore need an error trap, i.e. a check that ensures if the file has not yet been saved we just get empty text (“”) returned. To do this, we can use the following formula:=IF(ISERROR(OR(FIND("[",CELL("filename",A1)),FIND("]“,CELL(“filename”,A1)))),”",1)ISERROR(expression)?gives a value of TRUE if the?expression?is evaluated as an error, otherwise it is FALSE. In our equation above, if the file has not been saved, this formula will return the empty text (“”), otherwise it will return our ‘dummy’ value of 1. Substituting our derived formula above for the 1 gives us the final formula:=IF(ISERROR(OR(FIND(“[",CELL("filename",A1)),FIND("]“,CELL(“filename”,A1)))),”",MID(CELL(“filename”,A1),FIND(“[",CELL("filename",A1))+1,FIND("]“,CELL(“filename”,A1))-FIND(“[“,CELL(“filename”,A1))-1))It isn’t pretty, it’s not short, it’s not transparent, but it’s flexible and robust.?Final NoteThe formula above is intended to be copied – as is – straight into an Excel worksheet by pasting it directly into the Excel formula bar and pressing ENTER. In certain situations, it will not work due to the exact method of copying employed, fonts used or the set-up of the ASCII characters.In this instance, try re-typing all of the inverted commas (“ and ”) in the formulae first. If this doesn’t work, I apologize, but you will have to re-type it. C’est la vie.Vlookup Multiple Criteria Tutorial in Excel 2013Excel is a powerful way of creating, storing and analyzing lists of data for business purposes. The functions in Excel allow you to sort and retrieve data from large spreadsheets using formulas like the Lookup formulas in Excel. ?Learning to use the Vlookup or Lookup formula can save you time where you need to find specific data based on certain criteria. This tutorial will show you how to use the CONCATENATE formula to useVlookup to search multiple criteria within your data. This tutorial assumes you know how to create a spreadsheet and that you know how to work with formulas and functions in Excel.Microsoft Excel 2013 Simplified?will teach you the basics of Excel and how to work with the various elements within Excel. This course offers 110 lectures to help you learn to harness the power of Excel at the office and at home. The course is aimed at introducing beginners to the program and also helping more advanced users to improve their Excel skills. You will learn how to create worksheets and the course will slowly take you through the functions of Excel until you are able to handle advanced topics with ease.Understanding the Vlookup FunctionThe Vlookup function is designed to help you look up specific data in a list or array of data based on a specific criteria that you set. The Vlookup function is built to find data based on one column of data, but Vlookup can be used in conjunction with other functions to allow you to search using Vlookup based on multiple criteria. Using one function within another function is called nesting functions and the real power of Excel lies within its power to nest the functions you can use.The following example will show you how to use two criteria in the Vlookup function using the concatenate function. The tutorial is based on the following example data:We are going to create a function to search for the price of a specific item and item size. The Vlookup function requires a unique field to find. As you can see from the example we have two rows for each item, one for the small size and one for the large size. So we first need to use the concatenate function to create a unique field to search on before we use the Vlookup function.To learn how to use the various functions in Excel, sign up for the?Excel 2013 course now and learn to use Excel like a master. This course contains 101 lessons that will introduce you to Excel 2013 and will teach you the basics and more advanced concepts in Excel.Concatenate to Create Unique IdentifierSo we will first create a new column to create a unique identifier for our search. Add a column to your worksheet and then CONCATENATE the “Item” and “Size” fields to create a new unique identifier:This is the formula you will need to use to create the new column:Now that we have a unique identifier, we can create two input fields for our search. Add a new cell called Item and one called Size so that spreadsheet users can enter the item and size they are looking for:Now add a field for Price and then we will add the Vlookup formula to lookup the price of the item.Adding the Vlookup FormulaWe are going to use the formula wizard to add the Vlookup formula for our spreadsheet. To start the formula wizard, select the “vlookup” formula from the “Lookup & Reference” Formulas under the Formulas Tab:The following formula wizard will open up:Enroll in?the?Excel 2013 The Basics?course now and learn to use the formulas and functions available in Excel. This course offers 160 lessons and downloadable worksheets to help you learn all the Excel skills you need to master Excel 2013. The course will get you started with Excel, it will teach you how to perform calculations, how to modify a worksheet, how to format the worksheet and how to save. You will learn to manage large workbooks; it will teach you Excel formulas to make you life easier and you can follow along in the downloadable workbooks.Add the Lookup ValueWe now need to tell Excel what we are looking for in the Vlookup wizard. Remember we have created a new column that represents the concatenation of the item name and item size. So we need to find the concatenation of the item and size when we search for the data. To add the concatenation formula to our wizard, click the formula and then type in CONCATENATE(E13,E14) to create a field that will match our list based on what a user types in under item and item size:The table we want to search must start with the column that contains the field we are looking for, so the table we are searching for is C1:H11. The column we want to search for the answer – in this case we are searching for the price of the item – is column number 3 because column number 3 is the 3rd column in the table we are searching. Finally we will enter FALSE because we are looking for an exact match for our search and not the closest match.You completed formula wizard will look like this:If a user types in “Shirt” and “Small” The answer will look like this:Multiple String Search CriteriaConcatenate is a great way to use multiple search criteria when the criteria are all strings. You can concatenate as many fields as you like using the concatenate function so you can create a unique field and then use the same concatenation to create a unique value to search those fields.Macros – Creating MacrosRecord a MacroSet the security level to Medium or Low.On the Tools menu, click Options. Click the Security tab. Under Macro Security, click Macro Security. Click the Security Level tab, and then select the security level you want to use. On the Tools menu, point to Macro, and then click Record New Macro. In the Macro name box, enter a name for the macro?(macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.). NotesThe first character of the macro name must be a letter. Other characters can be letters, numbers, or underscore characters. Spaces are not allowed in a macro name; an underscore character works well as a word separator.Do not use a macro name that is also a cell reference or you can get an error message that the macro name is not valid.If you want to run the macro by pressing a keyboard shortcut key?(shortcut key: A function key or key combination, such as F5 or CTRL+A, that you use to carry out a menu command. In contrast, an access key is a key combination, such as ALT+F, that moves the focus to a menu, command, or control.), enter a letter in the Shortcut key box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or special character such as @ or #. NoteThe shortcut key will override any equivalent default Microsoft Excel shortcut keys while the workbook that contains the macro is open.In the Store macro in box, click the location where you want to store the macro. If you want a macro to be available whenever you use Excel, select Personal Macro Workbook.If you want to include a description of the macro, type it in the Description box.Click OK. If you want the macro to run relative to the position of the active cell, record it using relative cell references. On the Stop Recording toolbar, click Relative Reference so that it is selected. Excel will continue to record macros with relative references until you quit Excel or until you click Relative Reference again, so that it is not selected.Carry out the actions you want to record. On the Stop Recording toolbar, click Stop Recording . Create a Macro Using Microsoft Visual BasicOn the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Type or copy your code into the code window of the module. If you want to run the macro?(macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) from the module window, press F5. When you're finished writing your macro, click Close and Return to Microsoft Excel on the File menu. Create a Startup MacroAutomatic macros?(macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.), such as Auto_Activate, are designed to run when you start Microsoft Excel. For more information about these macros, see Visual Basic Help?(Microsoft Visual Basic Help: To get help for Visual Basic in Excel, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.).Copy Part of a Macro to Create another MacroSet the security level to Medium or Low. On the Tools menu, click Options. Click the Security tab. Under Macro Security, click Macro Security. Click the Security Level tab, and then select the security level you want to use. Open the workbook that contains the macro?(macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) you want to copy. On the Tools menu, point to Macro, and then click Macros. In the Macro name box, enter the name of the macro that you want to copy. Click Edit. Select the lines of the macro you want to copy. To copy the entire macro, make sure to include the Sub and End Sub lines in the selection.On the Standard toolbar?(toolbar: A bar with buttons and options that you can use to carry out commands. To display a toolbar, point to Toolbars on the View menu, and then click the toolbar you want. If the button you want doesn't appear, click More Buttons.), click Copy INCLUDEPICTURE "" \* MERGEFORMATINET INCLUDEPICTURE "" \* MERGEFORMATINET . Switch to the module where you want to place the code. Click Paste INCLUDEPICTURE "" \* MERGEFORMATINET INCLUDEPICTURE "" \* MERGEFORMATINET . TipYou can view your Personal Macro Workbook file (Personal.xls) at any time by opening it in the Visual Basic Editor?(Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.) (Alt+F11). Because Personal.xls is a hidden workbook that is always open, you must unhide it if you want to copy a macro.Troubleshoot Macros When I click the Refresh button, a message tells me that my macro changes will be lost.This message appears when you have made changes to a macro in the Visual Basic Editor and have also changed the copy of your workbook in the Microsoft Script Editor.To save the changes to your macro, do the following:Click No. Switch to the Visual Basic Editor window. Export any modules?(module: A collection of declarations, statements, and procedures stored together as one named unit. There are two types of modules: standard modules and class modules.) you have changed. In your workbook in Microsoft Script Editor, click Refresh INCLUDEPICTURE "" \* MERGEFORMATINET INCLUDEPICTURE "" \* MERGEFORMATINET on the Refresh toolbar. Click Yes. Import the modules to restore the changes to your macro. While recording a macro, I recorded an action I didn't want.To undo an unwanted action in a macro, you can open the macro in the Visual Basic Editor and remove any unwanted steps.If you don't want to use the Visual Basic Editor, record the macro again without the unwanted action.A macro I recorded sometimes produces an error message.A macro you record may not run properly in every situation. If the macro cannot run, Microsoft Excel displays an error message. Some macros depend on certain options or settings in Excel. For example, a macro that searches for bold text won't run properly if bold text isn't displayed. If a macro you've recorded produces an error message, note the error number. You can then search for "error messages" in Visual Basic Help and find information about the message you received. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download