Everything Excel. Only Excel.HomePDF to ExcelAbout UsOur AuthorsDisclaimerPrivacy PolicyAdvertisingExcel VersionsWhat is VBA?Automate Excel w/o CodePhotoshop TutorialsYoudaoXian GuoZhua XiaGoogleMy Yahoo!newsgatorBloglinesiNezhaPivot Table Tips (Part 1)November 24th, 2010| Categories: Data Manipulation| Tags: pivot, pivot table, tipsIn these two tutorials (two parts) you will learn a few cool tips regarding pivot tables: 1) To fill the empty values with zeros:Right click the base data field (circled below) Select Table Options… Type 0 in the empty cells, show (circled below) Click OK. The pivot table now should have replaced the blanks with [...]6 commentsPivot TablesNovember 21st, 2010| Categories: Data Manipulation| Tags: pivot, tablesA Pivot table is in essence a way to extract data and present it in a readable form. Pivot tables are a great way to interrogate data and create tables of information that can be refreshed when the raw data changes! Working example: you require a table that shows the following: How many positions (Actual [...]0 commentsAdvanced FiltersNovember 21st, 2010| Categories: Data Manipulation| Tags: Data, filterThe advanced filter can be a very useful tool when trying to remove duplicate values from a field. Working example: To remove duplicate position numbers from an establishment report. From the toolbar select Data > Filter > Advanced Filter… Click the selector tool for the List Range (circled below) Click the header bar for the [...]1 commentTotal, Average, Count, Min, MaxNovember 21st, 2010| Categories: Data Manipulation| Tags: average, count, max, min, totalThere are quick ways to use these everyday functions: Click the cell you wish to display the new value (ie – total) Select the sum button from the toolbar Check the range is correct (for the values you wish to include) If the range is incorrect you can highlight a new range Click OK. The [...]0 commentsAutoFilterNovember 13th, 2010| Categories: Data Manipulation| Tags: autofilter, Data, filterThe AutoFilter tool is very useful for manipulating the data to find data subsets at the click of a button. To turn the AutoFilter on: Highlight the data including the header column (if applicable). Note: the filter can be turned on without highlighting the data and it predicts the data range (usually all the data [...]2 commentsSorting DataNovember 13th, 2010| Categories: Data Manipulation| Tags: Data, sortHighlight the data From the toolbar select Data > Sort… Select your sort and order (up to 3 sorts can be applied simultaneously). Click OK. The data should now be sorted alphabetically by Service Centre.1 commentData CleansingNovember 13th, 2010| Categories: Data Manipulation| Tags: filter, replace, searchA useful method when performing data cleansing is simply “Find and Replace”. Working example: From the toolbar select Edit > Replace… (or press CTRL + H) Enter the find and replace characters (the default is not case sensitive, if you require this clic