Download Excel File: https://excelisfun.net/files/EMT1742.xlsx
Learn how to create a Day of Week Totals Sales and Average Sales reports using PivotTable, Sorting, Conditional Formatting, Worksheet Formulas and much more. Learn keyboards, PivotTable, Standard Formulas Continue reading “Day of the Week Sales Report: Helper Column, PivotTable, Formulas, Array Formula? EMT 1742”
Download Excel File: https://excelisfun.net/files/EMT1735.xlsx
Learn how to use Organizational Data Type in Excel to lookup records from an Power BI Online source.
1. (00:00) Introduction to how an Organizational Data Type works.
2. (00:50) Trick Continue reading “Organizational Data Types in Excel M365 & Power BI. Excel Magic Trick 1735”
I encountered a strange issue where I suddenly could not copy or paste in Windows or any of the Office applications (Excel, Access, Word, Outlook, PowerPoint, etc.) while working remotely. It was quite frustrating as I was pressed for time and needed this critical function to keep working. I then had the idea to stop the utility for the clipboard, effectively restarting it, and that worked!
If your clipboard copy/paste is not working on your remote machine, to save you the same time and frustration, here is how I resolved the issue:
By entering this command, you are forcing the shared clipboard to close and restart itself. Hopefully this helps you out!
Did you know that you can view two tabs at once in Excel, side by side for a single Excel workbook/file? This is especially helpful if you are working with a formula that affects another tab, whether checking for accuracy or troubleshooting. It’s also great if you are running what-if scenarios such as in a sensitivity analysis and you would like to keep an eye on the values in two tabs.
Alternatively, if you love keyboard shortcuts like I do, simply use this combination: ALT + W, then N.
Here is an example of how it looks for this sample loan calculator file:
Simple solutions are the best! I truly hope this helped you out! Please consider bookmarking this site and subscribing. I add new content often! Thanks!
Also, check out Dose for Excel (click the image below)! It adds over 100 functions to Excel to increase your productivity and more! Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in. Thanks for supporting my blog!
If I helped you today, please consider leaving a tip via PayPal to help cover blog costs! Thanks!!
Subscribe for more great content!
A common request I get is for ranking of sales in Access by region, state and item combination for analysis and space management. In my business, we have some overlap, where a state can be part of two separate areas or regions, for example Southern CA vs. Northern CA. It’s helpful for us to show separate rankings for each region and state combo, as the results often tell a different story for each.
The Pass-Through query below enables me to determine the ranking at each of these levels. By entering as a Pass-Through query, you can utilize the Rank and Partition SQL commands. After running this query, I bring the data into Excel for further analysis, but this query is a good starting point for any similar analyses.
Here is the full Access pass-through query, color coded for the explanation below:
Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM (Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM (Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Store_monthly_sales m, FR_Site_Master s
WHERE m.Site_num = s.Site_num AND m.accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
WHERE SumSaleQ >5)
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc
Query 1
Diving right in, since we are measuring at multiple levels, we have some nested queries. Working from the inside out, my first nested query is this one:
(Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Franchise.Store_monthly_sales m,Franchise.FR_Site_Master s
WHERE m.Site_num = s.Site_num AND accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
Query 2
We then nest that within this query (the WHERE clause is optional – I am dealing with sales in the thousands, and want to exclude items with a sales quantity less than 5. Note the Rank() over and Partition By Commands here:
(Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM …
WHERE SumSaleQ >5)
Query 3
The final nesting and main query is selecting the rank from the second query, along with the other pertinent attributes. The WHERE clause is again optional, I am looking to limit my results to the top 200 results only for each region and state combination. I use 205 as the figure to allow for ties. The Order by is also optional. I like to order the results by state, region and then rank ascending.
Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM …
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc
Select Region, State, Product_ID, SumSaleQ, StoreCount,Rank
FROM (Select Region, State, Product_ID, SumSaleQ, StoreCount, Rank() over (Partition By state, Region Order by SumSaleQ DESC)as rank
FROM (Select s.Region, s.state, m.Product_ID, sum(m.sale_quantity) as SumSaleQ, count(distinct(m.site_num)) as StoreCount
FROM Store_monthly_sales m, FR_Site_Master s
WHERE m.Site_num = s.Site_num AND m.accrual_month in (‘202007′,’202006′,’202005’)
GROUP BY s.Region,m.Product_ID,s.state)
WHERE SumSaleQ >5)
WHERE rank <205
Order by State ASC, Region Asc, Rank Asc
I hope this post will help you in your efforts to rank in Access. It can be a bit of a challenge, but hopefully the breakdown of the steps here will help you achieve your ranking goals. Please comment any questions, feedback, or what you would like to see next! Share this post if you feel it would help someone else! 🙂
If I helped you, please consider buying me a coffee via PayPal! Thanks!!
I’ve done a fair amount of research around dynamic and parameterized queries, specifically around an Excel query parameter, as in trying to pass a parameter to a SQL query in Power Query. After much trial and error, I finally have found a solution that works. Here’s the quick and easy way to do this and harness the power of query folding. This solution works in Excel – the process is a bit different in Power BI.
Please note that this post assumes you are at least moderately familiar with Power Query and SQL querying. Your mileage may vary depending on your situation.
In my example, “SParameter” is the name of the parameter I am using, and it represents a store number (retail location identifier). The related field in my SQL table is [Store].
Create the parameter:
Create the SQL query:
Build your SQL query as normal, but note that we are not going to put a where clause in referencing the new parameter here (see “Why Not Include the Parameter in the SQL Statement” section below). Here’s my simple query example – notice that I’m not including the store field in the criteria section of the query though this is the field my parameter will ultimately filter on.
Select * FROM Storelist WHERE OpenDate < GetDate()-365
If you think like me, you may be pausing here – my table has millions of records and I don’t want them all pulling in – don’t worry, this is the beauty of the process.
Why Not Include the Parameter in the SQL Statement?
In the SQL statement, logic would lead us to add the store number/SParameter as part of the criteria in the select statement. It may seem counter intuitive, but we cannot successfully put the parameter directly into the SQL code/query. I have tried several times, but the challenge is that you are combining data sources and at this time, this is not allowed due to the built in security (SParameter portion is local, SQL portion is external).
If you are tempted to test this yourself, the format would be Select * FROM Storelist WHERE Open < GetDate()-365 and Store = “&SParameter&”. You will likely run into this error “Formula.Firewall: Query ‘SParameter (2)’ (step ‘Filtered Rows’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”
I truly hope this post helped you out. If so, please feel free to leave a comment below letting me know so, and if you’d like, add what you’d like me to cover in a future post. Also, feel free to share this with someone else who may find it useful.
Please bookmark and subscribe to my blog! I am always curating and adding new, relevant content! Thanks so much!
Also, be sure to check out Dose for Excel (click image below)! Add over 100 functions to Excel to increase your productivity and more! They have a free trial right now, so you can try it out today! Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in. Thanks for supporting my blog!
Today we are going to explore various ways to delete every other row or delete every other column in Excel. VBA macro code is included to help you on your way if that is the path you choose.
Additionally, we will also cover how to delete every third/fourth/fifth row or column in Excel.
To delete alternate rows, you can use a helper column and use a formula that helps you identify alternate rows. This could be done by using the ISEVEN function that checks each row number and returns TRUE if the row is even and false if it isn’t.
Once you have this, you can easily filter the rows with FALSE in the helper columns and delete these.
In case you want to delete every third row, you need to use the MOD function to identify every third row. Once you have it, you can easily filter and delete every third row or every fourth row.
I also cover a method to use a simple VBA code to delete alternate rows in Excel.
Below is that VBA CODE TO DELETE ALTERNATE ROWS:
|
1 2 3 4 5 6 7 8 9 |
Sub Delete_Every_Other_Row() Dim Rng As Range Set Rng = Application.InputBox("Select the Range (Excluding headers)", "Range Selection", Type:=8) For i = Rng.Rows.Count To 1 Step -2 If i Mod 2 = 0 Then Rng.Rows(i).Delete End If Next i End Sub |
When it comes to deleting alternate columns, you cannot filter these. You can instead sort and bring all those columns together that you want to delete.
I cover a simple method that uses the MOD function to identify alternate columns and then sort these from left-to-right to bring these together. Once you have these in one place, you can select and delete these.
And there is also a VBA code that you can use to delete alternate columns.
Below is the VBA CODE TO DELETE ALTERNATE COLUMNS
|
1 2 3 4 5 6 7 8 9 |
Sub Delete_Every_Other_Column() Dim Rng As Range Set Rng = Application.InputBox("Select the Range (Excluding headers)", "Range Selection", Type:=8) For i = Rng.Columns.Count To 1 Step -2 If i Mod 2 = 0 Then Rng.Columns(i).Delete End If Next i End Sub |
Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel
You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/
#Excel #ExcelTips #ExcelTutorial
As the year comes to a close, I feel the need to take a step back and assess my career and job situation. I want to learn from the past year, by recognizing both my successes and failures. One of the best ways to do this is by asking a series of questions and then answering them honestly, even if it’s painful at times. After completing my 2016 career assessment, I found many insights and was able to forge a good plan of action for 2017. I have a list of goals, and now have made a solid plan to achieve them. See this post for the top 10 steps for achieving any goal to get started.
I also have a post and list of questions here for a self assessment of your personal life. Live moves at such a quick pace that sometimes we forget to stop and smell the roses. We slip into autopilot mode, just going through the motions. I know this is how it is for me, at least. It’s truly important to step back and evaluate where we are and what we have achieved. We all have goals that we want to achieve. That doesn’t usual happen when we are on cruise control.
So here’s my 2016 Career Assessment. I genuinely hope that it helps you. Maybe you can even use it as part of a review or career advancement discussion with your boss or supervisor. Maybe it will lead you to pull your resume together and start looking for a new opportunity.
Bonbon’s Guide Career Assessment
Click here for the free printable version!
Click here for the free printable version!
Please feel free to share with others who you think would benefit from it. I would love to hear your feedback, so please comment below!
In an effort to make a little bit of side income, I’ve decided to try my hand at affiliate marketing – specifically with the Big A. I am starting to generate some steady traffic and getting excited about my progress. This is definitely not a method to getting rich quick and easily, despite what many out there will lead you to believe. It is seriously hard work. I have spent quite a bit of money and time advertising, so far.
I’m not a marketing guru by any stretch of the imagination, so it’s all a learning game for me right now. I have sent them about 100 potential customers according to the logs, with three completed sales, but that’s not very much so far.
I have purchased three domain names to work this affiliate method. Two completely different and unrelated niches. I am pulling together some content, with embedded links, and the main focus on two of the sites is catalog style shop pages. I have bought some good plugins and have a great theme. My designs and customization need work, but I am getting positive feedback from any visitors I ended up engaging with.
The most challenging and frustrating issue so far is a few strings of correspondence with people who claimed they ordered one of the items on my website. Three different very legit customers of another website. I confirmed that they definitely did not get to their end purchase via my website by checking my Amazon reports. I hadn’t had a single referral sale on this item. I think these customers found me via Google, since I was ranking high for this particular item. They just assumed that they placed their order through my website, and that it was my job to fix it. It’s very hard to explain to someone who has no clue about internet marketing that you are an independent affiliate marketer with zero ties to the manufacturer of a product. I am just referring people to items I like and recommend on the big A – you can’t even check out on my site because the shopping cart function is not active (no point since there was no commerce!).
Being a person with a strong conscience, I felt compelled to help, despite the fact that I had no involvement. I’m also concerned about the website’s reputation, being brand new to this internet marketing world.
I started working in depth with the nicest one, and she gave me a copy of her invoice. It turns out she did in fact complete an order, but on the manufacturer’s website. Their customer service department was pretty much non-existent. I jumped through hoops to track down information about the company and after figuring out who to contact, I emailed the customer service department several times with no response. Unbelievable. They had a notice on their website that the item was back-ordered. It was a toy and I’m pretty sure that these people ordered for Christmas gifts. It’s a shame that the company let this happen.
I did what I could, but my hands were tied after those attempted communications with the shady toy manufacturer. I left off on a positive note with each of the customers, and have not heard from any in a few weeks. Hopefully it is over with. Scary stuff.
The lesson I’ve learned is that I need to do some more due diligence with any products I’m recommending. Check out the vendor thoroughly and make sure I’m willing to stand behind them.
I hope I will not be providing customer service to too many people for free anymore. I honestly do love to help people and am great with customer service, but it’s not fair when I have no ties to the sale, whatsoever.
It’s got me thinking this may not be the best internet marketing model for me, so I’m at the drawing board now. I am going to try my hand at the drop shipping/drop ship model. I’m currently at the stage where I’m looking for fulfillment companies. It has been grueling! So many companies, so many directories, so many differing opinions. I have spent hours looking around and haven’t landed on any one that I am fully satisfied with. I’ve got one that I’m feeling decent about and now it’s just a matter of deciding whether or not I am ready to jump in and take a chance. I will be posting updates about this foray as an affiliate marketer, so stay tuned!
If you have any advice and you are experienced in either drop shipping or affiliate marketing, please feel free to share in the comments below. Thanks!
Here we are at the end of 2016. It’s amazing how time flies and every year this time seems to sneak up on us. They say it’s because time flies when you’re having fun – but I feel time also flies when you’re too busy to notice. That’s the truth for me. Don’t get me wrong – I have plenty of fun and life is good, but I don’t think I pause often enough.
Before ringing in 2017 with a list of resolutions I may end up breaking, I think for me it’s much more valuable to look back at 2016 and assess the year – to do a year in review. I hope you will also pause and reflect on this past year. These are the questions I am asking of myself. I hope you find them to be valuable.
Bonbon’s 2016 Self Assessment
Don’t forget to be honest with yourself. 🙂
For me there were many ups and downs, but they just serve to motivate me going forward. Here’s to a happy, healthy, prosperous and joyful 2017!
I’ve also added a free printable Career Assessment here.
We are all guilty (well, I am, at least) of setting goals or making plans and failing at them. Often times it’s the failure to prepare a good road map. It’s equally important to have a strong commitment and to be willing to do anything it takes to continue working towards the goal, even in the face of perceived failure.
In this post, I’ll be sharing the top 10 steps for achieving any goal. It all starts in the planning phase. If you just haphazardly work towards a goal with no real plan in place, you certainly may get there, but it’s going to be a very rocky road the whole way there. Conversely, it’s definitely possible to be very successful with good planning, hard work and determination. You must stay focused on the goal and continually check in to evaluate your progress, especially when it’s a goal that will take a while to complete.
Top 10 Steps for Achieving Any Goal
Check out my first beauty video below. It’s a two in one, with a best makeup for dry skin review and a tutorial!. I’ve been using this combination of products for about three months and I am thrilled to share it with you. There’s honestly no need to break the bank with expensive everyday products. Personally I’d rather splurge on a night cream for wrinkles and some special masks/peels.
Just the other day I watched a Today show segment where they featured twin teen girls. They applied drugstore products only on one twin, and then luxury/premium makeup on the other girl. They then brought the girls outside and interviewed people on the street, asking who they thought looked better. The girl wearing the drugstore makeup was chosen almost every time! It was honestly not a big surprise to me.
I feel a little goes a long way, so I don’t normally where much makeup to begin with. I’m pretty frugal and love to save money, so most of my makeup either came from the drugstore, while the rest of the items were part of a gift or holiday set, or an occasional splurge (I love MAC eye shadows)! In this video, I cover items you can pick up relatively inexpensively, either at the drugstore, or online.
So I must forewarn you – I had a little blooper in the video. I didn’t really plan it out – the video was shot in one take with no cuts or anything. I applied the concealer and then realized I still had two more products to review before moving to the next step. I look pretty hilarious with my un-blended concealer on for what felt like 5 minutes! Enjoy!
&nbs
I hope that you will share what works for you if you use other products. If you end up using these, I’d love to hear your feedback. Please comment below!
E.l.f. Hydrating Serum
