Helping you Excel Yourself with spreadsheets

Extracting Letters and Numbers in Excel

Excel can now use regex patterns in formulas. You might think that the REGEXEXTRACT function would simplify extracting numbers and letters, but it is actually the REGEXREPLACE function that simplifies the process. If you haven't used regex patterns below is a link to is an introductory article I wrote for the CPA Australia INTHEBLACK magazine – it includes a video. https://intheblack.cpaaustralia.com.au/technical-skills/excel-tips-regex-arrives-in-excel Extracting Numbers If you want to extract all the numbers in a code the REGEXREPLACE function provides a solution…

Continue ReadingExtracting Letters and Numbers in Excel

Using SUM and SUMIFS for Multiple Criteria in Excel

SUMIFS is great for summing values based on multiple conditions. But when one of those conditions needs to accept multiple options, such as two or more states, combining SUM with SUMIFS can provide a flexible solution. In the image below we have a simple data table on the left and two yellow input cells on the right and a SUMIFS function in cell G2. Notice that the SUMIFS result spills across to the right, returning one result for each state…

Continue ReadingUsing SUM and SUMIFS for Multiple Criteria in Excel

Fill not working as expected

I was working on another project and tried something that I thought always worked and it didn't. When you drag a cell with a number with the Fill Handle it copies. When you drag a cell with a number but hold the Ctrl key down it increments. Well the Ctrl key didn't work when I had a filter in place in a separate part of the sheet. Has that always been the case? I have never seen the Ctrl key…

Continue ReadingFill not working as expected

The only time to use SUMIF

I no longer teach the SUMIF function. I teach the SUMIFS function as it provides more solutions because it handles multiple criteria. The two functions differ in their argument sequence which can be confusing when switching between them. Rather than learning both, it is easier to learn the SUMIFS function. There is however one time when the SUMIF function is shorter and easier to use than SUMIFS.

(more…)

Continue ReadingThe only time to use SUMIF

Unhiding Sheets is Now Easier in Excel

Not sure when this change happened but it is now much easier to unhide multiple sheets in Excel. In the past you had to use tick boxes to select sheets unhide. On click per sheet. Now you can just use the Ctrl or Shift keys to select multiple sheets to unhide. In the image below you can see that the tick boxes have been removed and just the sheet names are listed. You can hold the Ctrl key down to…

Continue ReadingUnhiding Sheets is Now Easier in Excel

Excel Icon Right Click

Did you know you can right click the Excel icon on the task pane? When you do you can see a list of recent files. In the image below I have right clicked the Excel icon. You can then click one of the file names to open Excel and the file. If Excel is already open it still works. You can open a recent file quickly by right clicking the icon and clicking the name. This technique also works for…

Continue ReadingExcel Icon Right Click

End of content

No more pages to load

Older Posts