为自己做个记录，也为自己打打气。当个每天学一点做一点的蜗牛。
| 08/05/2011 | Microsoft Excel Tutorial for Beginners #1 - Overview |
| Tip: | To create a chart: Highlight main portion of data and upper/left titles(not including total rows/columns, then click on Insert Chart. |
| 08/06/2011 | Microsoft Excel Tutorial for Beginners #2 - Get Started |
| Tip: | To edit a excel field: use F2 key; To jump back from end of one row to the beginning of next row: click on "Enter" key. |
| A Guide to the Business Analysis Body of Knowledge | |
| Tip: | Business analysis planning and monitoring Elicitation(启发式) Requirements management and communication Enterprise analysis Requirements analysis Solution assessment and validation />Underlying competencies Techniques |
| 08/07/2011 | Microsoft Excel Tutorial for Beginners #3 - Calculations |
| Tip: | To set a field formula: use "="+(highlight the first field)+(key in operator)+(highlight the second field); To make the rest of rows use same formula: highlight the first cell that has formula settled, mouse on the right bottom corner of that cell, then double click. To make sum of a column: mouse on the sum cell ( assume other impact data are at same column above ), then click on "Σ". |
| 5 Steps to Becoming a Business Analyst | |
| 08/08/2011 | Microsoft Excel Tutorial for Beginners #4 - Functions |
| Tip: | To set a field formula 2: use "="+(type in function name)+(enter"(")+(highlight the impacted fields)+(enter")"); To make the rest of columns use same formula: highlight the first cell that has formula settled, mouse on the right bottom corner of that cell, then drag to right side. To set a field formula 3: click on "fx"key at menu bar, then choose function name, then highlight impacted cells. |
| 08/09/2011 | Microsoft Excel Tutorial for Beginners #5 - Number Formats |
| Tip: | Accounting format cell: $ 135.00; Currency format cell: $135.00. To copy format from one to others: highlight the cell that has format settled,then click on "Format Painter" icon, then highlight the cells that you want to format, click on "Paste" icon. To increase/decrease decimal: highlight the cell you want to format, click on "Increase Decimal" or "Decrease Decimal" icon. To join the selected cells and center the label in new cell: highlight the related cells, and click on "Merge & Center" icon. |
| 08/10/2011 | Microsoft Excel Tutorial for Beginners #6 - Formatting Pt.1 |
| Tip: | To copy format from one to others 2: highlight the cell that has format settled,then double click on "Format Painter" icon, that way the format is locked. Then highlight the cells that you want to format, the new cells will automatically being formated. To set label center in both horizontal and vertical way: right click the cell you want to format, choose "Format Cells" icon, then go to "Alignment" tab, you may set label center in both horizontal and vertical way under "Text alignment". |
| 08/11/2011 | Microsoft Excel Tutorial for Beginners #7 - Formatting Pt.2 |
| Tip: | For empty cells that's not suppose to have data: add a dash in that empty cell, and center it. To set cell with different format ( text/background color ) due to content: set via "Conditional Formating" icon. , choose operator, enter compare value, then choose selected color |
| 08/12/2011 | Microsoft Excel Tutorial for Beginners #8 - Formatting Pt.3 |
| Tip: | To set cell with different format ( text/background color ) due to content 2: set via "Conditional Formating" icon., enter "=" + "(formula name, ie: min/max/count etc ) + "(highlight affected fields)", then choose selected color. |
| 08/13/2011 | Microsoft Excel Tutorial for Beginners #9 - Charts Pt.1 |
| Tip: | To insert a chart: Highlight the involved columns of data(including titles, then click on Insert tab -> Columns -> ( select the chart you need ). To set background color/background image for chart cell: Highlight the portion you want to format, right click to choose "Format Data Label" or "Format Plot Area" or "Format Chart Area". Fill -> "Gradient Fill"/"Picture or texture fill" are the ones you may want to play with. You may also want to add column top data so user get an idea each column means. Always keep in mind that somebody will have to look at the chart and understand it, so the best thing you can do is to keep the chart nice and simple. Don't make it complicated, so when somebody sees the chart, they will say "Yes, I understand it." |
| 08/15/2011 | Microsoft Excel Tutorial for Beginners #10 - Charts Pt.2 |
| Tip: | To copy the data from cell above: CTRL + apostrophe key ( ' ). To move your chart to different worksheet in the same Excel file:Right click the right top of the chart, choose "Move Chart" option, then choose where you want the chart to be placed. In this example, choose "New Sheet", then enter "Rental Column Chart" as new worksheet name, done. |
| 08/15/2011 | Microsoft Excel Tutorial for Beginners #11 - Charts Pt.3 |
| Tip: | To highlight two columns that next to each other and make a Pie chart: First of all, highlight first cell of column A, press "Shift" key and drag down to highlight the rest cells of column A, release. Click on "CTRL" key, then highlight first cell of column B, and drag down to highlight the rest cells of column B. Then click on Insert -> Pie chart. To add Pie percentage:Right click on pie, choose "add data labels" option, it will show data inside the pie. Then right click on the pie again, choose "format data labels", uncheck "value", and choose "percentage" checkbox, then change label position to wherever you want. To enlarge Pie size: click on outside end of pie, it will show a square, then drag corn of square to enlarge pie size. |
| 08/16/2011 | Microsoft Excel Tutorial for Beginners #12 - Printing |
| Tip: | To print the chart: Simplely select the chart, and click on print. ( You may see from Print Preview that it's print only the chart, not the tables above ) To adjust print format:Click on Print -> Page Setup, there are a couple of things you may play with. 1. Page tab -> Scaling, adjust to 140%. 2. Margins tab -> Center on page, check both Horizontally and Vertically. 3. Header/Footer, create custom header and footer. To print part of the worksheet: select the parts you want to print, then click on "Print". At "Print What" option, choose "Selection", click on "OK". (You can confirm it via Print Preview to ensure that). |
| 08/17/2011 | Microsoft Excel Tutorial for Beginners #13 - Updating Calculations |
| Tip: | What if the formula cells didn't get updated properly? One of the possibilities is you have a "-" ( means "Data N/A") for one of the formula cells, and that prevent formula cells below from re-calculating. To fix that: 1. ( In Excel New version ) Top menu -> Formulas -> Calculation Options -> make sure "Automatic" option is checked. ( In Excel old version ) Top menu -> Tools -> Options -> Calculation -> make sure "Automatic" option is checked. 2. ( In Excel New version ) Do nothing. (Can't find related setting) ( In Excel old version ) Top menu -> Tools -> Options -> Edit -> make sure "Extend list formats and formulas" option is checked. 3. Remove "-" from all formula cells ( formula cell only, not table data), set proper formula to them, then add new row data, you will see now all formula data is properly re-calculated. Double click on each formula cell to confirm the blue box range. To mannually fix the "formula cell not re-calculate issue":Double click on the formula cell, it will show a blue box around the involved table data, drag the bottom right corner of the blue box to cover the missing new rows. |
| 08/19/2011 | Microsoft Excel Tutorial for Beginners #14 - Percentages and Absolute References |
| Tip: | What if the percentage cells didn't get updated properly? To set the percentage formula for whole column, you can't simply enter "="+(select a cell)+"/"+(select total value)+(click on % icon), then hold bottom right corner and drag down. It will create invalid value/formula. You need to set the first percentage cell like this: "="+(select a cell)+"/+(select total value)+(click on % icon)+(press F4 key), then hold bottom right corner and drag down, this time there we are. That's called Absolute References. |
| 08/19/2011 | Microsoft Excel Tutorial for Beginners #15 - Percentages - More Examples |
| Tip: | (Same as above)What if the percentage cells didn't get updated properly? To set the percentage formula for whole column, you can't simply enter "="+(select a cell)+"/"+(select total value)+(click on % icon), then hold bottom right corner and drag down. It will create invalid value/formula. You need to set the first percentage cell like this: "="+(select a cell)+"/+(select total value)+(click on % icon)+(press F4 key), then hold bottom right corner and drag down, this time there we are. That's called Absolute References. |
| 08/20/2011 | Microsoft Excel Tutorial for Beginners #16 - Using The 'IF' Function |
| Tip: | To create a if condition in Excel? Use formula "=" + "if (" +(select first cell) + "In another words, =If(logical_test,[value_if_true],[value_if_false]). Another if condition example is: =if("A5=100%,"SOLD OUT","Seats Available"). What if there is a emtpy cell between if-condition cells prevent me from drag and drop copying?select a cell that has your "if condition" settle down, then copy(Ctrl+C or right click, all works), and paste(Ctrl+V or right click) to new cells, then drag and drop. |
| 08/21/2011 | Microsoft Excel Tutorial for Beginners #17 - Using Nested 'IF' Functions |
| Tip: | To create a nested if condition in Excel? Use formula =if(logic_test,[value_if_true],If(logical_test,[value_if_true],[value_if_false])). Another if condition example is: =if("A5 =B5,"TIE", if(A5 In Excel 97, 2000 and XP, you can do up to 7 layers of If-Conditions. In Excel 7, you can do up to 64 layers of If-Conditions. |
| 08/22/2011 | Microsoft Excel Tutorial for Beginners #18 - Nested 'IF' Functions Pt.2 |
| Tip: | Nested function doesn't work as you expected? Compare below two formula: =IF(C7=150,"Sold Out",IF(C7>99,"Last Few Seats","Promote")) =IF(C8>99,"Last Few Seats",IF(C8=150,"Sold Out","Promote")) The first formula works great, but the second formula failed when you enter 150 at C8 cell. The reason is: it falls into "Last Few Seats" and stop checking the rest of formula. Excel check formula from left to right, once it found something match, it stop execute the rest of formula check. Keep in mind, when you create the Nested function, when you're testing numeric values, to put them in correct order, otherwise they simply won't work. The best way is to shift on the highest value first, then move on to the lowest value. |
| 08/23/2011 | Microsoft Excel Tutorial for Beginners #19 - COUNTIF and SUMIF Functions |
| Tip: | CountIf function: =countif(range,criteria) Ex: If you want to get "Total count of Movies above 12 rentals", then the formula is: =countif(C2:C7,">12") Note: C2:C7 is just a dumn number. It means you highlight the "movie rentals" column data. SumIf function: =sumif(range,criteria,[sum_range]) Ex: If you want to get "Total Revenue of Movies above 12 rentals", then the formula is: =sumif(C2:C7,">12",D2:D7) Note: C2:C7 is just a dumn number. It means you highlight the "movie rentals" column data; D2:D7 is also dumn number. It means you highlight the "movie income" column data. CountIf function can be found in "Insert" -> Function -> Statistical SumIf function can be found in "Insert" -> Function -> Math & Trig |
| 08/25/2011 | Microsoft Excel Tutorial for Beginners #20 - Date & Time Pt.1 - Custom Format |
| Tip: | Enter a date at current year: enter month and day, hit Enter, Excel automatically add year and change format to its setting. Enter a date at future or past year:enter month, day and last 2 digits of the year, hit Enter, Excel automatically add year and change format to its setting. No matter which format/way you enter the date, Excel will automatically change to it's preset format. To copy the date format to other cells, use format painter to copy and paste. Example of set custom date format: Check Excel "Help" for details by searching "custom date format". To set today's date(a fixed date): CTRL + semicolon key To set today's date(change everyday): Enter formula "=today()" |
| 08/25/2011 | Microsoft Excel Tutorial for Beginners #21 - Date & Time Pt.2 - Date Calculations |
| Tip: | Each data related to a number in Excel, that way we can do date Calculations: simply switch between "Date" and "Number" format. To count Days-to-Go:enter function: "="+(select first date)+"-"+(select second date)+(if the second date is a shared fixed cell, then hit F4 key to lock the formula), then hit Enter. |
| 08/26/2011 | Microsoft Excel Tutorial for Beginners #22 - Date & Time Pt.3 - IF & Conditional Format |
| Tip: | What if Days-to-Go is a passed date? There are a couple of ways to handle that. 1)(Not recommend) Set a conditional format, if date 1 later than date 2, then set result cell data to be WHITE color. 2) Set formula: "="+(select first date)+"-"+(select second date)+(if the second date is a shared fixed cell, then hit F4 key to lock the formula) +" ex: =IF(A7-$E$3 How to set a Birthday Planner/Reminder:Give out Name/Birthday/Days-to-Go column w/ data. 1) Select all table data (not including title) 2) Click on "Condition Format". 3) Add first rule: "Use a formula to determine which cells to format", then enter formula: =$C44) Add second rule: "Use a formula to determine which cells to format", then enter formula: =$C45) Add third rule:"Use a formula to determine which cells to format", then enter formula: =$C46) Adjust the order of rules, make sure "red" rule was hit first, then "yellow" rule, then "green" rule. 7) Change birthday data to test resule. Note: $C4 is set by selecting first Days-to-Go cell (which is $C$4), then manually remove "$" in between. $C4 means the C column data( row data varies). |
| 08/27/2011 | Microsoft Excel Tutorial for Beginners #23 - Date & Time Pt.4 - Time Calculations & Formatting |
| Tip: | Why time calculation could be wrong? Excel store time(ex: hour) information as fraction of the day. Ex: 8:30 (hours) * 10 ( rate ), you might get a wrong value, because Excel store 8:30 hours as 0.354166667. You may change hour cell format to "General" to confirm this. To solve this: You need to times 24 to get correct result. Ex: 8:30 (hours) * 24 * 10 (rate) = $85. How to copy time formula:Mouse on the right bottom corner of a settled cell, then drag down. If you don't want to coyy formula, but want to copy the data inside ( keep all data same ): Mouse on the right bottom corner of that example cell, hold CTRL together, then drag down. |
| 08/29/2011 | Microsoft Excel VLOOKUP Function Tutorial Part 1 |
| Tip: | VLOOKUP function: =VLOOKUP(lookup_value, table_array,col_index_num, [range_lookup]) VLOOKUP function helps automatically pop up lookup table content by entering primary key. The rest of work is simply copy formula to rest of rows by click and drag. How: "=VLOOKUP("+(select primary key, ex: 2001)+","+(select whole lookup table w/o header)+(press F4 key to lock the lookup table)+","+(the index of column you wish to pop up from lookup table, ex: 2) + ")". Note: The primary key has to be sorted as A-Z or 0-9, or the formula won't work. What if the primary key IS NOT SORTED as A-Z or 0-9? The solution is to set range_lookup as false at the end of the formula instead of leave it blank. How: "=VLOOKUP("+(select primary key, ex: 2001)+","+(select whole lookup table w/o header)+ (press F4 key to lock the lookup table)+","+(the index of column you wish to pop up from lookup table, ex: 2)+","+"false)". Set the range_lookup as false is to find the exact match only. That way, no matter how you sort lookup table, the main data won't get mess. |
| 08/29/2011 | Microsoft Excel VLOOKUP Function Tutorial Part 2 |
| Tip: | Another way to set VLOOKUP function: 1. Select the lookup table w/o header. 2. Right click to "Name a range", ex: Hardware. 3. Select the cell you want to put VLOOKUP formula. 4. Top Menu "Formula" -> Lookup & Reference -> VLOOKUP. Function arguments window pops up. 5. Select primary key as lookup_value. 6. At table_arrage field, press F3 key to pop up all Paste Name, ex: select Hardware, click on OK. 7. Enter Col_index_num, ex: 2. 8. Enter Range_lookup, ex: false. 9. Click on OK. |
| 08/30/2011 | Microsoft Excel VLOOKUP Function Tutorial Part 3 |
| Tip: | How to incorporate the IF and ISBLANK function to make your VLOOKUP result more user friendly: When you have blank cell in your main table, and there is no related match in lookup table. You will want to show "-" for those related columns. Ex: =IF(ISBLANK(B10),"-",VLOOKUP(B10,Grades,2)) Note: Leave the Range_lookup as default ( true ) there, it means - find within the range. If you set it as false, that means exact match. |
| 08/31/2011 | Microsoft Excel Repeat (REPT) Function Tutorial |
| Tip: | REPT Function: =REPT(text,number_times) ex: =REPT("I",B10) -- If B10 value is 10, then the formula cell will repeat string "I" 10 times. REPT function can be found in Formula -> Text -> REPT. |
