Document Entire Excel Workbook with the Click of a Button

Document Entire Excel Workbook with the Click of a Button

Why Document an Entire Excel WorkbookYou already know the story…. or do you…..?So the internal accountant produces a monthly pack for management, which contains financial information. These reports are fantastic.  Detailed Sales reports show sales against budget, against prior year, as a 12 month rolling total. They are also further sliced against sales rep and […]

Quickly gather statistics for your data using Excels Descriptive Statistics

Excels Descriptive Statistics will quickly allow you calculate a number of statistics from multiple columns of data at a time.The statistics includeMeanStandard ErrorMedianModeStandard DeviationSample VarianceKurtosisSkewnessRangeMinimumMaximumSumCountIn this video you will learn how to activate the Data Analysis tool pac in Excel and how to quick run descriptive statistics. You can download the data set at the bottom of […]

stastics correlation coefficient excel

How to Calculate Correlation in Excel with the Data Analysis ToolPac

Correlation is a statistical measure that shows how two variable might move together.   For Example as the price goes down, the sales volume might go up, or as the weather gets warmer, sales volume might decrease.  These types of correlation might be obvious in some organisations, but looking at the correlation between different data sets […]

How to find the Nth Largest in Excel

Both MIN and MAX in Excel will return the Minimum or Maximum value from a list.  But what if you want to return a value that is the Nth smallest or the Nth largest in Excel? How would you go about this type of Ranking?You could use one of Excels Rank functions and sort by rank, […]

Excels Rank Functions

Using Excels Rank functions

Excels Rank functions have evolved over time, and give you the ability to ranks values in a list or column based on their value compared to other values in the same list or column.In Excel 2007 and previous versions, the function you would use to rank value is Rank and the syntax is:=RANK(Number, Ref, [Order])Where:Number […]

How to use SUMIF and SUMIFS in Excel

The SUM function in Excel is probably the most widely used function available.  In fact it is the only formula that has its own keyboard shortcut (Alt & =).  Therefore it should come as no surprise that this basic mathematical function has more powerful cousins available in Excel.  These functions are know as SUMIF and […]

paste special in Excel

Paste Special in Excel

Paste in Excel is one of the most commonly used functions.  You do it, I do it, everyone using excel will paste something during the day.  But did you know that Excel has a paste special option? An options that allows you paste data and formats in multiple ways!  From Transpose to pasting as an […]

Print Options in Excel

Fine Tune your Workbook with Print Options in Excel

There is nothing worse than receiving an Excel workbook that looks the part, but then when you print it, tables and charts are split onto different pages. Do people know there are print options available in excel? This problem is a common spreadsheet problem.  People fail to use the page layout options and page set up […]

Excels Scenario Manager and Goal Seek

Excels Scenario Manager and Goal Seek

Scenario Manager and Goal Seek in Excel are two very useful features and both of these function are classes as What if Analysis functions. Scenario manager can be used to save different scenarios of the same situation on the same worksheet.  For example, lets say you have a budget and you have prepared a best case […]

Wonderfully Weird function in Excel

Wonderfully Weird function in Excel

Excel is full of wonderful and weird features and functions.  With so many functions available, a lot of the time there is more than one way you can solve a problem in Excel.  Sometimes I look at functions and I wonder to myself….what problem could this solve? (that’s a bit sad to be admitting really) […]