Relative Referencing and Absolute Referencing Explained

 

There are two main types of cell references in Microsoft Excel namely relative and absolute although there is another reference TYPE called mixed reference where part of the reference is Relative and part is absolute. The two main types of reference behave differently when copied and filled DOWN to other cells.

Relative references change when a formula is copied to another cell. While Absolute references, on the other hand, remain constant no matter where they are copied. In order to understand how the two reference types are used, go to the tutorial in the link below

RELATIVE AND ADSOLUTE REFERENCE VIDEO TUTORIAL

How I developed Excel Skills: My Personal Journey

I knew it was important to learn Microsoft Excel, I had used Lotus 123 in the mid-90s but Lotus was slowly losing popularity and I had to take on the new Elephant in the House called Microsoft Excel which was gaining wider use in Industry. However, the people who learned how to use Excel first were so selfish and not willing to share hoping to keep the knowledge within a closed circle. Having learned Lotus 123 before, I knew I had to take on the same attitude I used with Lotus on Excel and I vowed to learn Excel and break the unproductive circle wide open for anyone to enter. It has been the sharing attitude since that has opened doors of opportunities for me. The 10 below summarises how I learned and improved Excel skills

1. In the first instance, Excel was created by a human being like me. Hence I decided to take on the mindset that Microsoft Excel was simple and easy to learn because if the guy who created it intended it to be difficult and complicated, then he or she must have been a fool. How can one create a product that potential clients would not be able to use? Therefore, the application was not beyond comprehension
2. I chose not to let other people’s opinions about Excel guide my attitude toward Excel spreadsheets. Like the Chinese would say don’t let anyone who has never built a house tell you it’s difficult. Hence, I took on any topic without fear of making mistakes, and knowing that the computer has the ‘Undo’ and ‘ESC’ options was a lifesaver. Others may have failed but they are not ‘Me’
3. I planned to learn at least one thing in Excel every day whether it was a basic task as simple as how to move spreadsheet rows or could be an advanced trick. I purposed in my heart that if I learned one thing a day, it translated into knowing 7 things in a week or 30 things in a month
4. Whatever I learned, I practiced the task at least 4 or more times to ensure my hands mastered how to accomplish the task without my brain being so actively involved. It’s like teaching your hand to sign your signature and letting it do the task without you thinking about the rest of your life.
5. Whatever I learned, I found someone else to teach. The best way to remember what you have learned is to be generous enough to teach others because as you teach the subject you are also relearning and reinforcing the information on your brain or your 'biochemical hard drive' if you like.
6. Don’t be stingy with information, being selfish with information is the fastest way to forget what you have learned. Teaching is also learning for the teacher. The attitude of thinking that if you are the only one intelligent on a subject, it means you are in charge! You are wrong. Like Eric Hoffer once said “In a world of change, the learners shall inherit the earth, while the learned shall find themselves perfectly suited for a world that no longer exists” the choice is yours.
7. Whenever, someone asked me for help in Excel with one thing, I shared more than they asked for because through that I was able to discover other things that I may not have been initially aware of.
8. Took time to explore Microsoft resources such as Excel’s own online help and other online sources to learn more about various Excel tasks.
9. I was never shy to ask for help from other Excel users
10. Above all, I have learned that one does not need to be a mathematical genius to competently use Microsoft Excel. One does not even need to have passed mathematics in primary or secondary school, the only requirement is that one needs to be a human being and must have passed first grade in primary school. Just to have known how to write and count if you can put it that way for Excel use. But generally one only needs to be human because computers can also be used for artwork, music and many other things. I am sure we all have seen how 1 or 2-year-olds can play games on gadgets even when they can’t even read or write 

Microsoft Excel Tip for DATA ANALYSTS in 2022

One way to ensure DATA CONSISTENCY in Data Entry

Put simply, data analysis is described as the process of collecting, modeling, and analyzing data to extract information that supports decision-making or call it data-driven decision-making. The data used as a basis for analysis is captured or entered by either the data analyst or data personnel somewhere in some data room. The accuracy of the result of the analysis is determined by the accuracy of the source data (like they say Garbage In, Garbage Out). However, one of the issues that I have encountered in my line of work that can easily distort accurate reporting is the failure by data workers to enter or capture repeating data items in a consistent manner. Thankfully Microsoft Excel 2016 provides users the automatic drop-down list feature to control consistency in data entry. To Initiate an AUTOMATIC DROP-DOWN List is simple but uncommon trick using the keyboard short-cut ALT + ↓ (the ALT key + Down Arrow key). Let me show you how this works in the short video in the link below using an example from the health sector

INITIATING AUTOMATIC DROP-DOWN LIST IN EXCEL VIDEO TUTORIAL

Building Microsoft Excel competent User in 1hour! The 20/80 Rule Learning strategy from experience

 “Whether you think you can, or you think you can't, either way, you're right.” ~ Henry Ford

It does not matter at what level of computer skills one may be whether new or otherwise. It has been achieved before and I relate this from practical teaching experience that anyone can become a competent Microsoft Excel user in 1 hour or less. The 20/80 rule works wonders in computer application skills training as a totally new user with practically no previous exposure to the application can be transformed. In the 20/80 rule, we use 20% of the time to introduce the application (content delivery) and 80% on building muscle memory or motor skills. This training model is quite useful when dealing with adult learners above the age of 35 or 40 (practically Adults).

How does this work? Let’s take the example of driving schools, the driving instructor knows that any new trainee knows what a car is including the steering wheel, gas or brake pedal and the newbie can actually drive the car in their mind. However, the new trainee’s hands and feet do not know, as experience has shown that whenever you let someone who has never driven before to try to drive, you can see how their hands and feet become epileptically out of sync in trying to get the vehicle even to move an inch. Why? Because whilst the mind knows something about the car, the hands and legs have absolutely no driving motor skills or muscle memory on the equipment. Are you catching the idea? Remember the first time you signed your signature! Your brain worked with your hand to ensure that the hand can actually scribble the signature the way you wanted it to look. In the initial signing sessions your brain was alert to ensure that the hand reproduced the same signature. However, when the hand masters the art, you no longer have to think about how you sign because your hand can now do the job without you thinking about it.

Applying the same method with building spreadsheets skills in adults, the category of senior citizens we can call BBCs (Born Before Computers), the battle is won in the first 10 minutes of the training session. Let’s now break down the 20/80 rule with reference to spreadsheet skills training

• The first 10 minutes or a little more depending on the number of students is for storytelling. During this time the instructor and the students introduce themselves and should include allowing the students to tell a bit about their experiences regarding the subject at hand. It’s during these moments when the instructor can take the students’ own stories of weaknesses with regard to the subject and turn them into stepping stones of triumphant entry into the next versions of themselves with reference to the subject before them. I am not sure why but I have learned from teaching experience that the name EXCEL scares the hell out of new users. It’s like whenever new users have seen the EXCEL interface on computers with the mysterious boxes as they usually refer to them all over the screen, it reminds them of their mathematical woes probably in their formative school days. How do I get the students passed that fear gate? I ask the students to open the Excel application on the training computers and we all look at the interface together before we even click anything on it because I know that we need to address the elephant in the house, the EXCEL boxes, or CELLS that they see. This is the most interesting part of the storytelling when you tell the students that what they are seeing is actually a primary school second grade (grade 2) mathematics book in electronic form which has been designed not to allow the student to do all thinking when using it because the computer application was designed to think for them. The students may probe a little further on the details but usually, you can feel the sigh of relief dawn on their faces as they suddenly relax with a sense of confidence to conquer. This makes the brain send a new message that they are dealing with the simple stuff.  It also helps a lot to dispel every rumor or every prejudiced idea the students may have heard about how complex Excel is and reinforce the “Yes we can do it” message that should encourage them to be willing to start from scratch with the attitude of “they can do this” By the way when dealing with adults try as much as possible to remember their names and call them by name when encouraging or motivating them it makes them feel ‘young’ so to say if you know what I mean
• It’s also important to let the students know from the onset that the computer has the lifesaving Undo and the ESC button so that they are not afraid to click and make mistakes. Students find this very comforting and are ready to explore without fear.
• If the students are already users of other computer applications such as Word Processors, they may not have problems handling the notorious Mouse but if not ensure that they are helped in handling the Mouse using a clicking (left or right) exercise because the Mouse handling skill is crucial in working with Excel.
• Utilize 20% of the time on the delivery of the content and clarifying details on spreadsheets and how to accomplish different tasks as per the subject plan. Adapt the plan based on the student profile as strict adherence to the order of the tasks can sometimes be very boring and fail to ignite the fire in the students
• 5.    In the 80% part of the rule, it’s time to move the content from the head to the hands (body) to build motor skills or muscle memory. Start with the simple but interesting tasks that should start building the muscle memory like the FILL down series technique for days, months and number series. This is where the students type the first day or month and use the left-click and drag down to fill in the rest of the names of days or months as a list. Let the students repeat doing the task for even four times again and again. In my experience, this creates the students ‘Wow’ moments and motivates them to begin to look forward to more interesting automated tasks. When you create that ‘Wow’ moment in students then you know that the hidden expert in the students is now awake and ready to come out. The rest goes into autopilot as you see a group of interested and interactive students even trying to get ahead of the instructor's Excel possibilities

The Objective of building computer skills is not to push everything in the head but to impart the skill into the body (hands) because the body does not forget easily just like the body does not forget how to walk. 

T

Microsoft Excel Tip Aplying FILL Series for Days, Months and Number Series

Techniques in applying FILL Series for Days or Months and Number series in Excel

There are times as an Excel user new or otherwise when we accomplish tasks not in a very efficient and easy way in Excel. For example, you want to fill in Months by typing all the months because you may not be aware of the Series 'Fill-down' techniques or you would like to fill in a number series from 1 to 10000 and you wonder how you can accomplish that task by a few clicks like a 'Pro'. Click the short video tutorial for some helpful tips that can increase your efficiency when working in Microsoft Excel. We hope you find this tutorial helpful

Excel Tip on Fill Series Video Tutorial

Welcome to Bright Excel Campus community

Thank you and welcome to all who have joined the Bright Excel Campus Facebook community page (https://www.facebook.com/groups/959592901391249). The general objective of the online Campus is to help build useful Microsoft Excel Spreadsheet skills in members which are suitable for data manipulation, data analysis, and reporting using the power of Excel Tables, charting tools, or advanced methods such as Power Queries, Pivot tables, and Power Pivot.

As you may be aware, Microsoft Excel is a software program created by Microsoft that uses spreadsheets to organize numbers, text, and other forms of data with the ability to use formulas and functions. For spreadsheet lovers, it’s the software of choice for data modeling, statistical analysis, and reporting in meaningful ways. Excel can also be used by schools for general mathematical computation.

We welcome all our members to this community of Excel users. Some of our members may be new to Microsoft Excel, some could be basic users and others may be advanced users. At whatever level you are this is the place you are at liberty to ask questions and share ideas for the sole purposes of ultimately developing experts in Excel Spreadsheet in every member. The Bright Excel campus community Facebook page is connected to the online campus blog https://brightexcelcampus.blogspot.com/where resource materials of interest will be uploaded for you from time to time use and you will be free to download.

My name is Bright Mulenga holder of a BSc in Computer Science, Professional Diploma in Project Management (with Microsoft Projects software), Advanced Diploma in Computer Science & Information Processing, and Masters in Business Administration (MBA). I have been using spreadsheets for over 20 years starting with Lotus123 and Microsoft Excel from basic to advanced techniques. I am also an ardent user of the SPSS application for data analysis purposes. It's my hope to share my Excel experience with members of the Bright Excel campus community

Excel useful Keyboard short-cuts that make you work like 'Pro'

Microsoft Excel seems by far the Application of choice by many financial and Data Analysts working with sets of data for decision making and reporting. It is an interesting and user-friendly data manipulation software to work with. However, it is even more interesting when you learn how to use short-cut keys to carry out commands without using the traditional mouse. Excel has a lot of short-cut keys available for use but here are some of the most useful  that can make you the user work like 'Pro' and set you apart from most Excel users in the link below ->>

Useful Excel Short-cuts

Technolgy For 'the GOOD' of Africa

We can not afford to click the 'Undo' button to reverse technology: Technology is here to stay

One wonders why some people are still afraid of the computer! Is it related to the way the computer looks? The desktop computer looks so big that sometimes someone may think that with one mistake everything could break down? Or the Laptop computer is too small for their liking? Whilst some of these could be attributed, there is still one thing that the writer of this article has observed over the years of spending time teaching people how to use the computer equipment and the applications installed on them. First things first, it was common in the 90s that the people who learned how to use the early versions of computers and their application software wanted to keep the skills only to themselves hence, portrayed the computer as a very difficult piece of equipment to mess with. People were made to understand and believe that the computer needed special attributes or intellectual abilities to go around it. Believe me, some top executives could still be uncomfortable with computers. Whatever the reason some people are still not friends with the computer, it is time to simplify and demystify these valuable pieces of technology.

It is a fact without question that the modern era is a technology-driven one. Therefore, it is not for anyone to decide whether to use or not to use technology as a daily essential in third world nations. The truth in the face of everyone is that we are all affected by technology. The advent of mobile technology, email, social media, and virtual platforms such as Zoom has given mankind the ability to communicate and collaborate instantly across cities, countries, and continents with ease hence, bringing about efficiency in service delivery systems. Nearly all the banking services have been automated, therefore, who can escape the technological Trap. People in the third world nations require computers or technologies of some kind in their homes to do what previously they had to take a trip for in order to accomplish. Things like shopping, education and so on. However, the rate at which technological services are progressing does not seem to match the advancement in computer literacy amongst the elderly (improperly referred to as the BBC: born before Computers) and vulnerable groups in rural Africa. Most rural schools may yet to get a really good experience with a modern Desktop Computer far less a Laptop or Notebook computer. They may have seen a computer on paper but not enough to distinguish the different common parts of the equipment in reality when the equipment is physically present. The level and quality of some categories of Computer knowledge being imparted in certain cases may be half-baked. The Lecturer calls the System Unit a CPU (Central Processing Unit). Well, the argument in calling the computer's system unit as the CPU that may have been advanced could be that of trying to simplify the technical jargon. But if that is the case why not call the entire house a Toilet in order to describe what a house is. Africa needs to bridge the Technology gap A.S.A.P. and stop being spiritual about everything

We are about to be overwhelmed by the 5G technology era and everyone better get ready to join in or be left behind. Technology will contribute to the conservation of our environment as we go paperless with technology.

Welcome to BRIGHT EXCEL CAMPUS where we are sharing “Technology for Good” through applying the power of Computer applications such as Microsoft Excel to help in making data tell the whole story that can help in data-driven decisions making in personal, professional, and business arenas. 

Learn anywhere any time and don't stop learning

Igwee! Bright