- Home
- MS Office
OEE Solution – Querying Various Sheets
In this post we will pull together formulas that we’ve seen in these posts Formula to get first, second and third highest values Formula to get last row number http://blog.thejaytray.com/excels-indirect-function/ Data Validation Lists COUNTIF Function Before continuing, if you are not familiar with the above posts or functions I would recommend reading them. Although I […]
Continue readingPareto Chart In Excel
Although there are Excel add-ons and more recent versions of Excel make it easier, for a lot of people Pareto charts are still awkward to put together. A pareto chart is a type of chart that contains both bars and a line graph. The bars show the individual order of items in descending order and […]
Continue readingFormula to get first second and third highest values
In this post I will show you how to look up the first, second, third and so on values. This formula can also be used to create Pareto charts which I will also go through in the next post. In previous posts I touched on the idea of having a file that could quickly query […]
Continue readingFORMULA TO GET LAST ROW NUMBER
In Excel, you can write a formula to get the last row number containing data. At first, it’s not an obvious formula, but using Excel’s own logic and lookup function can give you the answer. When i was first asked by somebody for a formula to get the last row number I almost immediately saw […]
Continue readingExcel Option Buttons
It’s been a while since I posted about Excel macros, so I think it’s time I touched on this subject again. In this post we will add Option Buttons to a sheet and have that sheet change based on the selected option. With Option Buttons, only one option can be selected at a time. If […]
Continue readingExcels INDIRECT Function
The INDIRECT “function returns the reference specified by a text string”. What does this mean and why would I use it? I have seen people have problems with amending formula to reference different sheets in particular, most of them just recreate the formula rather than trying to amend it. One of the ways around this […]
Continue readingData Validation List Based On Criteria
In earlier posts, we covered data validation list, from basic to more complex rules. In this post, we will create a rule for a Data Validation List that will be dependent on another value. If you want to read back over earlier posts, you can click on the links below. Introduction to Data Validation Lists […]
Continue readingAdvanced Filter in Excel
I have seen a lot of people using the AutoFilter (Filter) in Excel with ease, but I’ve been surprised to see that not many people use the Advanced Filter in Excel which, when dealing with lots of information or applying several filters, can save time and effort. In this post I will quickly go over […]
Continue readingAnimate In Excel 2
In the last post I showed you a macro that would animate in Excel. In this post I will show you another way I animate in Excel. It is a bit less technical than the last and uses the RANDBETWEEN function to change a chart and conditional formatting to change some colors. This is a […]
Continue readingAnimate In Excel
When creating a logo for my next site, I wanted to do it completely in Excel and then I later decided to animate in Excel. The logo itself is relatively straightforward using Excel’s shapes. The image below shows the final logo and also the shapes used to make it. I used Excel’s Drawing Tools to […]
Continue readingData Validation List To Automatically Update Source Range
The last 2 posts looked at some Data Validation options in Excel for either a short list or a set range of cells. This time we will use a formula in our Data Validation rule that will work out what range of cells to base the list on. Data Validation Rule To Update As List […]
Continue readingData Validation List Based On Range Of Cells
In the last post we created a short Data Validation List where we typed in the options for our drop-down list. This time we will create a Data Validation List based on a list in a range of cells. In this example we will create Data Validation Lists to column A and the options in […]
Continue readingData Validation List In Excel Introduction
In the next few posts we will look at Data Validation Lists in Excel. You may have seen these and called them drop-down lists. We will start with the most basic form of a small Data Validation List and then get more advanced. For this post, we will create a Data Validation List based on […]
Continue readingExcel Logo Animation
In 2017 I will be creating my ExcelWithExcel.ie site, in preparation for this I had to design a logo. So, being an Excel geek, I thought why not create the logo in Excel….. so I did. When talking about animating the logo as an introduction to videos, I was challenged to animate the logo in […]
Continue readingExcel Format dates to include st, nd,rd and th
In an eBook “Date Functions”, I showed you a formula that would include st, nd, rd and th to a day number, so 14 November would display as 14th November. I have since been asked by some people if it is possible to format dates to include st, nd, rd and th. The answer is yes, you […]
Continue reading