Stop Power Query from automatically setting Data Types
When you transform data using Power Query it will often automatically promote the headers (use first rows headers) and guess the data types for each column. You can stop it doing this.
(more…)Power Query and large data sets
When working with some large data sets in Power Query it may be useful to default your Close and Load option to Connection Only rather than a Table. That way if you do strike an issue it won’t take ages to load to data.
(more…)Entering today’s date in Excel
You can use a keyboard shortcut to enter today’s date in a cell, but you can also use it in lots of other places in Excel.
(more…)New Task Pane Icons
Seems new icons have been added in the right of screen to allow you to switch easily between Task Panes in a recent upgrade. This is in the subscription version of Excel.
Power Query shortcut for Adults
The shortcut to display the Queries & Connections Task Pane is easy to remember. It is Adults Only! To display or hide the Queries & Connections Task Pane you use. These keys are pressed in sequence, not held down. So only adults allowed in Power Query.
Summing Text Numbers in Excel Updated
Get the Sign Right in Excel
I was recently working with some data that had some issues with the sign on the quantities. The quantities should have had the same sign as the associated dollar amount, but they didn’t. Here is how I fixed it.
(more…)IFERROR in Power QUERY
Power Query does not have an IFERROR function. It does however have another function that performs the same type of calculation.
(more…)Prefix Numbers Conditionally in Excel
Let’s say we need to put a prefix in front of a number to identify the period being used. Whether that be year, month or week. But we still need to use the number normally. Here’s one way to do that.
(more…)Automating bullet points in Excel
Some people like to use bullet points in text boxes. Here is a simple technique to insert bullet points based on a list of entries in an Excel sheet.
(more…)The New LET Function in Excel
If you have the subscription version of Excel you may have the new LET function. This function lets you capture variables within a formula. Let’s see an example.
(more…)Single Accounting Underline
I learned something new recently about underlines. They are not all created equal. The Single Accounting underline has some advantages.
(more…)Use an Outlook template for an email
Macros are designed to reduce keystrokes and mouse clicks. Here is a perfect example. It takes seven separate mouse clicks to use an Outlook email template as a new email. Here is a macro that does it in one.
(more…)Goal Seek
The Goal Seek feature can save you a lot of trial and error when you want a calculated cell to equal a specific value. The keyboard shortcut to open the Goal Seek dialog is Alt T G.
Excel VBA to confirm a range is selected
One of the most powerful statements in VBA is Selection. This holds whatever the user has selected just before they ran the macro. Normally it is a range, but what if it isn’t?
(more…)Shortcut to lock or unlock a cell
The keyboard shortcut to unlock or lock a cell or a range (its a toggle, so it switches between the two) is Alt H O L pressed in sequence, not held down.
Excel VBA to select the first cell in a range
If you need to refer to the first cell (top, left cell) in a range there is an easy way to do it.
(more…)How to Create Linked Array Syntax in Excel
One of the frustrations with using array syntax is that you always have to type all the entries between the curly brackets. You couldn’t link to cells. Well that has all changed with dynamic arrays.
(more…)Windows Clipboard
Did you know you can accumulate copied entries in the Windows clipboard? The default setting is to only have the last thing you copied, but a setting change can give you access to multiple items to paste. You can also pin items to the clipboard to keep them there. To change the setting. Click the Windows button and choose the Settings icon The select System. Then select Clipboard (bottom left) and change the Clipboard History to On - done. …
Creating a list of files in Excel
Let’s say that you need to create a list of files in a folder. This is possible using VBA, but it is also possible in Power Query and it’s much easier to do in Power Query.
(more…)Export as PDF
If you frequently export files as pdfs then this keyboard shortcut may save you a little time. Pressed in sequence, not held down use Alt F E A
Excel Slicers Trick
Slicers can control multiple pivot table reports in Excel. The problem is that if you use a slicer on one sheet to filter a report on another sheet it is difficult to see that a filter is in place. This technique also comes with a warning – see bottom of post. (more…)
Text for a week in Excel
When working with weeks in Excel you may need to show the start and end date of the week in the same cell. Here’s how you can do that.
(more…)Adding columns to slicers in Excel
Slicers are a great filter interface. Sometimes, due to layout restrictions, you prefer the slicer to go across the sheet rather than down the sheet. Here’s how you do that.
(more…)Percentage Area Chart in Excel
You can use a pie chart to display a percentage, but it wastes a lot of space. An alternative that takes up less space is an area chart.
(more…)Restart a Pivot Table in one go
If you want to clear all the fields from a Pivot Table you can use the clear all option. On the PivotTable Analyze or Analyze tab click the Clear drop down and select Clear All. This removes all the fields and allows you to start again from scratch.
Using SEQUENCE with Dates in Excel Part 2
The SEQUENCE function returns sequential numbers. Let’s see how we can use it to create a Calendar matrix.
(more…)