Excel Formulas
An Excel Formula is series of calculations based on values in cells on
a spreadsheet. There are standard formulas like SUM and FIND supplied
with Excel and you can create your own custom formulas to perform
calculations. To see a list of the standard functions supplied with
Excel click on Insert > Function. You will be presented with the
following dialog box listing the available formulas and functions.
You can search for the available functions or select categories to view
specific groups of functions. Any custom functions are listed here as
well for use.
We have developed many custom formulas and functions for our clients.
It may be a simple calculation based on an existing Excel formula or
could be a complex statistical calculation that requires many
intermediate steps to arrive at the correct result.
Contact Us
today to discuss any specific customs formulas or functions
that you need developed
Excel Macros
An Excel macro is a set of instructions that can be triggered by a
keyboard shortcut, toolbar button or an icon in a spreadsheet. Macros
are used to eliminate the need to repeat the steps of common tasks over
and over. Tasks such as standardised formatting, adding or removing
rows and columns, protecting or unprotecting worksheets, selecting a
range of cells, or creating a table of default information can all be
performed via a Macro.
Excel Macros are either hand written in Visual Basic for Applications
(VBA) or the more common way for most Excel users via the macro
recorder within Excel. To start the macro recorder in Excel select
Tools > Macro > Record new Macro. You will then be
presented with the Record Macro dialog box
Standard Formatting Macro Example
Let's
say that you regularly apply the same formatting to cells in your
worksheets. Open a new workbook and following the steps below to create
your first Macro.
Step 1 - Select
Tools -> Macro -> Record New Macro. You
will be asked to specify a Macro Name, call this Macro 'StandardFormat'
and Click OK. This will set the Macro recorder to capture all commands
and actions that you take.
Step 2 - Select
Format -> Cells -> Font. This will
present the Format Cells Dialog Box. Now simply specify the standard
formatting for your worksheets and Click OK. For this
sample we have specified the following, Font - Tahoma, Style -
Bold, Size - 16, Color - Blue.
Step 3 - Select
Tools -> Macro -> Stop Recording. This
will stop the Recorder and save your Macro for later use.
Step 4 - Now
lets run the Macro and Test that it performs the actions
we require. Type in some basic Text to a Cell on the first worksheet.
Then select that Cell and then Select Tools -> Macros. The
following Macro selection box should be displayed.
Step 5 - Select the Macro 'StandardFormat'
and then select Run to execute the Macro. After Clicking Run the text
selected should automatically be formatted to the format you specified
in
Step 2 above.
Step 6 - That's
it you have now created your first Macro. You can use
that Macro on a single Cell or a Range of Cells to apply formatting as
required.
For more Macros that you can create yourself review our
previous
Newsletters
which contain many examples of basic Macros that
can be developed quickly and simply.
Custom Excel Macros
We specialise in developing custom macros for our clients. It can be as
simple as automating a series of calculations and converting some data
or it could be a complete forms based application with command buttons
and reporting.
Whatever your requirement if you need something automated within Excel
then our team of consultants will be able to help.
Contact Us
today to discuss your requirements.
