#learn vlookup my way

[intosnarkness]

i made a google sheet a work that took rsvps from our instructors who are agreeing to teach this year out of the system where they RSVPed

then it calculates a unique ID for them based on name and local union

then there are sheets broken out by course number (because each first number has a different person coordinating those instructors) and filled with the info we used to contact them. these generate the same unique ID from their name and local union

then i used if(iserror(vlookup script to pull over their rsvp, the email they entered, and any questions or concerns they had

and then i used conditional formatting to highlight anyone whose entered email didn't match the one we sent their invite to so we can update it in our system

and my boss took one look at this and said "you know no one else in our department can do anything even approaching this?"

so i texted my dad to thank him for teaching me how to do vlookups.

sincerely, if you are starting out in your career, learn how to make excel do a tiny bit of magic and you will go so so very far.

learn how to do vlookup, xlookup, if, iserror, countif, and sum and you'll be most of the way there. conditional formatting is annoying, but useful. pivot tables, if used correctly, might get you referred to the spanish inquisition. and VBA scripts! you can do so much stuff automatically with VBA. I use it every year to break our course evaluation master into individual sheets based on course and instructor. takes me 20 minutes instead of 3 weeks it would take doing it by hand.

you can get a lot of pre-written VBA code online as well, so as long as you know how to activate the developer console, you'll be fine. it's nowhere near as scary as you think it's gonna be.

learn excel. learn spreadsheets. you'll thank me for it.

[ghostofbambifanfiction]

Anyway now that everyone's confused, have a chapter 49 snippet

Remus Lupin: Peter's asking me why he hasn't been added back to the group chat yet.

Sirius Black added Peter Pettigrew

Sirius Black: Stop fucking leaving then

Peter Pettigrew: You were supposed to add me back right after I left!

James Potter: but every time you leave the chat it becomes harder to forgive harder to forget harder to reconcile especially when i later come to you for help and you cruelly rebuff me

Remus Lupin: What are you on about?

Peter Pettigrew: James asked me to teach him the full functionality of Excel yesterday and I said no.

Remus Lupin: Why did you need to know the full functionality of Excel?

Peter Pettigrew: Some Lily related reason, probably.

James Potter: first of all peter if you're going to keep exiting the group chat in a huff you may as well vlookup new friends we four are meant to be a merged cell and i've had it with you exporting yourself elsewhere then deciding to pivot table your way back in

Peter Pettigrew: ......WHAT?

James Potter: see? i can learn excel all by myself

Remus Lupin: You've clearly just Googled a glossary of Excel terms.

James Potter: a full understanding of the terminology is a vital step towards excel proficiency my friend

Sirius Black: I'd love to take the piss too but he is actually using Excel for a serious reason

James Potter: that's right and now that i know how to wrap text and expand column widths it's over for you people

[hunxi-after-hours]

paper anon coming back!!! because of some schedule issues, my first final exams are in two weeks - from written chinese and spoken chinese and honestly i am not that stressed about it :D uhhh. all i do is memorize vocab. i dream about flashcards. it's horrible but the satisfaction of being able to understnad / know a lot of words >> i think by now i know around 220ish? there's official vocab list and then there's just stuff we learn by convos 1/2

paper anon 2/2 anyway! phonetics. phonetics are the bane of my existence. we do dictation from pinyin table and no matter if i feel super confident or like i failed, i always get 80% lmaoo. we have cultural geography but our prof is fucking whack which is annoying because it sounds like a rly good class :(( either way, my life is composed of vocab, radicals and constantly drilling pinyin listening practice lol. hope you're doing well!!!! <333333 enjoy incoming holidays

aw hey hello paper anon! look at you go!! two hundred words is already a Whole Lot, it's no wonder your dreams are full of flash cards. phonetics strike unending fear into my heart so I wish you the best of luck with your dictation tables. I was locked in mortal combat with a vlookup formula in one of my tables the other day at work and I nearly lost all hope because of it, and that didn't even involve pinyin OR dictation

ooooh mark me deeply curious as to what "cultural geography" entails! it sounds neat but also like a subject that a Whack Professor could ruin, alas

good luck on your upcoming final exams!!! and hopefully soon after that, you'll be able to rest and take a break from dreaming in flash cards!!

[login360seo]

How I Got My First Job as a Data Analyst: A Personal Journey

1. Building the Foundation

Before I could even think about applying for data analyst roles, I had to build a strong foundation in the required skills. I started by mastering Excel, a tool that is fundamental for data analysis. While Excel may seem basic, it’s surprisingly powerful, especially with its advanced functions like PivotTables, VLOOKUP, and macros.

From there, I moved on to learning SQL, which is essential for querying and managing databases. SQL skills are a must for almost every data analyst role, as they allow you to pull and manipulate large datasets. I took free online courses to learn the syntax and practiced writing queries to build confidence.

The next step was to get familiar with Python, which is a versatile language widely used in data analytics and machine learning. Learning Python not only gave me the ability to analyze data more efficiently, but also introduced me to libraries like Pandas and Matplotlib, which made it easier to clean and visualize data.

2. Gaining Practical Experience

While learning theoretical concepts is important, nothing beats hands-on experience. To gain practical skills, I started working on small projects. I found datasets online, from platforms like Kaggle and UCI Machine Learning Repository, and started exploring them. One of my first projects involved analyzing customer data to understand purchasing behavior and generate insights for marketing strategies.

I created dashboards using Tableau and Power BI to present my findings in a clear, visually engaging way. Visualization skills are highly valued by employers, as they allow you to communicate complex data insights in a simple, understandable manner. I shared these projects on my GitHub portfolio and LinkedIn profile to showcase my work.

3. Networking and Learning from Others

In addition to building technical skills, I knew I had to network to get my foot in the door. I joined data analytics communities, both online and offline, and attended webinars and meetups. These events helped me connect with professionals already working in the field, allowing me to ask questions and learn about their experiences.

I also reached out to data analysts on LinkedIn for informational interviews. These conversations were invaluable in giving me a better understanding of the day-to-day responsibilities of a data analyst and what employers look for in candidates. I made sure to ask for advice on how to break into the industry and the best ways to tailor my resume.

4. Tailoring My Resume and Applying for Jobs

When it came time to apply for jobs, I knew I had to stand out. I tailored my resume for each position by highlighting my skills in data analysis, SQL, Excel, Python, and visualization tools. I also made sure to include my personal projects and the impact they could have on business decisions, emphasizing my ability to generate actionable insights from data.

I applied to a wide range of positions, from internships to entry-level data analyst roles. To gain more exposure, I didn’t limit myself to just large companies—I applied to smaller firms, startups, and even remote opportunities. I focused on companies where I believed I could grow my skills and gain valuable experience.

5. Nailing the Interview

After several applications, I finally landed an interview. The company was a mid-sized marketing firm looking for a junior data analyst to help with customer segmentation and campaign analysis. During the interview, I was asked technical questions related to SQL queries, data cleaning, and data visualization. I was also given a small case study where I had to analyze a dataset and present insights to the team.

Thanks to my preparation and practical experience, I felt confident in tackling these challenges. I focused on explaining my thought process clearly, breaking down how I approached the data, cleaned it, and generated insights. I also highlighted my experience with personal projects, which gave me real-world examples to discuss.

6. The Offer and Beyond

A week after the interview, I received an offer! It was an incredibly rewarding moment, and all the hard work finally paid off. In my new role, I applied the skills I had learned, but more importantly, I continued to grow and learn from my colleagues. The learning never stops in data analytics, and every project is an opportunity to refine your techniques.

Conclusion

Landing my first job as a data analyst was the result of a combination of strategic skill-building, hands-on experience, networking, and persistence. By focusing on mastering key tools like Excel, SQL, and Python, and showcasing my abilities through personal projects, I was able to demonstrate my value to potential employers. The journey taught me that with determination, continuous learning, and the right mindset, breaking into the field of data analytics is entirely achievable. This first step has set the foundation for a fulfilling career, and I’m excited to keep growing in this dynamic industry.

[jackrobinson123]

Advanced Tips for Using Excel Estimating Software

When it comes to estimating for residential construction, Excel estimating software is a powerful tool that can save you time and improve your accuracy. With the right techniques, Excel can change the way you do estimates and it should be a key part of your project planning. Let’s get into some advanced tips to help you get the most out of Excel estimating software.

Customizing Templates for Each Project

First off, customizing templates for each project is key to mastering Excel estimating software. Every project is different and a one size fits all template just won’t cut it.

Why Customize? Customized templates allow you to account for project specific variables whether you’re working on a cozy bungalow or a sprawling mansion. I once had a one size fits all template almost cost me big time on a custom home for a client. By customizing my template I caught the error early and avoided a major mistake.

Steps to Customize:

Start with a Base Template: Begin with a standard template that has common line items.

Modify for Project Specifics: Add or remove items based on the project’s unique requirements.

Include Client Preferences: Make space for client specific details that impact costs.

For more on how to customize your estimating process check out our construction estimating software guide.

Using Advanced Formulas and Functions

Excel estimating software is powerful because of its ability to automate calculations with advanced formulas and functions. This saves time and reduces errors.

Key Formulas to Know:

SUMIFS: To sum costs based on multiple criteria.

VLOOKUP: To pull data from other sheets in your workbook.

IF Statements: For conditional calculations that adjust to project changes.

Using these formulas can feel like having an extra set of hands on the job. I once used a combination of these functions to simplify a complex remodel estimate and cut the time in half. That gave me more time to focus on other project tasks.

Data Validation and Error Checking

Errors in estimates can lead to budget overruns and unhappy clients. Data validation in Excel estimating software can help prevent mistakes before they happen.

Why Validate Data? Data accuracy protects your pocketbook and client confidence. I learned this the hard way early in my career when a simple data entry error cost me big time. Since then I always validate my data.

Techniques for Error Checking:

Conditional Formatting: Highlight cells that don’t meet the criteria.

Error Checking Rules: Use Excel’s built-in rules to automatically flag potential issues.

With these tools you can keep your estimates accurate and your projects on track. For more on accuracy check out our blog on construction budgeting.

Visualizing Data with Charts and Graphs

Visual aids like charts and graphs can make complex data easier to understand for you and your clients. Excel estimating software has many options for data visualization.

Benefits of Visualization:

Simplifies Complex Data: Turn rows of numbers into visuals.

Enhances Client Presentations: Helps clients understand the estimate and its components.

Creating Good Visuals:

Choose the Right Chart: Pie charts for cost distribution and bar charts for comparing estimates to budget.

Keep it Simple: Clarity over complexity.

I once used a series of charts in a presentation to show a client where their budget was being allocated and it completely changed their perspective on the project. They felt more confident and informed and it made the project approval process much smoother.

Integrating Excel with Other Software Tools

Integrating Excel estimating software with other tools can add functionality and streamline your workflow. This is especially useful for linking your estimates to accounting and project management software.

Why Integrate? Integration reduces duplication of effort and potential errors. It’s like having all your tools working together in harmony.

How to Integrate:

With Accounting Software: Link Excel to QuickBooks for financial management.

With Project Management Tools: Use integrations to update project timelines based on estimate changes.

This has been a game-changer for my business, I can manage multiple aspects of a project from one platform. Check out our product page to see how Bolster can help with seamless integrations.

Continuous Learning and Skill Development

Excel is always evolving and so should you. Stay up to date with the latest features so you get the most out of your Excel estimating software.

Why Learn? Continuous improvement in your skills means better efficiency and accuracy in your work. I make it a point to take an Excel course every year and it always pays off.

Resources for Learning:

Online Courses: Coursera and LinkedIn Learning have great Excel courses.

Excel Forums: Join communities to share tips and solve challenges.

So get learning and keep your estimating skills sharp.

Conclusion

Excel estimating software is about more than just entering numbers into a spreadsheet. By customizing templates, using advanced formulas, ensuring data accuracy, visualizing data, integrating with other tools and continuous learning you can transform your estimating process and your business. Try out these tips and see how they work for you. Comment below or connect with Bolster on social media 🏠️👋️

[w-ht-w]

skills for public sector + government consulting

PrepLounge:

You'll work with 3 tools where you need to quickly develop proficiancy (know all the key function and work fast) - Excel : function to know vlookup(), index(match(..)), sumproduct(), sumif() . In addition you may learn to work with the keyboard only, this allow higher productivité than using the mouse pointer. You will also need to develop your own rules to always check your results and make sure your calculation are right on the first time - Powerpoint : alignements, formats. In creating content you'll need to make sure you use parallel phrasing - Emails : sharp wording, direct and clear phrasing, easy reading ... In addition to the basics (Excel, PowerPoint), a number of tools are being increasingly used. While a basic knowledge of them is not expected for these (whereas everyone is expected to be able to use Excel and PPT), having these skills could definitely help you shine in certain cases: 1) Tableau / Power BI User: Similar data visualisation tools, these are becoming increasingly used to show data dynamically and make powerful dashboards. It can also be used as an alternative to Pivot tables / Pivot Charts - i.e. to quickly play with data and get some early insights 2) Alteryx: More powerful analytical tool to do more complex analysis, on larger data sets, than Excel. 3) Python: My understanding is that while Python is more powerful, it is also much less user friendly (especially to people without a programming background). So Alteryx fulfils most of the functionality required for consulting, but in a way that is much easier to learn and explain. That being said, I know that some firms (e.g. Oliver Wyman) are actually using Python nowadays and making all junior consultants learn how to use it. 4) SQL: I have not seen SQL being used at Bain, but I know that it is used at some other firms to manage particularly large data sets (e.g. government data sometimes with hundreds of millions of entries). Having knowledge of additional tools (e.g. R) could be useful for you, but if you aren't able to share this with your colleagues, it is unlikely you will use it much as a consulting generalist. On the other hand, this would enable you to work with the analytics teams much more closely, so could be seen as an advantage and also enable you to potentially do a secondment into the advanced-analytics team (if your firm has one) ... Excel and Power Pointare the main apps. More and more projects nowadays require Tableau. I believe being good with SQL, R, etc will help you stand out. ... In McKinsey, we used Excel 99.5% of the cases -the Advance Analytics hub took care of the more difficult stuff-.  In Amazon however, people regularly know SQL on top of Excel.

ChatGPT: "What hard skills and technical skills are needed for public sector and government consulting?"

1. *Policy Analysis:* Understanding and analyzing policies and regulations is crucial for offering informed advice. 2. *Data Analysis:* Proficiency in data interpretation, statistical analysis, and using tools like Excel, Python, or R for data analytics is valuable. 3. *Project Management:* Skills in managing complex projects, timelines, and resources are essential for executing government consulting projects effectively. 4. *Financial Acumen:* Knowledge of budgeting, financial modeling, and cost-benefit analysis helps in evaluating and recommending strategies. 5. *Legal and Regulatory Compliance:* Understanding laws, compliance issues, and legal frameworks is vital when working within the public sector. 6. *IT and Technology Skills:* Familiarity with tech tools, databases, and emerging technologies used in government operations enhances consulting efficiency. 7. *Communication Skills:* Strong written and verbal communication skills are crucial for conveying complex ideas to diverse stakeholders. These skills, combined with an understanding of public policy, political landscape, and strategic thinking, form a robust foundation for success in public sector and government consulting.

Example job description (2,

Provide analysis and recommendations to government clients on a variety of issues in the public sector, including budgeting, policymaking, and program evaluation

Conduct research on behalf of government clients and prepare reports with findings and recommendations

Serve as a liaison between government clients and other stakeholders, such as private sector businesses, nonprofits, and community organizations

Attend and participate in public meetings, hearings, and conferences related to government clients' needs and interests

Develop and maintain relationships with key contacts in the public sector

Evidence-based policymaking

[elevatadata]

How can I use chatgbt improve my excel data processing?

You can use ChatGPT to improve your Excel data processing in various ways:

1. Formulas and Functions: Ask ChatGPT for help with Excel formulas and functions. Describe your data manipulation needs, and it can provide you with the necessary formulas or explain how to use functions like VLOOKUP, SUMIF, or INDEX-MATCH.

2. Data Cleaning: Seek assistance in cleaning and formatting data. ChatGPT can suggest techniques to remove duplicates, handle missing values, or format data for analysis.

3. Pivot Tables: Learn how to create and use pivot tables effectively for summarizing and analyzing your data. ChatGPT can guide you through the process.

4. Charts and Graphs: Request tips on creating various types of charts and graphs to visualize your data. ChatGPT can help you choose the right chart and customize it for your needs.

5. Data Analysis: If you have specific data analysis tasks, describe them to ChatGPT, and it can provide step-by-step instructions or suggest relevant Excel add-ins.

6. Macros and Automation: Explore ways to automate repetitive tasks using Excel macros. ChatGPT can offer guidance on writing VBA (Visual Basic for Applications) code.

7. Data Validation: Learn how to set up data validation rules to ensure data integrity and prevent errors in your spreadsheets.

8. Conditional Formatting: Get advice on applying conditional formatting rules to highlight important data points or trends in your Excel worksheets.

9. Collaboration: If you collaborate on Excel files, ChatGPT can suggest best practices for sharing and reviewing documents, including using Excel's built-in collaboration features.

10. Troubleshooting: When you encounter errors or issues in your Excel files, describe the problem to ChatGPT, and it can help you diagnose and resolve them.

Remember to provide specific details about your Excel tasks or questions, and ChatGPT will provide tailored assistance to enhance your data processing skills.

[krakenartificer]

Hi Raina! I'd like to appeal to your hard-won wisdom on The Adult World-- do you think it's possible to teach yourself/find online resources to help learn things like data analysis? I want to look at numbers and graphs and spreadsheets for money but don't know what resources are good enough to trick capitalism and bosses into hiring me for it!

Oh, yeah, 100% Source: I pretty much did that. So first the disclaimers: I did have a bachelor's degree in applied mathematics, and I did shell out for an MBA as my happy-divorce-day present to myself. I know that I really don't use either of those things in my day-to-day work, but hiring managers probably are considering them when they look at my resume. I'm also white, a native English speaker, and talk like an educated middle-class suburbian, which I'm sure also play into mangers' willingness to give me the benefit of the doubt. So my exact path may not work for you. That said, data, in particular, has several advantages right now:

1) Demand is large, and supply is small. If my department doubled in size, we could still not quite answer all of the questions the business leaders are asking of us. I don't think there's been a moment that I've worked for this company that we didn't have a least one job slot we were hiring for. In addition to making this a lucrative industry, it also makes it fairly easy to break into, because hiring managers are willing to accept far less perfect candidates if only they can get someone who knows something working on this project.

2) The field is changing very fast. No one knows what languages or software we'll be using a year from now, so it doesn't really matter that much if you don't know the one we happen to be using right now. Supervisors are much more concerned with "When it turns out we have to switch our entire reporting scheme to Scala, will you be able to learn that?" And in that context, it is incredibly encouraging to hear a candidate say that they once had to do a thing in Python but they didn't know Python so they Googled and perused Stack Overflow until they could do the thing. You are very likely to have a question at the interview that's something like "Describe a time you had to learn something quickly" or "What's your approach when you don't know how to do something?", and as an autodidact, you will have lots of examples for those moments.

3) There are lots of places that don't have anything at all. A person who knows how to put conditional formatting on a column in Excel would be an improvement on what some smaller companies are currently doing. If you can make a graph and code a vlookup() function, then you're an Expert!

4) the field is so new, and is changing so fast, we're still working out the distinctions between the assorted sub-fields. Which means you can start as someone who does data visualization, pivot to data science, change your mind and end up in data engineering, and then decide to do database administration instead.

So yeah. My recommendation is to search job boards for things that look like they might be what you want to do, and write down what the minimum qualifications are for each one. If you already meet 70% of the requirements, start applying! If not, make a li'l histogram of requirements they want and you don't have, and start finding ways to get them.

I, for instance, downloaded an SQL syllabus from some university class that had it publicly posted, and learned SQL by just doing the assignments on my own time when things were slow at the retail job I had. I got an office job on the strength of that, buuuuut my first assignment wasn't really doable in SQL, so I did the work in Python (a language that, up until that point, I had made 0 programs in, but I had watched while someone else made a program in it), and then bodged it into Excel for visualization. That made me look enough like a developer that the data science team was willing to talk to me, and so I got to sit in on the Alteryx intro seminar when they did, and then (since Alteryx was brand new and didn't really have any documentation or communities at that point) taught myself how to use it by trial-and-error. That got me enough experience that I subsequently got offered a job paying twice as much, working with BASH, hql, scala, and Jenkins (a list of coding options that -- you'll notice -- I had not yet had any experience in).

Basically, as a rule, hiring managers have no idea what all is going on behind the scenes, nor do they care, as long as they get the intended outcome. So my approach for interviews is to approach it as communication/translation problem for the first half: what exactly are they hoping the person in this position will be doing? "So, for example, {possible project based on my understanding of how they described the job}, would that be the type of thing?" Repeat until you're pretty sure you know what they're looking for. If you can do that thing, then you're justified in saying "I can do that", and you probably have evidence to back it up. So the second half of the interview is using their questions as an opportunity to lay out your evidence. Bonus: asking questions in the interview makes you look both smarter and more engaged!

If you get a technical interview/whiteboarding interview, don't panic! They're looking more at how you approach the problem than they're looking at your actual ability to write solid code / know the exact names for everything (my last interview I had to ask "What's that?" after, like, 3/4 of the questions. Then the interviewer would start describing it, and I'd be like "OH yeah, so then ....". I got the job.) So if you don't know what to do, start writing out outlines, mind-maps, lists ... whatever would help you figure out how to get started. Write down the facts of the situation, and implications of those facts; write down questions you have, and how to get them answered. This is a situation where partial credit is very VERY much a possibility, so get as many possible partial-credit sources on the whiteboard as possible.

So yeah. Coursera, Khan Academy, etc, all great. You can also just find some school that doesn't password-protect their class materials, and if you can mess around enough to solve the problems on the homework assignments, then you know (at least) as much as anyone who's officially taken that class. Alteryx and Tableau offer free online training with a web-portal sample of their software. You could also check for volunteer opportunities: I'm organizing permit applications for Sierra Club, and I bet there's a non-profit near you that would be equally delighted (read: fucking overjoyed) to let you take over all graph/numbers/spreadsheets for their projects. Then you get them to write you a letter of recommendation, and put the reporting work on your resume, and you've got "real world experience" while you're saving the world.

I wish you the best, and feel free to ask more questions as you go farther along your journey! I definitely recommend the data-work life; it's been my favorite career so far.

[studyburst]

I LOVE Excel! I took an Excel class during University and I've never stopped using or learning about it since. During my summer internship, I would literally use Excel functions and macros for hours every single day. Even if you don't want to be an Excel fanatic, learning a few basic functions will make your life easier (and even impress a few managers along the way!).

I've outlined below some of the most useful Excel functions to learn. Some of these functions are very basic but can make a huge difference. These functions save time, automate procedures, and make your life easier! And if you need some help on how to learn them, I've listed some helpful resources to get you started. With all of us social distancing at home, now is as good of a time as any to add a skill to your resume. 

Excel Functions to Know:

SUM: returns sum of cells selected

SUMPRODUCT: multiplies ranges or arrays together and returns the sum of product

IF: return one value for a TRUE result and another for a FALSE result

SUMIF: returns the sum of cells if the cell meets a single condition

AVERAGEIF: computes the average of the numbers in a range that meets a certain criteria

VLOOKUP: lookup and retrieve data from a specific column in a table can be exact or approximate match

LEFT: returns a given number of characters from the left side of text string

RIGHT: returns a given number of characters from the right side of text string

CONCATENATE: joins the text of cells together and returns the joined text in one cell

LENGTH: returns the given number of characters in a text

Resources:

Excel Jet: Very useful as a type of Excel dictionary to look up syntax and specific formulas you don't know

Excel Easy: almost like on online textbook for Excel (great resource for those who have no knowledge of Excel at all!)

Udemy: paid excel course with lots of videos, assignments, and downloadable resources

Coursera: self-paced learning option, quizzes and assignments, and a course certificate

Excel Youtube Course by Technology for Teachers and Students

Datacamp (7/12/20 edit thanks anon for the suggestion!)

Prologue to my Excel series

I'll also be posting Excel tips, tricks, function explanations, and some basic Excel tutorials so if you'd like to see more appear on your dash in the future, follow my blog and look for my Excel series.

[captawesomesauce]

Thoughts at 133pm...

I learn by doing.. and failing... and trying again... and doing...

A good way for me to learn is for me not to work on someone else’s projects or data, like a class/tutorial... but to be faced with my own project and try and work out the logic, steps, and tools. 

So I decided it’s high time I make a spreadsheet of all of my academic honors and certificates and stuff, because I keep having to send tailored lists to different groups. 

What I realized is that the best way of going about this is to make a master list. Just a single sheet with everything added on it. 

From there I wanted custom-tailored sheets but I wanted it to auto-populate key info!

For example:  American Red Cross roles require you to take required and recommended classes. So I have boxes for each role, and the classes listed below. Next to the classes it auto-populates the date I took the class, and at the top it tells me how many I still need to take or if it’s complete, and then it fills a 2nd sheet with a list of just the roles, and whether they’re complete or not. 

As long as I edit the main sheet, it automatically fills in the rest. 

I had to learn a bit of VLOOKUP and some other coding, especially conditional formatting for the IF statements to get the coloring and stuff just right. 

Another one is a Custom lookup. Say I just want my academic stuff? I type in Academic and it fills this new sheet with just those ones. Or say I want PIO? same...Type in PIO and I know have all of the PIO rows. 

This time I had to learn the FILTER command and after a little trial and error, I figured out the logic and syntax and it works great!

I’ve always said this, the older you get, the more your life is run by spreadsheet!!!! You know you’re an adult when your budget, and your this and your that... everything’s on a spreadsheet!

[begedil]

Internship Tips & Advice

Alhamdulillah, finally found the time to sit and write. Been a crazy busy, super fun, extremely tiring, definitely rewarding 22-week internship experience for me and now I’m back at school. I’ve actually been wanting to pen this down for the longest time but man, working life is tough. It’s been full throttle since week 3 so alhamdulillah for the 6 days of leave. That being said, I know we all have things so I won’t beat around the bush. Here’s some tips and advice for my friends who already started their internships this semester.

Pre-internship (and it’s first few weeks):

-       Advice #1: Get a planner.

Things are going to get hectic and it’ll be (really) hard to rely solely on memory to know your upcoming events. Personally, I prefer planners with a monthly-view as it provides me with a better gauge of how much work is coming up and how busy I’m gonna be (also because I lack the discipline to fill up a daily/weekly calendar). To avoid getting overwhelmed, I don’t include work meetings in my planner, I only indicate the tasks I need to complete and things I have after work.

-       Advice #2: Work out your time ratio.

Figure out who and what you want to make time for. Yourself, your family, your friends, your hobbies – everything you hope to make time for during internship. Planning is one thing, whether you can do it is another. It’s okay if it changes throughout the internship. Never thought basketball would be one of the things I’d want to make time for but here I am. Use the first few weeks to find the right momentum and slowly work things out from there. You don’t have to figure it all out on the first try. It’s still a mystery to me how parents have the energy to do this whole work-life balance thing.

Throughout internship (the school side of things):

-       Advice #3: Logbook. Logbook. Logbook.

Pretty sure every school differs in their daily submission requirements but for my course (Mechanical Engineering) I have to submit a bi-weekly logbook detailing learning points and answers to specific questions. I didn’t think it would be such a burden but… boy was I wrong. I find it is so dreadful. Not because the questions are hard to answer, but because I keep forgetting to do it and end up trying to type as fast as I can before the Friday deadline. So please, don’t put yourself through what I did and set a reminder or block your calendar to complete it sometime during the week. Have your sticky notes open on your desktop and pen down points throughout the week so you don’t panic like I do when you have 5 hours left to submit it.

-       Advice #4: @ friends who are not on internship

Not gonna lie, the FOMO did kick in a few times. While many of my friends are in school going for lunch together, there I was trying to figure out why my vlookup formula isn’t working. I know the thought that I would not know anyone when I go back to school next semester seems absurd, but it does genuinely scare me sometimes (ESFJ much?). Luckily for me, being part of an adhoc helped make me feel like I’m not completely out of the loop and my (lovely) friends do check up on me or (try to) make dinner/weekend plans. Lunch too (and won’t let me PAY, if you’re reading this - may Allah bless you). So, if you have a friend going on internship next semester, please text them occasionally, I can promise you they’d be extremely grateful to know you still remember them. Advanced note: Expect late replies though, it’s not indicative of how thankful they are in having you check up on them.

Throughout internship (the work side of things):

-       Advice #5: Make full use of all the opportunities they provide employees.

Let me just get one thing out of the way first; Yes, interns are employees as well. In the MNC I work for, there are many sponsored certifications for employees to take up. Hopefully when you see me in school next year I am a certified yellow belt in Lean Six Sigma, insha Allah (if I ever find the time to complete the learning materials that’s been put on hold for 2 weeks now). Start making it a habit to ask questions – get your supervisor to teach you how to navigate a certain platform (to my engineering friends, SAP is more confusing than it sounds) or have them share their personal opinions on how a meeting went. It’s interesting to hear from someone who has been working there a lot longer than you have.

-       Advice #6: Get to know the different working styles you get along with.

Make use of this time to work with as many individuals as you can. You’ll be able to know more about your own tendencies and motivations as well as the working styles that you can (and cannot) click with. (You know those unpleasant group project experiences you’ve had in school? Yeah they happen in the workplace too, with adults.)

-       Advice #7: Be open about your workload.

I am so blessed to have the supervisor that I do. 70% of the good experience I’ve had thus far I owe it all to her. (The other 30% were the friends I made and the company benefits. I mean $30 bowling vouchers for $5? WHAT. A. DEAL.) One thing that has helped facilitate a good working relationship is open communication. Whenever she assigns me a task, I let her know what are the tasks that I currently have on hand (including school tasks such as logbook) and how early she can expect for me to complete the task. This way, my supervisor is constantly updated on my current workload and can better decide if more work should be assigned to me. I know how that may sound crazy (and scary) but really, sometimes with the amount of work they have to handle, supervisors can forget what they have assigned you. Trust me, it’s worse if you tank and end up having a backlog of tasks to complete.  

Throughout internship (the you side of things):

-       Advice #8: Don’t worry about your Zuhr and Asr’ prayers!

I’ve had my fair share of part-time jobs and I know this can be a worry sometimes. So, for anyone who may need the extra reassurance, don’t worry. Let colleagues know early on about having to go for prayers. Alhamdulillah my colleagues were really understanding of it, they even showed me where I could go to pray. My advice to you is to read the situation when you are informing them about having to go for prayers and if they look hesitant, let them know how short it’d probably take you. Most of the time, they are hesitant because they are overestimating the time it takes to pray and they are just worried that your work will be affected or that you won’t be there when they need you.

-       Advice #9: Remember that emails are not an easy feat.  

Another one of the small things I never thought could be so challenging. Being overwhelmed by emails is a real thing and please don’t let it happen to you. People send emails even after midnight (which also reminds me, please DO NOT bring work home unless absolutely and definitely necessary) and the rate that emails come in awaiting your reply, my friend, is a recipe for burn out. I mean people even block their calendars just to clear emails! Don’t try to reply them all in one go. Consider the subject and assess its urgency. Also, another tip, Outlook allows you to @ the one concerned/who needs to take action in your email. Based on my experience, that allows you to get faster replies from the relevant colleagues. May Allah ease your inbox, insha Allah.

-       Advice #10: Don’t limit yourself!

You are not “just” an intern. Really, this self-limiting mindset does more harm than good. If you tell yourself you are just an intern, then you’re likely to be treated as one – being assigned work that full timers do not want to do. I’ve heard so many stories from friends of how they had to overtime because they were overwhelmed with work. Speak up for yourself. It wasn’t easy gaining the trust, but by asking if I could take up a certain task or help out with one that was not originally assigned to me, my supervisors and colleagues very rarely give me admin work and trust me to handle bigger tasks. (Alhamdulillah!)

Be honest with yourself and ask yourself how you want your internship journey to be. This was my first ever internship experience (and quite possibly the only one before I venture out into the working world) so I really wanted to learn as much as I can and prove to myself that I am capable and ready. Hence, I hope that with the tips I shared here, I can help you achieve that too, insha Allah.

[jekim1112]

4 Excel tips in media planning for a college senior

I saw a post on Quora in which a university student threw a question about how to use Excel and what is the important functions for the media planner role. When I just started my career, back then, I didn’t know what I could do with Excel either. In fact, I had an Excel certification from Microsoft but, you know, getting certified is actually different from applying the knowledge in real life. Luckily, I met a great boss to mentor me so that I could learn and grow.

Likewise, I’d like to share some Excel tips that I frequently use as a media planner with people who are newer to this area. If you learn proactively and get used to these things before starting your career, it will pave the way for a successful career.

1. Let’s play around with basic media math

There are fundamental metrics we need to know for media planners. For example, CPM, CPC, CTR, and conversion rate, etc. I’m sure that having a good command of manipulating these will be a great asset for your upcoming work life and also lead you to get a sense of relationships between measurements and metrics. Make sure you understand how and why it works. I suggest looking at this blog that would be helpful for you to have a grasp of the metrics!

2. The more you use shortcuts, the quicker you work

If you are comfortable with excel shortcuts, especially when you are soring out raw data and pivoting it in order to look into it in a multi-faceted way, it will be really useful because you don’t waste your quality time anymore. Filtering tables, autofill data, find and replace characters, freezing panes, and jumping between multiple spreadsheets are some examples that I typically use on a daily basis.

Hey there! If there is a media planner who is coming up with something more on this, please leave a comment to support future media planners working with us in the same industry.

3. Charting graph is life

When we devise and present a media strategy to clients, it is normally based on lots of supporting data to back up our market insights by including visual material such as graphs and charts. Since most of the market data is vast and complicated, we resort to graphs with multiple axes & pivot charts.

4. Organizing data is a key to finding meaningful insights

Pivot tables, conditional formatting, protecting spreadsheets with complex formulas by password, VLOOKUP, IF, SUMIF, and IFERROR are my favorite functions that I use a lot. Furthermore, if you are good at Macro which is an advanced excel skill to save tons of time and potential errors, it would be a huge advantage for your future career as well. Please refer to this blog for further information.

Conclusion: Don’t forget the purpose of it all

Be mindful. However important the 4 tips above may be, we shouldn’t lose sight of the goal: persuading your clients.

How well we visualize data in various formats to help our clients easily get the gist of it is the key and that’s why we use Excel, PowerPoint, and various kinds of marketing dashboards.

If you would have a question, please don’t hesitate to contact me. I always welcome enthusiasts. Plus, as a person who has got great help from my colleagues and bosses, I am willing to share my humble knowledge with you who are looking for an opportunity to learn from.

[mr-entj]

Are there any resources you can point me to in order to improve my consulting math? Management consulting is a route I want to take after university but the biggest thing standing in my way is the lack of math skills I have (I study Law and Political Science).

Combined with the following ask:

Mr. ENTJ, I’m extremely artistically inclined but somewhat incompetent in mathematics. Yet I’ll be studying Commerce at the most prestigious university in my country in the Fall of 2018 for undergrad. To what degree do I need mathematical knowledge to be successful in business and how can I improve my grasp on mathematical concepts? I lack interest in numbers and symbols so I’m wondering if there’s a more creative/artistic way of looking at it.

Hi Mr-entj. How much math did you have to learn to be good at business? I’m horrible at it but looking for ways to improve and quickly before I graduate.

It depends on what kind of business you’re looking to do but generally the math concepts in college are much more complex than anything you’ll use in the real world. Unless you’re an analyst for a think tank in Washington D.C. weaving complicated macroeconomic models, consulting/business math is straightforward. Don’t be intimidated.

Business Math Concepts

Master:

Division

Percentages

Decimals

Probability

Statistics

Conversions (units of measurement)

Algebra (especially finding unknowns)

Basic Finance (especially interest, compound interest, risk)

For mental math (important in the case interview), the expectation is you can do problems like these in your head:

25% of $10 million = ?

$32,000 x 4 = ?

$1 million increased by 50% = ?

15,000 decreased by 30% = ?

The majority of business math will come in the form of word problems because the real world isn’t going to come up to you and ask: “Hey, my company is failing and I need to figure out what’s wrong, what’s 200x+3= 50,000? Solve for x, thanks.”

They’ll ask you questions like these instead:

“Hey, my company is failing and I need to figure out what’s wrong. Can you identify which department is underperforming the most, quantify by how much, and recommend solutions?”

“I feel like we’ve increased our financial performance this year, can you show me which specialization brings in the most revenue with the lowest overhead?”

“Our community clinic needs to save money to stay open, can you find a way to identify the least utilized services and the highest utilized services? Can you measure the impact to our patients if we change any of these services?”

“Hey, we put in that solution a month ago and we want to know how well it’s working. Can you figure out if we solved our problem and by how much?”

The key takeaway is you have to understand what’s going on, what message the data is telling you, and the cause and effect of your actions. Questions that might come up include:

What the hell is going on here?*

How are we measuring success? Is it making more money? Is it losing less money? Is it higher output? Is it better quality scores? Is it fewer errors? What’s the metric for success?

How do I know we’re successful? How do we quantify and measure it?

If I want to increase variable A, what do I need to change/move/increase/decrease/close/open to make it happen?

If I increase variable A, what impact (if any) will it have on variable B?

The data says that C has decreased, how much impact did D or E have on that change (if at all)?

*critical question to answer in any business environment.

Business Math Tools

Learn:

Data visualization. All data and insights gleaned from calculations need to be translated into a data visual (graph). People will run for the hills if you hit them with a giant wall of text summarizing week to week revenue reports; that’s not a stimulating read so use graphs. Learn histograms, line charts, pie charts, waterfall charts, etc. Know how to read them, how to use them, and when to use them.

Microsoft Excel like the back of your hand. This program will be used to run numbers and process data. Learn VLOOKUPs, pivot tables, shortcuts, and all the goodies.

Tableau is a powerful data visualization software that’s very popular. It’s used to produce dynamic charts and graphs that automatically update based on fresh data.

SQL if you’re a glutton for punishment. SQL is used to query for data, knowing SQL makes you infinitely more attractive in the job market.

Resources

Math: Consulting Math, Math 2, Math 3, Math 4, Business Math concepts, GMAT books are also useful

Graphs: 44 Types of Graphs and How to Use Them, Working with charts, graphs, and tables, Data Visualization 101, Types of Graphs

Excel: Excel Formulas, Formulas 2, Formulas 3, Excel Shortcuts, Shortcuts 2

Tableau: Tableau 101, Tableau Community Forums, Hot Keys & Keyboard Shortcuts

SQL: SQL Tutorial, SQL Tutorial 2, 18+ Best Online Resources for Learning SQL and Database Concepts (2018 revision)

[globalteachonline]

What you'll learn Master Microsoft Excel from Beginner to AdvancedCreate dynamic reports by mastering one of the most popular tools, PivotTablesMaintain large sets of Excel data in a list or tableuse Excel Like a bossWant to learn Microsoft Excel and improve your data analysis skills, but don't know where to start? OR Have you been using Microsoft Excel for a while but don't feel 100% secure? There is so much information out there. What do you need to be successful at work? I've selected the basic Excel skills data analyzers need and combined them in a structured course. In fact, I've put together the most common Excel problems my clients face. I am adding to my 15 years of experience in finance and project management. I've included all the hidden tips and tricks I learned from Excel as an MVP and included EVERYTHING in THIS course. I've also made sure that it really covers Excel beginners. This practical example will help you understand the full potential of each function. You'll learn how to use Excel for fast and painless data analysis. There are many useful and time-saving formulas and functions in Excel. We tend to forget what they are when we don't use them. This Microsoft Excel Essentials course will give you the practice you need to best implement the solutions to your assignments. That way, you can achieve more in less time. By the end of the course, you can be sure that you will showcase your new Excel skills on the job. So you can: Enter data and navigate large tables Apply hacks in Excel to get work done faster Choose the right Excel formula to automate your data analysis (Excel VLOOKUP, IF function, ROUND, etc.). Use Excel's hidden features to turn cluttered data into precise notes Get answers from your data Organize, clean and manage big data Create attractive Excel reports following a number of spreadsheet design principles Turn cluttered data into useful charts Create interactive reports with Excel pivot tables, pivot charts, slicers, and timelines Import and transform data using tools like Get & Transform (Power Query). We'll start with the basics of Microsoft Excel to make sure we have the basics right. We move on to advanced topics like conditional formatting, Excel spreadsheets, and Power Query. We cover important formulas like VLOOKUP, SUMIFS, and nested IF functions. As well as discussing the purpose of a feature or formula, I'll also cover how you can use it with practical examples. There are challenges and exams along the way to test your new Excel skills. Your Excel course download notes are available as PDF files. They discuss the most important points. Have it ready and refer to it when you need it. Who this course is for:This Course is equally suitable for students from all industry types