There are a number of mouse and keyboard shortcuts for copying. But there is one type of copy that can be frustrating. Copying dates can be challenging because, in general, Excel wants to increment them, not copy them. There is a simple technique to instruct Excel to copy a date. (more…)
You learn something new every day. Today I discovered that you can have columns within a text box. If you right click the text box and choose Format Shape the Task Pane below should open on the right. Click the third icon (Size and Properties) at the top and then open up the Text Box options. The Columns button allows you to specify how many columns plus the gap between them. Have fun.
Excel 2016 has introduced a new type of IF function to simplify handling multiple conditions. It is called IFS. (more…)
Woohoo - my book made it to 3 on the top ten e-books in the CPA Library.
Some systems add DR and CR to the end of numbers when they export into Excel. This renders the values useless for normal calculations. You can use data cleansing techniques to remove the characters using formulas or Power Query. There is one function however that can perform calculations on these types of entries. (more…)
Charts have a behaviour that many people don’t realise. That behaviour can also be turned off. If you hide a row or column in the data range used by a chart, the values will also be hidden on the chart. (more…)
When clearing page breaks in Excel VBA you need to be careful. There is one command that will clear page breaks but it will also affect other print settings.
(more…)
When creating ranges of formulas that you want to copy down, you sometimes have a trade off in the use of fixed and relative references. If you need to create a relative reference that acts like a fixed reference you can use a trick. (more…)
Filtering coming soon to the Data view in Power BI. Fingers crossed this gets done soon - patience everyone.
You can create complex functions with Excel to handle dates. But it makes more sense to get your data structured correctly and then you can use simpler date formulas. Power Query allows you to fix your data so that you can use those simpler formulas. (more…)
In the structure below let's assume you want to copy the SUM formula from cell B5 to cell C5. Obviously you could use copy and paste, but that would require a few keyboard presses or mouse clicks. Given that we already have cell C5 selected we can use Ctrl + Shift + > This copies whatever is in the cell on the left to the current cell.
Excel has a shortcut that allows you to follow a link to its source but it has a limitation find out how to get around that limitation. (more…)
When data is imported into Excel sometimes the values come in as text rather than values. Most functions can’t perform any calculations with text numbers, but one can. See how easy it is to add up text values. (more…)
I recently found this keyboard shortcut for MS Word to increase the font size of the selected word(s). To reduce the font size use
Privacy settings allow you to control who sees the Power Query data. There seems to be a bug that remembers your response to a dialog and this ignores any changes to the Privacy settings. Find out the VBA line of code that can fix it.
(more…)Some articles make a greater impression than others. I was recently asked about a magazine article from August 2011. Someone had changed jobs and needed to be reminded of the rolling 13 month technique. The congratulations relate to 15 years of Excel Yourself!
Formatted Tables are great but there is an issue when it comes to copying formula that use the table names (Structured References). There are two techniques that cope with this limitation. (more…)
In a Linkedin Excel Group recently there was a discussion about whether or not you should use the Dim statement to declare your variables. The argument was that you don’t have to and someone had managed to successfully create some code without declaring variables.
(more…)
A drop down list in Excel can help speed up data input and ensure the user has entered a valid entry. If you have only a few choices, creating the drop down can be a quick process. (more…)
Converting multiple text numbers into real numbers or reversing the sign on multiple numbers is easy in Excel if you know how to use Paste Special. (more…)
Hyperlinks are a great way to navigate around complex spreadsheets. Most times when you create a hyperlink you link to a single cell within the sheet. In some cases there is a good reason to link to a range. (more…)
Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently there is an import routine that requires it. My normal solution is a macro because it automates the whole process but there is a manual technique that is quick and easy. (more…)
Why would you purposely open a file as read-only? If you regularly open files from last month and then save them as this months’ version then read-only is your friend – see how. (more…)
Looks like Power BI will soon be able to extract data from tables in PDF documents. Great to see requests being acted on.
I recently helped a client reduce the size of an Excel file. The file took a while to save which was frustrating and time consuming. I thought I would share this reasonably easy solution. (more…)
If you want to filter by blanks across multiple columns the standard Filter feature can’t help you. You can use the Advanced Filter but that takes time to set up and most users don’t know how to use Advanced Filter. (more…)
Make your headings bold. This tip applies to tables and to the structures you use for charts. Excel looks for the bold format when it reviews tables and layouts to figure out if your table has a headings row. You can use Ctrl + Shift + L to add or remove the filter icons to a data table. There is also an icon on Data ribbon tab. This will work more reliably if the headings are bold. I use the…
I have found a keyboard shortcut combination to one on my favourite right click options and its quicker to use. (more…)
Unfortunately lots of people use the Merge & Center format in their spreadsheets. When working with other people’s files that contain Merged cells I will often remove the Merged cells format and apply Center Across Selection which is the preferred format to use. The macro below will convert Merged cells to Center Across Selection.
(more…)A trick to stay in the cell you are editing is to hold the Ctrl key down when you press Enter.