Displaying 1-20 of 68 results
Exclusive
Simple but effective, part 2
- 18 Apr 2024
- Simon Hurst
In this series we are going to examine some of the capabilities of Excel Tables. In part 1 we considered different ways of turning a range of cells into an Excel Table and demonstrated the importance of Tables in helping to ensure that ranges in formulas adapt automatically to changes in the dimensions of the Table. This time, we will look in more detail at adding rows and columns to existing Tables and see how this can contribute to automating your spreadsheets.
Exclusive
Simple but effective, part 1
- 27 Mar 2024
- Simon Hurst
It’s very easy to format a block of data in your favourite colour scheme by using an Excel Table, but Excel Tables are so much more important than just facilitating formatting. In this series we are going to see just what they are capable of, starting with looking at how to create Excel Tables and going on to cover some simple examples of their practical use, such as avoiding global austerity.
Excel's new functions might change data analysis forever
- 29 Feb 2024
- Liam Bastick
Excitement is building, as a range of new functions introduced by Microsoft towards the end of last year are – hopefully – edging closer to general release. Here, Microsoft MVP Liam Bastick explores how they work and what they might mean for you.
Exclusive
Spreadsheet review – can Power Query help: part 1?
- 13 Feb 2024
- Simon Hurst
Legacy Excel supports a range of functions and techniques that can help identify potential errors and problems within a spreadsheet as part of a review process. However, it’s worth considering the addition of Power Query to your review toolkit on the grounds of simplicity and ease of use.
Exclusive
Enhancing data display and entry with Excel Form Controls
- 13 Nov 2023
Form Controls are objects that can be used to display data or make it easier for users to enter data, to perform an action or to make a selection.
Exclusive
EXCEL TIP OF THE WEEK
Excel Tips & Tricks #482 – Get Data From Picture
- 30 Oct 2023
- Ian Pay
Hello all and welcome back to Excel Tips and Tricks! This week, we have a Creator level post in which we're covering some relatively new functionality that allows users to capture data into a spreadsheet from an image.
Exclusive
Advanced conditional formatting – part 3: the horror of peril sensitivity
- 02 Oct 2023
- Simon Hurst
Applying default conditional formats is very straightforward and can be accomplished with just a couple of clicks, but delving a little deeper can help you to use conditional formatting in additional ways and to tailor your format to your own requirements. After looking in part 1 at some of the more advanced ways to use cell formats as part of conditional formatting and in part 2 at the graphical conditional formats, this time we will look at some other uses of conditional formatting including how it works with PivotTables and Power Query tables as well as some more unusual applications of con...
Exclusive
Formatting Slicers
- 30 Aug 2023
- Liam Bastick
In earlier versions of Microsoft Excel, you can use report filters to filter data in a PivotTable report, but it is not easy to see the current filtering state when you filter on multiple items. In Microsoft Excel 2010 and later versions (that is, from when Slicers were introduced), you have the option to use Slicers to filter the data
Exclusive
YOUR QUESTIONS ANSWERED
Your Questions Answered #4 – Is Excel still the go to application for financial modelling?
- 27 Jun 2023
- Craig Stirk
In a recent webinar on ‘Financial Modelling and Scenario Planning: Mastering Effective Decision-Making’, Craig Stirk and Ben Ducker explored why scenario planning is important and why structured models can help. Craig returns to answer some questions from the webinar they were unable to respond to during the session.
Exclusive
Organisation Data Types
- 19 Jun 2023
- Simon Hurst
Once planned to include a whole range of general data to be provided through a now-terminated partnership with Wolfram Alpha, built-in Excel Data Types are now restricted to Geography, Stocks and Currencies. However, using Power BI Pro, it is possible to build your own ‘organisational’ Data Types to give your Excel users access to information from both within your organisation and more public sources of data.
Exclusive
Is there anything that Power Query can't do?
- 30 May 2023
- Simon Hurst
There are many things that Power Query can't do but, in this occasional series, we will be looking at some of the less obvious techniques available. In this episode we look at automating the changing of column heading text.
Exclusive
Power Query – building in checks and controls – part 1
- 21 Apr 2023
- Simon Hurst
In the first of a three-part mini-series we are going to see how Power Query can help identify potential problems in our output tables. In part 1 we examine the importance of total checks and see how to construct them.
Exclusive
YOUR QUESTIONS ANSWERED
Your Questions Answered #3 – Questions on the life-changing opportunities of Power Query?
- 17 Apr 2023
- Simon Hurst
Our latest live webinar saw Simon Hurst drill into the capabilities of Excel Power Tools and, in particular, Power Query. The webinar provoked many questions. We dealt with some of the more straightforward questions as part of the post that announced the availability of the Webinar recording. This time we delve deeper into Power Query.
Exclusive
Excel updates – Data From Picture
- 07 Jul 2022
- Simon Hurst
Simon Hurst describes a new feature of Excel, which uses a picture as a data source.
Exclusive
EXCEL HOW TO
Excel how to: speed up formulae - using Excel tables part 2
- 06 May 2022
- Simon Hurst
In our first look at Excel Tables, we concentrated on referring to Table contents from a cell outside of the Table itself. This time we will consider formulae within Tables.
Exclusive
EXCEL HOW TO
Excel how to: speed up formulae - using Excel tables part 1
- 21 Apr 2022
- Simon Hurst
Having previously looked at how to enter cell references in formulae using dollar signs to fix all or part of a reference, and the use of Range Names to make formulae easier to enter and understand, this time we will start looking at how the use of Excel Tables can make it quicker to enter Excel formulae.
Exclusive
TECH NEWS
Multiple Excel Tables in a single worksheet – avoiding Cell Clash
- 17 Mar 2022
- Simon Hurst
I have been working on a project recently for which I wanted to include a set of refreshable Excel Tables in a control worksheet to highlight exceptions in the underlying data. Generally, the best way to avoid Tables clashing with each other as they expand and contract is to keep them on their own, separate, worksheets. In this case, I wanted to create a single workbook that would highlight potential errors at a glance, and the Tables concerned were only likely to generate a few rows each. The following examples demonstrate some of the considerations involved.
Exclusive
Excel update – Power Query can now use Dynamic Arrays as a data source
- 06 May 2021
- Simon Hurst
Up to a week or so ago, the Data Ribbon tab, Get & Transform Data group included a From Table/Range command that would create a new Power Query query from an Excel Table, a named Range or from a selected block of cells that would be turned into an Excel Table as part of the process. In the Insider, Beta Channel edition of Office 365, this option has now been renamed as From Sheet. This change in name is a result of the expansion of the available data sources within an Excel worksheet to include Dynamic Arrays.
The greatest spreadsheet error of them all – treating Excel with utter contempt
- 06 Oct 2020
- Simon Hurst
The PHE Covid-19 testing data debacle has created a great deal of comment and discussion. Leaving aside the rights and wrongs of the actual issue, some of the attitudes it has revealed have been illuminating.
Power Query combine – how to automate consolidations
- 20 Aug 2020
- Simon Hurst
About three years ago, the old Excel, Get External Data options were relegated to 'legacy' status and the use of Power Query became the default method for linking to external data from within Excel.
Displaying 1-20 of 68 results
Search the Excel Community archive
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.