Rushicom.files.wordpress.com



Chapter 01 ( Interface & use of basic icons) Ribbon - TabsRibbon View at Various screen Resolutions – Shrinking and MinimizingRibbon - to Access Dialog BoxQuick Access Tool Bar - QAT QAT Usage – Activation and DeactivationQAT Customization – Selection – Separator – De-selectionKeyboard shortcuts – Usage/Application to connects to tabs and ribbonsMini Tool Bar – on right click of the mouse after selecting some text or cell with dataLive Preview – when fonts and few icons are used then live preview on excel sheet appearsZoom Slider and Page Layout – Bottom of the excel sheet ( right and left corner )Adding new worksheetRecent Documents – ViewRecent Documents – Managed through excel options-advanced tab – Manage upto 50, if screen allows (only when your monitor is large to hold what amount of data, remember no scroll bar by excel provided).Recent Documents- erase by choosing excel options and reducing view documents to zero.Save as PDF ( Note: XPS is new type like PDF files from Microsoft )Excel Options – Activating and Location in Excel 2007Excel Options – Quick Usage optionsOpening Templates- Online in different categories using icon menu and choosing “New”(workbook) option.Closing Workbook – Using “Exit Excel” and tagging it to QAT to close all work books.Chapter 02 ( Features)Big Grid in Ex: In 2007 Excel – 1048576 rows and 16000 columns ( XFD – LAST COLUMN ), Note: around 65 thousand rows in 2003 and last column IV (Not roman letter)New file extensions xlsx and xlsm, normal xlsm is macro enabled workbook.There is no problem in saving file in xlsm format as you go back and forth with xlsx or xlsm.Cell Styles ( Build in ) – Data and Model ; Title and Headings; Number formatThemes ( On page layout tab ) – choose themes if you want to create a common file across to team or company.Data Visualization – Data Bars, color scales, icon sets. – Usage or application. In conditional formatting.Other-“ highlighted cell rules: and “top/bottom rules” in conditional formatting.Tables and Table formulas – extension, addition creation of pivot table from table and creation of table from insert tab or format as table icon on Home tab.Table - no need to select the range of data of table or select its extension when pivot table is formed, because excel automatically extends the data and only pivot table needs to be refreshed.Sort by color – only in table format with filter on.Sort by font – only in table format of data with filter on.Remove duplicates from data set and Mark duplicates from data set.Picture tools and Word artHow to print data set with a picture background – though insert shaping, using transparency and picture.How to add word art to titles of a data set or title of a chart, to make data set view of spread sheet more interesting to have a great look.Formula bar – Extension if formula’s are large data size either by clicking extension button to expand. Translation of languages – application to few cells if needed. ( May not be required )Chapter 03 ( TEXT )Drag the fill handle to extend a series – use of ctrl key and menu to fill weekdays, fill series option in menu. ( available through build in menu options )Create your own series – Using custom list option. ( using excel option )Concatenate function ( Concatenate, “ “ ( for space)) use of & key or [cell value & cell value ] will join text values of the sell. to add space it is A2&” “&B2 if A2, has name Nataraj and B2 has name ganeshi. It will appear Nataraj ganeshi.Rapid formula copy through double clicking fill handle. Use of left column and data right below the data of the fill handle to fill the series. ( May have to refer lesson )Convert to Proper case – Use of proper(A2&” “B2), lower and upper functions to convert lower case to upper case.Joining Text and dates – use of concatenate, & and date formats to join text and date.Converting formulas to values – use of paste values, drag on right click for menu which say paste only values.Breaking apart text ( use of text to columns icon on data tab )Parsing fixed width. ( Use of fixed width from text to column and format cells)Chapter 04 ( Customizations )Data entry tricksCustomize work books by creating templates. ( saving file as .xltx extension or excel template file and using it – new file – my templates and selecting file.)Move or copy worksheets.Group mode and remember to deselect the group.Arranging windows- see workbooks side by side, see worksheets side by side. ( use of view tab and relative icons) Chapter 05 ( Sorting and Subtotals )Data Preparation – enter data without any blank row or blank column as it helps in pivot table and sorting.Sorting – basic using home tab or data tab and sorting AtoZ or ZtoA or custom sort to sort multiple columns of dataRadom sort – use of Rand() function – remember no arguments is rand() function.Sorting by weekday – custom sort.Rearranging columns – either by creating pseudo row and using custom sort options and sorting by row 1. Or by dragging after pressing ctrl+space bar ( to select entire column) and then moving it though fill handle and mouse.Subtotal – Use of subtotal icon in the dataset to get subtotal of a sorted column and grand total on Data tab.Collapsing subtotals – Use of 1, 2, 3 buttons of left of the screen after using subtotal icon on data tab for better view.Copy Subtotals: Use of visible cells icon or alt+; or using customize in QAT for seeing visible cells. To capture only subtotals summary to a different sheet or to summarize data by subtotals only. Formatting subtotals – Use visible cells options as used in point 58.Multiple subtotals – Use of subtotal icon.Filling data in subtotals – Use of “go to special” in subtotal icon in data tab, then select blanks and remember to capture last row up the row of the total. Customer no and customer name.Subtotal tricks – use of using =sum() function / 2 to get the grand total. And others.Chapter 06 ( Pivot tables )Creating Pivot tableChanging pivot table – use of refresh button after changing the data in work sheet.Pivot table – copy of summary. To add zero to blank cells of the pivot table.Pivot sorting – Manual as well as using sort icon.Use of expand and collapse button, changing in the data set if you find wrong entry in data set and then pressing refresh. Double click on number value or data in pivot table to get details of that particular numerical value appearing in the pivot table.Summarizing dates. ( Changing daily dates to day, month & year. You can also do it for week, but two things you need to remember that select day and capture 7 days in the option available. Use of group filed icon the relative icons like active filed icon in pivot table tools, 10 or top 5 listing and use of group filed in options tab to create one field, say revenue three times to arrange at frequency distribution type, count and total value or revenue.Calculation fields or column In pivot tableUse of pivot table fields to calculate outside the pivot table.Pivot formatting use of pivot table style and use of active filed icon.Use of pivot table to get unique list ( say by customers, etc )Ad hoc queries by using filter in field list of pivot table.Many reports in few clicks using pivot table. Use In “Pivot table names-Options-Show Report filter pages”. Ex: to get print outs of may customers and making customer specific pivot table to each customer.Pivot table compatibility with 2003. Remember most the options of 2007 excel does not work or work at all if sent back to a computer which runs on 2003. Best option to come over this is to save in xls format and then allow other person, use to refresh button. ( Not required so much as on date)Making report with pivot table. Making columns appear in row and then creating the pivot table out of it. (L59-Re-reference needed)Chapter 07 (Recording Macros) Recording Macro – a simple , single cell change recordingsRecording Navigation – a little complex by applying “ use relative references” iconRecording Macro with Navigation on dynamic data set. Use $ key and choose in any function like sum() or others accordingly, to ensuring selection of entire data when there is increase of rows while recording macro and applying it to different location data sets.Event Macros – applicable to worksheet and workbook. Selection in General and Worksheet to events on right in VBA codes. No need of Module in this case.Chapter 08 ( Formulas )Auto sum tricksFormula Auditing3 ways to enter formula = keyboard, mouse and (+ sign and keyboard)Function F4 – Relative and Absolute formulaDiscovering functions (fx-key, arguments and other details)Goal seek functionWorkday function, Networkdays function, convert function ( use help function in function icon to look for conversion) ex : =Convert(“100”,”lbm”,”kg”) to convert 100 pounds to equivalent kg.IF formulasVLOOKUP and HLOOKUPSUMIFS,COUNTIF,AVERAGEIF Functions and all the other functions as per (Excel_Syllabus_Guru.pdf)IFERROR – used in vlookup and othersDate and time calculationsChapter 09 ( Charting and Smart Art )Creating and Formatting chartAdding and removing chart dataInteractive chart using filter iconDiffering magnitude charts meaning – on large data set to small data setPie charts , XY, Bubble charts, saving chart types as favorite templates.Creating smart artMaking appear dynamic text in smart art. ( Smart art does not accept formulas )Chapter 10 ( Macros – Minimum learning as below)Record your first macro ( a simple macro )Variable number of rows ( learn how to make total appear in the last row of the dynamic data set)Total rows ( Learn how to make sum, count , average , max , min appear in the last row of the dynamic data set)Learn event macros ( that is change in worksheet, workbook, particular code etc) End of Document ................
................

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

Google Online Preview   Download