Microsoft Office

/Microsoft Office
­

How to Insert an Attachment Into an Excel Spreadsheet

When you insert an object into an Excel file, such as a PDF file, Word document or an email message, you can access it more easily during a meeting or share more comprehensive information when you send the spreadsheet to a co-worker. When you attach an object this way, you can add all the content of an existing file, include a clickable icon linked to the file, or create a brand new file and then embed it in your Excel worksheet.

By |March 19th, 2016|Excel|

Change the number of worksheets in a new workbook

Beginning with Excel 2013, each new workbook contains one worksheet. You can change the number of worksheets that appear by default in a new workbook.Click the File tab.Under Excel, click Options.On the General tab, under When creating new workbooks, in the Include this many sheets box, enter the number of sheets that you want to include by default when you create a new workbook.Click any other tab

By |November 16th, 2015|Excel Tips|

Keep Excel Headers Visible

When you have lots of data that goes down for rows and rows that don’t fit on one page, you have at least 3 options for getting Excel to keep the column headings in view whilst scrolling up and down:

By |November 15th, 2015|Excel Tips|

Get the last day of a month

To get the date of the last day of the month in Excel, use the End of Month function.

By |November 14th, 2015|Excel Tips|

Look Up the Address of an Item in a List

 You can use a formula that returns the cell address of a particular item in a worksheet. In the example below, cell D2 contains a Cog or Sprocket Stock Number, and cell E2 contains the following array formula to return the item’s address:=ADDRESS(MIN(IF(A3:A15=D2,ROW(3:15))),1)Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl + Shift + Enter keys,

By |October 31st, 2015|Excel|

Deleting a Database

If you have a database you do not need anymore, you can delete it. To delete a database, in My Documents, in Windows Explorer or another file management application:You can click it to select it and press DeleteYou can right-click it and click DeleteA warning message would be presented to you to confirm what you

By |October 22nd, 2015|Access|

Calculating Variances with Negative Numbers

A common metric in dashboards and reporting is variance calculation, i.e. comparing a particular data point against a target or previous data point to find the delta.For example, if you have a target of 100, and an actual of 67, then the variance would be -33%. In other words, the Actual is 33% less than the Target.The standard

By |September 22nd, 2015|Excel, Formulas|

Calculate Percentage Change

If you want to calculate the percentage change between value old_value and new_value, this is done using the formula:percentage change = ( new_value - old_value ) / old_valueFor example if your sales figures are $12,500 in January and $17,500 in February, the percentage change in February, compared to January, is given by:percentage change = ( $17,500 - $12,500 )

By |September 7th, 2015|Excel|

Excel String Concatenation

Related Function:CONCATENATEExcel string concatenation, i.e. joining together text strings, can be performed by using the: Ampersand (&) operator, orCONCATENATE functionUsing the Ampersand (&) OperatorThe & Operator, when used between two text strings, tells Excel to return a single text string, consisting of the concatenation of the two original strings.Syntax=text1&" "&text2&" "&[text3]&" ... )Examples ABCDE1FirstNameLastNameDOBFormulaResult2   ="John"&" "&"Jones"John Jones3BarbaraBUSH =A2&" "&B2Barbara BUSH4GeorgeBUSH11/12/85=A3&" "&B3&",

By |September 5th, 2015|Formulas|

Using SUMPRODUCT Function for Advanced Counting

You can use the SUMPRODUCT function to create advanced analysis of a data set. For example, you can calculate the number of time a sales rep has made sales in a particular region.

By |August 27th, 2015|Excel|