My free Excel webinar for June 2018 covered Copy and Paste Tips and Tricks. Download the materials using the button below and watch the video.
Download Copy Paste materials
The session focuses on the Paste Special dialog plus a little known pasting feature that is great for dashboards.
It covers the hows and whys of
- Paste Values, Paste Formulas, Paste Formats
- Converting negative to positives
- Fixing Text numbers in-situ
- Applying a Factor to a range
- Paste Link – how and why to use it
- Transpose (switching rows to columns and visa-versa)
- Paste Picture Link (great for dashboards)
The session includes lots of keyboard shortcuts. As always, I shared a few other tips during the session.
My free Excel webinar for May 2018 covered Text functions. Download the materials using the button below and watch the video.
You know how well Excel handles numbers, but not everyone knows that Excel has built-in functions and features to work with text as well. This session covers Excel’s text functions and features, in it you will learn
- the different techniques to split text
- techniques to extract text from text
- how to easily join text
- techniques for tweaking text for dates, numbers, upper and lower case
- the formulas for extracting sheet and file names
- two new Excel 2016 functions for combining text from ranges
As always, I will be sharing a few other tips during the session.
Download Text Function materials
It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file.
Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.
Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.
This is the first in a series of webinars dedicated to macros. Future paid sessions this month will expand on the techniques taught in this session.
Macro Webinar Materials
Blanks in Excel can cause a lot of issues, but there is one instance where you can skip them and speed up copying and pasting.
Check out my September 2014 Excel Yourself article, video and companion file are now online at the magazine site. The article covers converting a badly laid out report into a structured data layout using a formula.
The video can be viewed from the media section at the bottom of the page.
Click here to view the article.
Check out my follow up article and VIDEO on the ITBDigital website on how to convert a vertical bullet chart into a horizontal one.
Click here for full article and video.
For the original bullet chart post click here
These techniques are based on ones in the great book
Excel 2007 Dashboards and Reports For Dummies by Michael Alexander
Check out my July 2014 article on Bullet charts on the CPA Australia ITBDigital website – click here to see the article. The video is below.
Bullet charts were developed by Stephen Few – see his pdf on bullet charts click here.
The technique is based on one used by Michael Alexander in his great book Excel 2007 Dashboards and Reports for Dummies by Wiley.
When you copy a formula in Excel, any relative references (those without dollar signs) may change depending on where you paste the formula. If you would like to copy a formula and not have the relative references change you have two options.
The Format As Table feature has many useful features that are worth taking advantage of. The previous post listed them. The video of this blog is shown at the bottom of the post.
Changing a sheet name and deleting the hyperlink cell are two processes that can break Excel sheet hyperlinks. The video is at the bottom of the page.
If I print emails I typically only want the first page. To do that in Outlook takes a few clicks each time.
When dealing with data lists in Excel it is a common requirement to extract the unique entries from a field. Excel has a built-in feature that will create a unique list.
Line charts are frequently used in Excel but their default settings leave a lot to be desired. See the transformation of a standard line chart to a simpler and easier to read line chart.
A Column chart walks into a Bar chart … sorry, I couldn’t resist that. Column charts are one of the most popular and straightforward of Excel’s many chart types. Its close cousin is the Bar chart.
Excel 2007 introduced the new interface called the Ribbon. It’s a cross between a toolbar and a menu. It also has a Quick Access Toolbar (QAT) that many people don’t seem to be using.
Excel allows you to easily hide a group of sheets BUT frustratingly, it won’t let you unhide a group of sheets. You have to unhide them one sheet at a time.
Make Your Own Lists like January, February etc
When you drag a cell with January in it with the fill handle (the small cross at the bottom right corner of the cell), Excel creates a series with February and March etc. This is quick and useful. It also works for days of the week. Eg drag a cell with Monday in it to get Tuesday, etc. This is an example of a custom list.