Maximizing Connectivity & Productivity

Archive for the Excel Category

Excel table advantages

So why use Excel tables?  Isn’t a spreadsheet just like a table?  Well, there are benefits to using an excel table.. First, here’s how to create one. Simply select your data and go to Home-> Styles-> Format As Table, pick the table style from the selection that […]

Read more

Conditional formatting in Excel

Use this feature to highlight different aspects of data and make it more readable. Select the data containing the info to be highlighted.  Go to Home->Styles->Conditional Formatting->Highlight Cells Rules and then select which rule applies best. If you are adventurous, you can also create your own rules.  […]

Read more

Slicer in Excel 2010

Use slicers to view (slice) a pivot table to make the pivot table summarization of data easier to read. In essence, a slicer will ‘slice’ the pivot table by selected categories, so that only data is viewed. To use: Click inside the pivot table -> the contextual […]

Read more

Reduce the #DIV/0! error in Excel

If you know your formula is correct, and it is okay for the column being used in the division to have a zero, (and you don’t want your end user to think your spreadsheet is wrong), then your formula should use the IF statement.  Ex. If  A2/B2=0 […]

Read more

Use Text to Columns to split Excel columns containing a separation character

Use the Text to Columns feature in Excel if you have columns in Excel containing a lot of data seperated by either a comma, semi-colon, period or space(s). Simply select the column containing the unsplit data, go to the Data tab, select the Text to Columns button, […]

Read more

Return a Worksheet name to a cell

 In Excel, use the CELL function/formula and the MID and FIND to return the name of an Excel Worksheet in a Workbook. The formula below shows us how; =MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256) Where A1 is any non error cell on the Worksheet. If you want the full path of the […]

Read more

Save file as Tab delimited with changes

 To save: ActiveWorkbook.Close SaveChanges:=1 or ActiveWorkbook.Close True Or to not save: ActiveWorkbook.Close SaveChanges:=0 or ActiveWorkbook.Close False

Read more
Page 3 of 3123
Mississauga, ON, Canada 905.607.3500