Today’s #TipTuesday is yet another Excel tip. This one is about navigation, and some tips around things I use every day when I’m working in Excel. Some of these are particularly useful if you are using Excel to get a set of data ready for an integration, which often is the world I’m living in. Sometimes I’ve integrating right from Excel and sometimes I’m working with something where I need to “save as” and I need to make sure I don’t inadvertently have blank rows or columns in my data. That, in a nutshell, is the background for this post and set of tips.
Continue reading “Excel Navigation tips”Tag: Excel
Excel’s hidden gem: IFERROR
Today’s #TipTuesday is yet another Excel tip. Can you tell I’ve been working with spreadsheets a lot lately? My blogs tend to follow my work and these last 3 weeks have been no exception. As I run into things or use something I haven’t touched in a while, I tend to blog about it!
This tip is about what I believe to be a hidden gem in Excel and that is the IFERROR formula. Over the years, I have seen many people do many funky workarounds to “pre-check” if they will get an error when there is a formula that will help you out much more easily!
Continue reading “Excel’s hidden gem: IFERROR”Excel VLOOKUP – numbers as text & vice versa
Have you ever tried a VLOOKUP formula in Excel where you’re looking up something that is numeric but stored as text, or vice versa? So annoying, when you seem to have one set of data one way and the lookup in another, right? Today’s #TipTuesday is all about helping you with a couple of formula tweaks to make this a bit easier.
Continue reading “Excel VLOOKUP – numbers as text & vice versa”Excel tip – “corner” copy
Today’s #TipTuesday post is a little Excel tip. It’s one of those things that I assume most people know it, and then I work wth someone and watch them use Excel and end up teaching it to someone. I call it corner copy but I’m sure there’s some official name!
There are 2 parts to this tip really: one is the copy via double clicking on the lower right corner and the bigger tip is the little pop-up window that gives you options on how that command is executed. That little pop-up window is the *gem* of this tip in my opinion as it appears in numerous instances of doing things in Excel.
Continue reading “Excel tip – “corner” copy”Excel Paste Special – Operations
Today’s #TipTuesday is an Excel tip. Did you know there are some funky Paste Special options beyond the basics like Paste Values, Paste Formats etc.? I find if you were to ask most Excel users what Paste Special is, the majority will answer “pasting values instead of formulas”. While that’s definitely true, there are also Paste Operations.
There are random circumstances where I’ve found this to be useful in a pinch and today was one of them. I was working on some Fixed Assets stuff, and my data had asset cost and accumulated amortization both as positive values. I wanted to do some Pivot Table analysis with it, to validate some Net Book Value info, without fussing with formulas. Paste Special Operations to the rescue!
Continue reading “Excel Paste Special – Operations”Fun with XML, the end
(At least I hope it’s the end, LOL!). Here are the various parts to my mini-series so far, which is the story behind all of this (part 1), the technical deep dive into the SQL query (part 2), a bit about namespaces in the FOR XML (part 3) and finally a bit about validating an XML file against an XSD schema (part 4).
So, what is this post about? This is taking a step back to describe the process itself. The part 1 story tells you what I’m doing, and ultimately I am all about the process. It’s one thing to create some code in order to create an XML file, but ultimately the best solution for an ongoing, repeatable process is to think about the end to end steps from the point of view of the user. This is about the “solution” that I created, which may give you some ideas on either the thought process behind it, or ideas on how to mimic the same thing yourself for a process of your own on something else.
Continue reading “Fun with XML, the end”Excel tip: Copy & Paste Unique Values
Here’s a cool little Excel tip for #TipTuesday. Have you ever had a column of information in Excel and wanted to get a distinct / unique list of values from one of your columns of data? I learned a new thing last week doing just this, and, surprise, it wasn’t “create a pivot table with that column”!
Flash Fill in Excel
Today’s #TipTuesday post is a Microsoft Excel tip, instead of the usual Dynamics GP tips! I do have a tie-in to Dynamics GP on where you could use this in a pinch in GP from an exported Smartlist, at the end of this post!
Have you ever used Flash Fill? It came out in Office 2013 (as far as I can confirm) and I must say, there are a number of different instances where I’ve found this extremely useful in a pinch to pull data out of some cell that contains more than what you need.
Upload Pending issue in Excel with OneDrive
Recently I’ve noticed that every time I open an Excel document from my OneDrive sync folders, I’d be prompted with one of two messages/warnings.
One was “Upload Pending” and the other was “SERVER FILE UPDATED. A newer version is available” with a Reopen Document prompt. This has been happening in nearly every single document I open from my OneDrive folders. It is super annoying!
Continue reading “Upload Pending issue in Excel with OneDrive”
Cool Excel Tip: Dates without holidays included
Have you ever wanted to put a schedule together in Excel with planned dates for something over a period of time, and then manually reviewed the formula, to change dates so they didn’t fall on a holiday?
A client of mine prepares a billing schedule a year in advance where they have to incorporate certain specific holding periods and factor in different holidays of both themselves and their service providers (mostly the same holidays but some different). For example, their data is read by a third party service and it takes so many business days after a scheduled read date to get the data back for billing; then they have a window of so many business days to finish and post the billing cycle etc. All these dates are based on a start date and a factor of so many days – business working days – but excluding holidays.
Continue reading “Cool Excel Tip: Dates without holidays included”