background preloader


Facebook Twitter

300 Excel Examples. 1 Create a Macro: Swap Values, Run Code from a Module, Macro Recorder, Use Relative References, FormulaR1C1, Add a Macro to the Toolbar, Macro Security, Protect Macro. 2 MsgBox: MsgBox Function, InputBox Function. 3 Workbook and Worksheet Object: Path and FullName, Close and Open, Loop through Books and Sheets, Sales Calculator, Files in a Directory, Import Sheets, Programming Charts. 4 Range Object: CurrentRegion, Dynamic Range, Resize, Entire Rows and Columns, Offset, From Active Cell to Last Entry, Union and Intersect, Test a Selection, Possible Football Matches, Font, Background Colors, Areas Collection, Compare Ranges. 5 Variables: Option Explicit, Variable Scope, Life of Variables. 6 If Then Statement: Logical Operators, Select Case, Tax Rates, Mod Operator, Prime Number Checker, Find Second Highest Value, Sum by Color, Delete Blank Cells.

300 Excel Examples

QA0001 - Synchronise SharePoint with Excel. Bloomberg Business - Business, Financial & Economic News, Stock Quotes. This Is How Fast America Changes Its Mind By Alex Tribou and Keith CollinsUpdated: June 26, 2015 Eleven years after Massachusetts became the first state to allow same-sex couples to marry, the Supreme Court has now extended that right nationwide.

Bloomberg Business - Business, Financial & Economic News, Stock Quotes

The decision came after a wave of gay marriage legalization: 28 states from 2013 to 2015, with 36 overall prior to the Court's ruling. Office France : le blog officiel de Microsoft France. Ce Weekend de Pâques fut bien agréable mais dès mardi qui n’a pas pensé ?

Office France : le blog officiel de Microsoft France

Vivement le prochain jour férié? ? Mais quel jour est-ce ? Et l’année prochaine quels seront les dates exactes des jours fériés ? Alors créons un classeur qui calculera tout seul ces jours de repos comme celui-ci. ASAP Utilities for Excel – Blog » Tip: Easily insert an English formula in your non-English Excel version. Go to Special - Detailed Tutorial on how to use this Excel feature. Posted on March 12th, 2012 in Excel Howtos - 18 comments This article is written by Myles Arnott from Excel Audit.

Go to Special - Detailed Tutorial on how to use this Excel feature

Excel's Spreadsheet Auditing Functions & How to use them? Posted on January 18th, 2012 in Financial Modeling , Learn Excel - 15 comments This series of articles will give you an overview of how to manage spreadsheet risk.

Excel's Spreadsheet Auditing Functions & How to use them?

These articles are written by Myles Arnott from Excel Audit In the first two articles in this series we highlighted the risks that poorly managed spreadsheet solutions can introduce to a business and outlined the steps companies can take to manage this risk. This article works through the application of some of Excel’s built in auditing functions: Error checking (Background and stepping through each error)Trace ErrorCircular ReferenceGo To Special Let’s have a look at an example spreadsheet that is riddled with issues. Download Example file first. If you are up for a challenge you could download the file and work through the report in the Example tab to see how many of the errors you can find yourself.

First off let’s identify the obvious issues Circular reference On opening the file you are presented with this warning message: GiliWiki - Excel. Excel Videos tips - Danny Rocks. Non-contiguous cells & Named Ranges. Forcing Users to Enable Macros. It happens at least once a quarter.

Forcing Users to Enable Macros

A customer calls you to complain that your expertly-created dashboard is doesn’t work “for them”. After a few seconds of questioning, you realize they didn’t enable macros. Forcing Users to Save As XLSM. I'm sure some of you have faced this: You send out a macro enabled workbook (an xlsm file). The user performs a Save As on the workbook, but saves it as an xlsx. Show AutoFilter Criteria in Status Bar. One of my 12 fans (Lester) emailed to remind me that I have a blog.

Show AutoFilter Criteria in Status Bar

I almost forgot. So with a good kick in the pants, I’m back to blogging. . While I was away, Microsoft awarded me with the coveted Microsoft Excel MVP award for the fourth time. So I guess I better start earning that distinction here. Filter for Unique Records. July 21, 2011 by datapig Leave a reply » During the summer, blog readership will be down. So I'm saving my good Excel and Access tricks for later.

Right now, I'm continuing a series called 'Oldies but Goodies'. In this series, I'll share some of the older Excel and Access tricks that have been around for years. How to Use Advanced Filters in Excel. Cascading ListBoxes. I’ve decided to dedicate this week to using Recordsets in Excel.

Cascading ListBoxes

Not to connect to external data sources, but to specifically using them to simplify internal Excel tasks. Today, I’ll show you how to use Recordsets to help create a set of List Boxes that cascade values.. The ProblemI want to create a set of ListBoxes – Region, Market and State. I need to have these ListBoxes dynamically work together. That is to say, if I select a certain Region, the Market and State ListBoxes need to show only values that exist in the Region I selected. Here are a couple of screenshots to show what I mean. The Solution The solution involves using an ADO Recordset to pull the records for the Child ListBoxes using the Parent’s value as the criteria. Smart Scroll Bars for Model controling.

It’s time for another life-changing Excel trick.

Smart Scroll Bars for Model controling

In this post, I’ll show you how to make what I call “smart Scroll Bars”. These are Scroll Bars that work together to dynamically change their max parameters based on a predefined limit. I put together this snazzy flash animation to show you what I mean. In this scenario, the total allowed is 100. This means that the total value output by all these Scroll Bars cannot exceed 100. Here are the basic steps for creating smart Scroll Bars: Step 1: Set up your spreadsheet. The basic set up relatively simple. Then you specify a “Total Allowed” field which will hold your maximum limit. Relative Sheet References function. Category: VBA Functions | [Item URL] You may have discovered that Excel's support for "3D workbooks" is limited.

Relative Sheet References function

For example, if you need to refer to a different worksheet in a workbook, you must include the worksheet's name in your formula. This is not a big problem -- until you attempt to copy the formula across other worksheets. The copied formulas continue to refer to the original worksheet name. This tip contains a VBA function (named SHEETOFFSET) that lets you address worksheets in a relative manner. Then, you can copy this formula to other sheets and the relative referencing will be in effect in all of the copied formulas. The SHEETOFFSET Function The VBA code for the SHEETOFFSET function is listed below.

Function SHEETOFFSET(offset, Ref) ' Returns cell contents at Ref, in sheet offset Application.Volatile With Application.Caller.Parent SHEETOFFSET = .Parent.Sheets(.Index + offset) _ .Range(Ref.Address).Value End With End Function Using the SHEETOFFSET function. Exploding a Dataset using a PivotTable. If you've been following the blog, you'll know that it's Pivot Table week. That's right. All in honor of the upcoming Independence Day holiday. Here's another dose of PivotTable awesomeness. How to Change Excel Chart Data Labels to Custom Values? Posted on May 5th, 2010 in Charts and Graphs - 48 comments We all know that Chart Data Labels help us highlight important data points. When you “add data labels” to a chart series, excel can show either “category” , “series” or “data point values” as data labels.

XY Chart Labeler Add-In. A very commonly requested Excel feature is the ability to add labels to XY chart data points. The XY Chart Labeler adds this feature to Excel. The XY Chart Labeler provides the following options: Add XY Chart Labels - Adds labels to the points on your XY Chart data series based on any range of cells in the workbook. Move XY Chart Labels - Moves the entire set of data labels or individual labels in any direction and in the increment of your choosing.